Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I am trying to get an array of site users sorted by a custom meta value (an int).

I can query the users just fine and get them back to use in a foreach. I currently have the query results come back sorted already by display_name from the users table. I want it to sort by the custom usermeta value.

Any ideas on how to modify this query to get this done?

function get_all_top_members() {
    global $wpdb;
   $gather_users = "SELECT * FROM ".$wpdb->prefix."users ORDER BY display_name ASC";
$all_users = $wpdb->get_results($gather_users);
return $all_users;
}
share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

You won't get a proper sort on the meta_value column because the values stored there aren't treated as integer values, but you can use a method similar to how it was done it WordPress back when we were using meta_value_num to do sorting, which basically involved adding a number onto the front of the data.

function get_users_ordered_by_meta( $key = '', $d = 'ASC' ) {
    global $wpdb;
    $d = ( strtoupper( $d ) == 'DESC' ) ? 'DESC' : 'ASC';
    if( '' == $key )
        return;
    $r = $wpdb->get_results( $wpdb->prepare( "
        SELECT u.ID, u.display_name, u.user_nicename, um.meta_value 
        FROM {$wpdb->usermeta} um, {$wpdb->users} u
        WHERE um.user_id = u.ID
        AND um.meta_key = %s
        ORDER by (0 + um.meta_value) $d
    ", $key ) );
    return $r;
}

This then gives you a proper sort on the meta value column.

Figured you might like a function so you can call it wherever you need it, just pass it the meta key you have setup for users, and optionally change the sort order.

$yourvar = get_users_ordered_by_meta('your-key');

Sort descending.

$yourvar = get_users_ordered_by_meta('your-key', 'desc');

You'll get an array of objects returned just like before, so i'll assume you know how to loop over that data and appropriate it for display, etc...(if not, post a comment).

share|improve this answer
    
Pure genius, you my man are amazing. Worked perfectly and can now be reused for a couple other things that i need to access and sort. –  estern23 Mar 8 '11 at 0:00
add comment

try :

global $wpdb;
$gather_users = $wpdb->get_col("SELECT user_id FROM $wpdb->usermeta WHERE $wpdb->usermeta.meta_key = 'META_KEY_HERE' ORDER BY $wpdb->usermeta.meta_value ASC");
$all_users = $wpdb->get_results($gather_users);
return $all_users;

and change META_KEY_HERE to your meta key

share|improve this answer
    
Didnt work for me, thanks for the effort though! –  estern23 Mar 8 '11 at 0:02
    
what didn't work? it returns an array list of ids in a sorted manner –  Bainternet Mar 8 '11 at 0:04
    
The sort won't come out correctly like that, try it with a custom field holding a numeric sort value and watch what happens to the results. –  t31os Mar 11 '11 at 9:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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