By Darrell on

I am trying to modify a page snippet (http://drupal.org/node/63422) to add the Last Name field to the output. I need a list of all users and I want to be able to sort on Last Name. This is what I have so far:

<?php
//choose the role to list by role ID value. 
// Note ID 1 == anonymous, ID 2 == authenticated user
// so valid values here are > 2.
$fid = 2;
?>

<h2>A list of all users with Role ID <?php print $rid?></h2>
<br/>

<?php
$header = array(
  array('data' => t('Username'), 'field' => 'users.name'),
  array('data' => t('Last Name'), 'field' => 'profile_values.value'),
  array('data' => t('ID'), 'field' => 'users.uid'),
  array('data' => t('Status'), 'field' => 'users.status'),
  array('data' => t('Member for'), 'field' => 'users.created', 'sort' => 'desc'),
  array('data' => t('Last access'), 'field' => 'users.access'),
  t('Operations')
);
$sql = "SELECT users.uid, users.name, users.status, users.created, users.access, users.uid FROM {users} INNER JOIN {profile_values} ON users.uid=profile_values.uid WHERE profile_values.fid='2'";

$sql .= tablesort_sql($header);
$result = pager_query($sql, 50);

$status = array(t('blocked'), t('active'));
while ($account = db_fetch_object($result)) {
  $rows[] = array(theme('username', $account),
          $account->value,
          $account->uid,
          $status[$account->status],
          format_interval(time() - $account->created),
          $account->access ? t('%time ago', array('%time' => format_interval(time() - $account->access))) : t('never'),
          l(t('edit'), "user/$account->uid/edit", array()));
}

$output = theme('table', $header, $rows);
$output .= theme('pager', NULL, 50, 0);
print ($output);
?>

and the result is:

    Username      Last Name       ID     Status     Member for           Last access                 Operations
    glocker40                              204      active       1 day 12 hours      1 day 8 hours ago       edit
    Rowland                                203      active       1 day 23 hours      1 day 23 hours ago      edit
    hobya                                  202      active      6 days 14 hours      6 days 14 hours ago      edit
    steveyahn                             201      active      1 week 12 hours      1 week 11 hours ago      edit
    Kim                                      200      active      1 week 23 hours      1 week 11 hours ago      edit

    I am only showing a few lines of output for an example. The Last Name column is empty but I can sort on that column
    and I get a true sort as if the names were actually there. How do I get the names to show?

⋅ Categories: Drupal 5.x

Comments

userofdrupal’s picture

userofdrupal commented

I'm no Drupal/php expert but it looks like your select list is off. You have:

"SELECT users.uid, users.name, users.status, users.created, users.access, users.uid FROM ..."

It seems to be missing the profile_values.value column. It should be:

"SELECT users.uid, users.name, users.status, users.created, users.access, users.uid, profile_values.value FROM ..."

My two cents.

Darrell’s picture

Darrell commented

Thank you, I was thinking I could only select from users and the join would pull values. This opens a lot for me. Again thank you for replying.

Darrell’s picture

Darrell commented

With help I fixed the above problem but now I want to add another column from the profile_values table. If I add the column "first Name
" as below I get the column heading but both first name and last name contain the last name. Being new to PHP and MYSQL I am at a loss as to how to proceed.

$header = array(
  array('data' => t('Username'), 'field' => 'users.name'),
  array('data' => t('Last Name'), 'field' => 'profile_values.value'),
  array('data' => t('First Name'), 'field' => 'profile_values.value'),
  array('data' => t('ID'), 'field' => 'users.uid'),
  array('data' => t('Status'), 'field' => 'users.status'),
  array('data' => t('Member for'), 'field' => 'users.created', 'sort' => 'desc'),
  array('data' => t('Last access'), 'field' => 'users.access'),
  t('Operations')
);
$sql = "SELECT users.uid, users.name, users.status, users.created, users.access, users.uid, profile_values.value, users_roles.rid FROM {users} INNER JOIN {profile_values} ON users.uid=profile_values.uid LEFT JOIN {users_roles} ON profile_values.uid=users_roles.uid WHERE profile_values.fid='3'";
$sql .= tablesort_sql($header);
$result = pager_query($sql, 50);

$status = array(t('blocked'), t('active'));
while ($account = db_fetch_object($result)) {
  $rows[] = array(theme('username', $account),
          $account->value,
          $account->value,
          $account->uid,
          $status[$account->status],
          format_interval(time() - $account->created),
          $account->access ? t('%time ago', array('%time' => format_interval(time() - $account->access))) : t('never'),
          l(t('edit'), "user/$account->uid/edit", array()));
}

$output = theme('table', $header, $rows);
$output .= theme('pager', NULL, 50, 0);
print ($output);
userofdrupal’s picture

userofdrupal commented

It depends. If the value in the profile_values.value column is always something like:
"Jane Doe"

In your SELECT list you might be able do something like:

SELECT users.uid, users.name, users.status, users.created, users.access, users.uid, profile_values.value, users_roles.rid, SUBSTRING_INDEX( profile_values.value, ' ', 1) AS first_name, SUBSTRING_INDEX( profile_values.value, ' ', -1) AS last_name

Then in your array do something like:

$header = array(
array('data' => t('Username'), 'field' => 'users.name'),
array('data' => t('Last Name'), 'field' => 'last_name'),
array('data' => t('First Name'), 'field' => 'first_name'),
...

Darrell’s picture

Darrell commented

profile_fields
fid = 1, title = First Name, name = profile_First_Name
fid = 2, title = Last Name, name = profile_Last_Name
fid = 3, title = Phone, name = profile_Phone

profile_values
fid = 1, uid = 22, value = Jane
fid = 2, uid = 22, value = Doe
fid = 3, uid = 22, value = 555-1515

So far I can either get a listing with the value for fid 2 in both first and last name columns or three rows for each user with fid 1 in the 1st row,
fir 2 in the second row and fid 3 in the third row.
Some how I need to join profile_fields and profile_values and use profile_fields title as the key to select profile_values.value to print.
I am just guessing as I am very new to PHP and MYSQL.
I have looked at userinfo.module and it can print out a list like I want but it does not let me sort on the table headings. I need to be able to sort on the headings.

userofdrupal’s picture

userofdrupal commented

Sorry I misunderstood what you were trying to do. It looks like you want to pull in join values from 2 different rows (e.g., fid=1,uid=22 and fid=2,uid=22). I'm not sure how to do this but there's probably someway. If I think of anything I'll post.

RobertNelsonVance’s picture

RobertNelsonVance commented

Yea, I've run into the same issue. Have done tons of Google and Drupal searching and no luck. Looking through other modules now to see how someone else may have solved this.