I modified the drupal user listing to see the profile.realname in the columns, because username is a numeric id (needed by our hierarchical system of salesman).

 function user_admin_account() {
  $header = array(
    array("data" => t("ID"), "field" => "u.uid"),
    array("data" => t("username"), "field" => "u.name"),  // every username now looks like "xxx/xxxxxx", 
                                                          // so real need of a realname info
    array("data" => t("Real name"), "field" => "u.data"), // added column for salesman realname
    array("data" => t("status"), "field" => "u.status"),
    array("data" => t("role"), "field" => "u.rid"),
    array("data" => t("last access"), "field"  => "u.timestamp", "sort" => "desc"),
    t("operations")
  );
  $sql = "SELECT u.uid, u.name, u.status, u.timestamp, u.data, r.name AS rolename FROM {role} r INNER JOIN {users} u ON r.rid = u.rid WHERE uid != 0";
  $sql .= tablesort_sql($header);
  $result = pager_query($sql, 60);

  $status = array(t("blocked"), t("active"));
  while ($account = db_fetch_object($result)) {
    $extra = unserialize($account->data);           // to retrieve the real name in users.data ; see below
    $rows[] = array($account->uid, format_name($account), $extra['profile_realname'], $status[$account->status], $account->rolename, format_date($account->timestamp, "small"), l(t("edit account"), "admin/user/edit/$account->uid"));
  }

  $pager = theme("pager", NULL, 60, 0, tablesort_pager());
  if (!empty($pager)) {
    $rows[] = array(array("data" => $pager, "colspan" => 7));
  }
  return theme("table", $header, $rows);
}

But with this hack, tablesort "looses his alphabet" because it sorts users data field and not users.data.profile_realname serialized data. Then the sort on my realname column is a complete mess.

Any idea of how I could sort based on a profile data.

Comments

killes@www.drop.org’s picture

You cannot do that in Drupal 4.4. In CVS Drupal you can do a join on the profile_fields and profile_values table and sort on the realname field in the table.

Anonymous’s picture

trishredhop’s picture

Version: » 4.7.3

I need to try something like this as I need to list by city, then by organization in a table like:

Boston
Society Jesuit Theological Seminary
Name                                 Phone
[ahref=UserID_url]UserID         (000) 000-0000
[ahref=UserID_url]UserID         (000) 000-0000
[ahref=UserID_url]UserID         (000) 000-0000
Concordia College
Name                                 Phone
[ahref=UserID_url]UserID         (000) 000-0000
[ahref=UserID_url]UserID         (000) 000-0000
[ahref=UserID_url]UserID         (000) 000-0000
[ahref=UserID_url]UserID         (000) 000-0000