mysql query two database tables, users and usermeta and sort by custom meta int value

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).

Related Posts: