By Anonymous (not verified) on

Hi,

i want my userlist to be sorted by a profile field that contains a date.
dates in the database are saved like a:3:{s:4:"year";s:4:"2004";s:5:"month";s:1:"8";s:3:"day";s:1:"1";} so i can not sort it simply with the sql command ORDER BY.

i'm using the following code to get a userlist sorted:

<?php
    // Code from profile_browse() in drupal/modules/profile.module,v 1.154, lines 470-490
       
    // Compile a list of fields to show.
    $fields = array();
    $result = db_query('SELECT name, title, type, weight FROM {profile_fields} WHERE visibility = %d ORDER BY category, weight', PROFILE_PUBLIC_LISTINGS);
    while ($record = db_fetch_object($result)) {
      $fields[] = $record;
    }

    // Extract the affected users:
    // v.fid = 9 is the field id we want to sort by. Modify it to your needs.
    // If the field contains integer values use ORDER BY ABS(v.value) to force numeric sorting
    $result = pager_query("SELECT u.uid, u.access FROM {users} u INNER JOIN {profile_values} v ON u.uid = v.uid WHERE v.fid = 9 ORDER BY ABS(v.value)", 40, 0, NULL);

    $output = '<div id="profile">';
    while ($account = db_fetch_object($result)) {
      $account = user_load(array('uid' => $account->uid));
      $profile = _profile_update_user_fields($fields, $account);
      $output .= theme('profile_listing', $account, $profile);
    }
    $output .= '</div>';
    $output .= theme('pager', NULL, 20);

    drupal_set_title(t('user list'));
    return $output;
?>

is there any posibility to order it correctly by date?

thanks you,
Felix

⋅ Categories: Drupal 5.x

Comments

pbarnett’s picture

pbarnett commented

Hi.

Try

    $result = pager_query("SELECT u.uid, u.access FROM {users} u INNER JOIN {profile_values} v ON u.uid = v.uid WHERE v.fid = 9 ORDER BY u.access ASC", 40, 0, NULL);

Pete.

Anonymous’s picture

Anonymous (not verified) commented

it doesnt work with your code.

i was thinking a bit and because of the serialized array, that is the way the date is stored in the database, it would be imposible to sort. its kind of complicated, but it would just work to load all users into a php-array, sort it and print it later to the screen. this solution will probably work for my use. but with a great amount of users probably it would cause some errors.