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!
Hi Matt,
Great tutorial.
I am planning to implement something similar, but confused how to implementing it. For example if I want to list some data using custom query on my “mywebsite.com/listing” page, what is the correct way to do it ? Create a blank page by the name “listing”, create a custom template for that page and write the query into that template page, or write a function with the query in the functions.php and call that function to the “listing” template page ? I think writing the code into the page.php with a conditional statement wont work for me because I need couple of other pages too with custom queries and putting all into the page.php file makes it a huge file which will be difficult to manage.
Please advice.
Thank You
Hi NetChaos, thanks for the nice words. In your case I would definitely encourage you to build a custom function and save it on functions.php. This way you can easily access it from any template file. The more you plan ahead the parameters your function would receive the easiest will be for you to use the same repeatedly. Keep well!
Thanks for the tips, Matt
@ NetChaos You are welcome :)
good luck!
Hello, Cool tutorial, like brian on May 14, 2009. i want to make the gravatar link to the author page, but i have troubles with ′ and “” i think, because the code print the ,
Thanks for your time.
The code i use to show the gravatar of my users is:
get_col( $wpdb->prepare(“SELECT $wpdb->users.ID FROM $wpdb->users ORDER BY %s ASC”, $szSort ));
foreach ( $aUsersID as $iUserID ) : $user = get_userdata( $iUserID );
echo ‘<a href="”‘ . ucwords( strtolower(get_avatar( $iUserID, $size = ‘45′, $border=’0′)) ). ‘‘;
endforeach; // end the users loop.
?>
Hi Visualko, thanks for the comment and for sharing back how you did it.
Hi, thanks for sharing this very useful bit of code !
however, i was wondering, if there were a solution to separate Users by role ?
I need for a plugin i’m actually developing to separate administrator and contributor and fetch datas for both but separated.
maybe you have a solution ?
And again, Thanks.
Sorry for my poor english, i did my best.
Fabien from France
YES… Thanks for this! does exactly what I’m trying to do!
For the non programmatic, the following plugin may be useful to achieve various user queries and lists:
It was recently launched for http://weblogtoolscollection.com/pluginblog/2009/07/31/amr-users-plugin-for-user-lists-and-reporting/.
It is aimed at being as configurable and generally useful as possible, so needs to be tested against various peoples different uses of the user data. I’d be interested in feedback and feature ideas.
It is of course also available at wordpress: http://wordpress.org/extend/plugins/amr-users/
You just saved me! You would believe my fight with WP database…
Newbie stuff!
Great tut. Thanks
Sorry just one more thing, could this be used to sort user my posts submitted?
If yes, how? Is it the “sort thingy” I have to change?
Cheers
Hi guys,
This is already been asked but unanswered.
Is there a way to link the retrieved names to their profile?
Thanks a lot,
ak
Hi Guys,
Ok I figured it out so I am listing it here… might be of help if someone is looking.
get_col( $wpdb->prepare(
“SELECT $wpdb->users.ID FROM $wpdb->users ORDER BY %s ASC”, $szSort ));
foreach ( $aUsersID as $iUserID ) :
$user = get_userdata( $iUserID );
// these 2 lines basically make links to found users
echo ‘ user_login ) ) . ‘”> ‘;
echo ucwords( strtolower( $user->user_login ) ) . ‘‘;
endforeach;
?>
Cheers,
ak
Hi Amit, Thanks for the comments and for posting your solution :)
Hi Matt,
You know the solution I posted above links the retrieved users to their profile. Now do you know of a way so that I can make these links another sql query to retrieve more info.
For eg: on first retrieval I get the user id or anything and I make this id a link. Upon clicking this link I retrieve more information of this user.
Thanks,
ak
Matt, thank you so much! I needed this solution — you just saved me *hours* worth of puzzling. :)
Hi Matt,
thanks for the great code! However, $wpdb should be globalized at the beginning of your code (like Matteo wrote above). This is not needed in all cases, but it makes the code work under certain circumstances.
matt, this query is exactly what i was looking for…thanks!
one question, is there a way to exclude authors from the list who have not yet added a post?
thanks again. bardo
Matt, great posting and code!
I have limited PHP skills and have inserted your code in a page template and works great – listing the users name in full.
I see a lot of people have added snippets of code to provide more functionality but cannot find where I am supposed to put/replace in the code.Could you please give another example.
I would like the avatar to show at the side of the users name and either the gravatar or the name to be a link to their profile page. I am also using Cimy User Extra Fields in the profiles and would like to know how to pull some of that info too.
Thanks for any assistance offered
Sorry, to explain a little further
In Amit’s comment on September 3, 2009. The code doesn’t work for me, just a blank page – are there extra ‘/” where their shouldn’t be?