WordPress Wednesday: Displaying Users by Last Name

Geek Factor: 4

In WordPress 3.1, WP_User_Query() was introduced. This new function allows you to create a query of your WP users, and order them in a variety of ways (for a more detailed explanation, please check out this post by Matt Varone).

Unfortunately the ordering options are a bit limited — they include items like the user’s login, nicename, email, URL, etc. Other potential options — including first name and last name — are not available. (My guess is that it’s because the values you can use to order users are stored in individual columns in the database, whereas first name, last name, etc. are stored as name-value pairs in the meta-value column in a separate table — can any SQL geeks confirm this?)

Now, if you are forward-thinking enough to make your users’ logins or nicenames start with their last name, bravo! You can order your authors by last name in a relatively painless fashion. If you are not, like me, you’re in luck: in this week’s WordPress Wednesday, we’re covering how to display users using other ordering options.

I put together the following example from code by the talented folks in the WordPress Support Forums. Below, we are gathering all of our users with the ‘author’ role (they have a wp_user_level of ‘2’) and sorting by last name:

[php]
$authors = $wpdb->get_col(
“SELECT um2.user_id
FROM {$wpdb->usermeta} um1
LEFT JOIN {$wpdb->usermeta} um2
ON um1.user_id = um2.user_id
WHERE um1.meta_key = ‘wp_user_level’
AND um1.meta_value = ‘2’
AND um2.meta_key = ‘last_name’
ORDER by um2.meta_value ASC”
);
?>
[/php]

If you’re not familiar with SQL queries or the WordPress database tables, the above probably seems a bit intimidating. In short: the user information in WordPress is stored in two different tables. One holds the basic information — user ID, nicename, email, etc. The other holds the metadata, including first and last name, and the user role. The code ‘joins’ the information from the two tables and nabs information about the user from both, including their last name; it then orders the query by that last name value. If you’d like to find out more about this kind of query, WordPress has a section on using Custom Select Queries in the Codex.

Once the query has been created, you can then loop through the array it returns to display information. Here is a simple foreach() loop going through each of our users; it displays the user’s name and description from their bio. For more options, check out WordPress’s information about the_author_meta():

[php]
foreach($authors as $author) {
$user = get_userdata($author); ?>

first_name.’ ‘.$user->last_name; ?>

user_description; ?>


[/php]

Using the first snippet of code, you can order by any user meta value, including custom fields you have added yourself.

Overall, this is a little more convoluted than a straight-up WP_User_Query(), but is very handy when creating an author page that can be sorted in a variety of ways.

Have you run into a query you need that doesn’t actually exist in WordPress? How did you solve the issue? Please share in the comments!

  1. Jeff F. said:

    Thank you for this! As a note, I was getting the error “Fatal error: Call to a member function get_col() on a non-object” using this in functions.php until adding “global $wpdb;” before the query. Thought I would add this to the comments in case anyone else experiences the same thing.

    @ 1:17 pm
  2. Jeff – thanks for the heads up!

    @ 10:51 am
  3. Tom said:

    Thanks for this. Worked like a charm.

    @ 1:34 pm

Leave a Comment

Note: Fields marked with a * are required; email addresses are not published.