0

In my custom module for Drupal 7 I've been using the following 2 SQL queries to find the user's avatar picture and the city (a custom text field):

function pref_block_view($block_name = '') {
  global $user;
  $viewer_id = $user->uid;

  if ($block_name == 'pref_main') {

    $result = db_query('select field_city_value from {field_data_field_city} where entity_id=:uid', array(':uid' => array($viewer_id)));
    $city   = $result->fetchField();

    $result = db_query('select filename from {file_managed} where uid=:uid', array(':uid' => array($viewer_id)));
    $avatar = $result->fetchField();

and at first this worked well (probably because each user hat just 1 picture - namely the avatar).

But then users attached pictures to blogs and books and now this query doesn't really work:

 # select * from drupal_file_managed where uid=1;
 fid  | uid |           filename           |                      uri                       |  filemime  | filesize | status | timestamp
------+-----+------------------------------+------------------------------------------------+------------+----------+--------+------------
  631 |   1 | 111.png                      | public://111_0.png                             | image/png  |   289191 |      1 | 1303376737
  843 |   1 | etud18.png                   | public://etud18.png                            | image/png  |   893838 |      1 | 1305905585
  527 |   1 | yaro-3.jpg                   | public://yaro-3.jpg                            | image/jpeg |    89814 |      1 | 1302367745
  529 |   1 | yaro-4.jpg                   | public://yaro-4_0.jpg                          | image/jpeg |   122129 |      1 | 1302367789
  638 |   1 | etud015.jpg                  | public://etud015_0.jpg                         | image/jpeg |   116956 |      1 | 1303459359
  835 |   1 | pref.jpg                     | public://pref_0.jpg                            | image/jpeg |    95079 |      1 | 1305838610

So I'm obviously using wrong query. Any help please?

3

Check the table "users", column "picture". Join that on the fid from the file_managed table.

  • Thank you, this works now: $result = db_query('select f.filename from {users} u, {file_managed} f where f.fid = u.picture and u.uid=:uid', array(':uid' => array($viewer_id))); $avatar = $result->fetchField(); – Alexander Farber Oct 5 '11 at 21:35
  • 1
    by the way, to display the avatar you could just use print theme('user_picture', array('account' => $user)); – uwe Oct 5 '11 at 21:57
  • Thanks, but I need it for an embedded Flash game (I pass the avatar URL to it thru FlashVars) – Alexander Farber Oct 6 '11 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.