Custom Query: List WordPress Users
— Quick post today. I recently had been working in a site with a CMS powered by WordPress and was asked to list nicely all the full names and emails of all the active users. As I didn’t found a function/solution out of the box thought of sharing it with you.
One thing to keep in mind is that WordPress keeps users data in two separates tables, users and usermeta. The first one stores what I would say its the most relevant information of the user for the system, the username, the email, password, etc. And the second one stores all remaining data, first name, last name, etc. Depending of which plugins you have installed you could have more/different fields. To mix the information of these two tables into one object WordPress provides the get_userdata(); function, which returns an object that we can use to easily display the information we want. To call this function we need to provide a user ID number, so to list all users with their details we have to first know the IDs of them.
Custom Query
The code to do this is pretty simple and it goes like this:
<ul id="membersList">
<?php
/*
First we set how we'll want to sort the user list.
You could sort them by:
------------------------
* ID - User ID number.
* user_login - User Login name.
* user_nicename - User Nice name ( nice version of login name ).
* user_email - User Email Address.
* user_url - User Website URL.
* user_registered - User Registration date.
*/
$szSort = "user_nicename";
/*
Now we build the custom query to get the ID of the users.
*/
$aUsersID = $wpdb->get_col( $wpdb->prepare(
"SELECT $wpdb->users.ID FROM $wpdb->users ORDER BY %s ASC"
, $szSort ));
/*
Once we have the IDs we loop through them with a Foreach statement.
*/
foreach ( $aUsersID as $iUserID ) :
/*
We use get_userdata() function with each ID.
*/
$user = get_userdata( $iUserID );
/*
Here we finally print the details wanted.
Check the description of the database tables linked above to see
all the fields you can retrieve.
To echo a property simply call it with $user->name_of_the_column.
In this example I print the first and last name.
*/
echo '<li>' . ucwords( strtolower( $user->first_name . ' ' . $user->last_name ) ) . '</li>';
/*
The strtolower and ucwords part is to be sure
the full names will all be capitalized.
*/
endforeach; // end the users loop.
?>
</ul>
Conclusion
The code itself its pretty explanatory, hope you find the code useful. Cheers!
I did something like this on http://wlnhelena.org/directory/ . I’m pulling all the members (stored in the Announcements blog), adding a link to a generic profile page that fills in their info (requires them to update their profile, though) and a link to the contact page that automatically puts their name in the form. I’m a novice programmer so I’ll take a look at your code vs. mine and see if I can make mine slimmer or more efficient. Thanks!
Great tutorial! This would be perfect for creating an authors sidebar module, provided an if($user->wp_user_level >= 1) check around the echo to include all roles except subscribers.
(http://codex.wordpress.org/Function_Reference/get_userdata) for full list of object variables, (http://codex.wordpress.org/Roles_and_Capabilities) for list of roles and their capabilities.
Thanks guys, glad you like it and nice suggestion Billy :)
Awesome, thanks for your great post.
hy, sorry for my bad english… I’m a wordpress newbie and I want to know where I put the code above… I’m using wordpress 2.6.5 with k2 theme. the result that I would want to obtain is similar to that one of billy
thank a lot
no billy but john bedard, sorry
Hi enrico, you could add a page called members in the admin panel and then add anywhere you like in page.php the following code:
if ( is_page('members')
{
paste the article code here
};
Thanks so much for this.
I implemented it by turning the query into part of a custom page template. Then, I created a blank page and assigned it the template to list all of the users. I made the page private so only members of the site could access it.
Thanks again!
Question: How can I add the bio information to this query?
@Net_Experienced The bio information should be stored in $user->description. Thanks for the nice comments.
Thanks, Matt. That did the trick.
Is there a way to limit the number of members? e.g. I have over 200 users and just want to publish the recent members.
Also when I use tag, 1 user is listed per row. Is there a way to list 2 or more members in 1 row and the 3rd and 4th ones go under them etc.?
Hi Carlisle, to select the last, for example, 10 members you could try to query the users by ID descending and limit that to 10 results. Something like this might do it:
$szSort = "ID";
$aUsersID = $wpdb->get_col( $wpdb->prepare(
"SELECT $wpdb->users.ID FROM $wpdb->users ORDER BY %s DESC LIMIT 10"
, $szSort ));
And to display the users in rows you could float the li items with css and then apply some style as margin or padding.
Hi all,
Iim having this error with your code :
Fatal error: Call to a member function on a non-object in /home/public_html/wp-content/plugins/exec-php/includes/runtime.php(42) : eval()’d code on line 24
Have you got any idea ?
Thanks in advance
Cheers
I put global $wpdb; and now it works !
Bye
Thanks Matt for the tip. I used a simple css and everything works perfect now.
I will ask one last question. Let’s say we have over 100 users and want to publish 50 users in first page and 50 on a second page.
It maybe a problem for those who have too many users so they may need a kind of navigation.
Hi Matt,
What would the query look like to create a sorted list by last name?
Thanks!
@Carlisle, Agree. The best would be using mysql LIMIT in the query, and passing the page variable in the url, as most pagination system do.
Hi Jerryk, it’s tricky to sort the users by last name but of course not impossible. I think something like this might do it:
$order = 'user_nicename';
$user_ids = $wpdb->get_col("SELECT $wpdb->users.ID FROM $wpdb->users ORDER BY $order");
foreach($user_ids as $user_id) :
$user = get_userdata($user_id);
$user->full_name = ucwords(strtolower(trim($user->last_name))) . ' ' . ucwords(strtolower(trim($user->first_name)));
$aUsers[] = $user;
endforeach;
function compare($h1, $h2)
{
return strcmp($h1->full_name, $h2->full_name);
}
usort($aUsers, 'compare');
foreach ( $aUsers as $user ) { ?>
<li>
<p><?php echo $user->full_name; ?></p>
</li>
<?php } ?>
It works lovely. I do wonder however (and I’m sure if I knew more about what I was doing, I wouldn’t have to ask)… is there any way to SORT through the users and only display users with say, the last name of “doe”? Or assuming we’re using something like “Register Plus” which allows for new fields and I’ve got a Country field, can I tell the query to only display users in the United States??
Any help would be HUGELY appreciated, I’ve been scouring the internet and my php/mysql resources but I’m just not getting it!
Hi Nathan, you may want to loop first the users one time to check their country and assign the ones from USA to an array to then loop that array and display the names correctly. I had a similar situation as well, this might help you:
<?php
// ORDER BY NICENAME
$szOrder = 'user_nicename';
$aUser_ids = $wpdb->get_col("SELECT $wpdb->users.ID FROM $wpdb->users ORDER BY $szOrder");
foreach($aUser_ids as $user_id):
$user = get_userdata($user_id);
$user->full_name = trim($user->last_name) . ', ' . trim($user->first_name);
// MAKE SURE country_field IS THE NAME OF YOUR REGISTER PLUS VALUE.
foreach ( $user->country_field as $key => $value )
{
if ( $key == "united-states" )
{
$aUSAusers[] = $user;
}
endforeach;
endforeach;
// Compare function to sort them by last name - first name )
function compare($h1, $h2)
{
return strcmp($h1->full_name, $h2->full_name);
}
usort($aUSAusers, 'compare');
foreach ( $aUSAusers as $user )
{?>
<li><?php echo $user->full_name; ?></li>
<?php endforeach; ?>
I came a long way in making this work but still can’t list by last name. Here is the code I am using. Can you help me in the adjustments… Thanks
$szSort = “user_user_login”;
/*
Now we build the custom query to get the ID of the users.
*/
$aUsersID = $wpdb->get_col( $wpdb->prepare(
“SELECT $wpdb->users.ID FROM $wpdb->users ORDER BY %s ASC”
, $szSort ));
/*
Once we have the IDs we loop through them with a Foreach statement.
*/
foreach ( $aUsersID as $iUserID ) :
/*
We use get_userdata() function with each ID.
*/
$user = get_userdata( $iUserID );
Hi,
Great bit of code- did almost exactly what I’m looking for, with one problem hopefully you can help with. With WordPress MU, I’m trying to list all the editors for each blog – I need to extend your SQL so that it joins with the usermeta info somehow, to just pull out users with wp_X_capabilities (where X is the blog ID). I think it should be;
“SELECT $wpdb->users.ID FROM $wpdb->users INNER JOIN $wpdb->usermeta ON $wpdb->users.ID = $wpdb->usermeta.user_id WHERE $wpdb->users.ID != 1 AND $wpdb->usermeta.meta_key =”wp_X_capabilites” ORDER BY %s ASC”
But I get no result – any ideas?
Thanks,
Hi Luc, glad to hear you found this code useful. Im unfortunately unexperienced with WordPress MU so wont be able to give you much help. I will surely let you know if find someone to spot you in the right direction. Cheers!
Hi All,
I would like to add a pagination method to this fantastic code. We have more than 1200 users and I’m working on a directory page. At the moment I’m able to show all in one page so it’s necessary to add a page method. Can anybody let me know where can I view any example ? thank’s in advance!
Also had to make the $wpdb variable global….just for those that aren’t sure how to do this….include this line before the $wpdb variable is first used:
global $wpdb;
Hope that helps – dave
Thanks a lot!
Your suggestion from Jan. 11 worked perfectly fine for me. Could even include a fairly large table with values out of Cimy User Extra Fields where the output is created, exactly what i was looking for.
thanks for the code. It works fine except I can’t get the sort by last name portion to work. Is that meant as a replacement for the a original? or as an addon to it? if it’s an addon , where does it go? any help would be great.
Hi Matt -
I really like this bit of code, and plan on using it in a future site I’m working on.
However, I was wondering if you (or anyone else) knew of a way to search JUST the user directory, or at least a Search box that gave users the ability to search posts OR users of the site.
BTW – you should add a donate button on your site for all your great work.
Hi Parker, Thanks for the nice comment, I´m glad you found it useful. I took your suggestion and brought the “invite me a cup of tea back” :)
As for search just users I’m not sure if there’s a plugin out there for that and I honestly havent looked into it yet, as a temporal solution you could use JS to add pagination and search to the list of users. Will let you know if I come across a solution. Keep well.
Hi Matt -
Thanks for your quick reply. It is definitely starting to look like I’ll have to cobble up some sort of custom solution.
Have you checked out Pittsburgh Designers? They’ve managed to put together a pretty impressive user search, and I can’t figure it out: http://pghdesigners.com
I didn’t see the “invite a cup of tea” link anywhere!
Hi Parker, Thanks for the heads up on the cup of tea link :) I have placed it just above the comments box now. As for your original question, yes I think the best for you is to create a custom search for users. Shouldn’t be too hard unless you need advanced features. You could surely use the post’s code as a start, just have to modify it a bit. Simple solution would be to pass a variable from a form, sanitize the variable and then search for matching results. This is a very rough example without the sanitation that might help you as a start.
$CLEAN_VARIABLE_FROM_SEARCH = "%".$CLEAN_VARIABLE_FROM_SEARCH."%";
$aUsersID = $wpdb->get_col( $wpdb->prepare( "SELECT $wpdb->users.ID FROM $wpdb->users WHERE user_nicename LIKE %s ORDER BY %s ASC", $CLEAN_VARIABLE_FROM_SEARCH $szSort ));
Hi Matt,
I’m trying to do something similar to enrico with the
if ( is_page('members'){
paste the article code here
};
…this solution. I can’t get it to work. I’ve been trying to get a user list working, but PHP 4.3 seems to be holding me back. -At least from using some of the community oriented plugins out there.
When I write the code as instructed, i get this…
” Parse error: parse error, unexpected ‘{’ in ./wp-content/themes/fresh/page.php on line 17 “. I’m not all that well versed in php, so I’m sure this is my own issue, but I’ve tried a number of arrangements and syntaxes and can’t get a page generating result.
A suggestion or some guidance would be very much appreciated.
Hi Dilliott, thanks for stopping by. I think its a simple typo mistake, try this:
if ( is_page('members') )
{
paste the article code here
};
splendid, many thanks.
Sweet thx for the query mate! Do you know of any way to pass the variable in order to get the gravatr of the user? I tied
‘ . ucwords( strtolower( $user->first_name . ‘ ‘ . $user->last_name. get_avatar( $comment, 32 ) ) ) .’
but it diodn’t work I only get the generic gravatar. It doesn’t fetch the correct one based on the user… I will continu my search but if you know of any trick, let me know !
cool I have it up. heres the required snipet to use:
echo ” . get_avatar( $iUserID, $size = ‘30′ ). ”;
Another question: How would we implement a conditionnal comment that displays the gravatar as a link towards the users website when one is available?
I’ll be on this shortly but if somebody knows something about it don’t hold back and let us know :)
Thnaks again!
Hi Matt, thanks for the code, I’m using in a underdeveloped project.
Is there the possibility to exclude some users from the generated list?
Hi Luca, thanks for the comment. I would suggest to check the ID of the user right after the: “foreach ( $aUsersID as $iUserID ) :” You could have an array of IDs you want to hide to check before pulling the rest of the user information. Something like this might work:
<?php
$aExcludedIDs = ( 1, 12, 132 ); // IDs of the excluded users.
foreach ( $aUsersID as $iUserID ) :
if ( !in_array( $iUserID, $aExcludedIDs ) )
{
$user = get_userdata( $iUserID );
// ... rest of the article
}
?>
Ok, thanks!