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!

Tags: , , ,

Wednesday, November 19th, 2008 WordPress

→ If you find this post useful please consider inviting me a cup of tea :) Thanks!

68 Responses to “Custom Query: List WordPress Users”

  1. 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!

  2. John Bedard on November 19, 2008.
  3. 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.

  4. Billy on November 20, 2008.
  5. Thanks guys, glad you like it and nice suggestion Billy :)

  6. Matt on November 20, 2008.
  7. Awesome, thanks for your great post.

  8. DeveloperFox on November 21, 2008.
  9. 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

  10. enrico on December 1, 2008.
  11. no billy but john bedard, sorry

  12. enrico on December 1, 2008.
  13. 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
    };

  14. Matt on December 1, 2008.
  15. 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!

  16. Net_Experienced on December 16, 2008.
  17. Question: How can I add the bio information to this query?

  18. Net_Experienced on December 16, 2008.
  19. @Net_Experienced The bio information should be stored in $user->description. Thanks for the nice comments.

  20. Matt on December 16, 2008.
  21. Thanks, Matt. That did the trick.

  22. Net_Experienced on December 18, 2008.
  23. 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.?

  24. carlisle on January 8, 2009.
  25. 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.

  26. Matt on January 8, 2009.
  27. 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

  28. Matteo on January 8, 2009.
  29. I put global $wpdb; and now it works !

    Bye

  30. Matteo on January 8, 2009.
  31. Thanks Matt for the tip. I used a simple css and everything works perfect now.

  32. carlisle on January 8, 2009.
  33. 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.

  34. carlisle on January 11, 2009.
  35. Hi Matt,
    What would the query look like to create a sorted list by last name?
    Thanks!

  36. jerryk on January 11, 2009.
  37. @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.

  38. Matt on January 11, 2009.
  39. 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 } ?>

  40. Matt on January 11, 2009.
  41. 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!

  42. Nathan on January 26, 2009.
  43. 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; ?>

  44. Matt on January 30, 2009.
  45. 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 );

  46. Mike on February 15, 2009.
  47. 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,

  48. Luc on March 1, 2009.
  49. 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!

  50. Matt on March 4, 2009.
  51. 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!

  52. saguado on March 4, 2009.
  53. 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

  54. Dave on March 17, 2009.
  55. 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.

  56. Nammatj on March 22, 2009.
  57. 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.

  58. john on April 6, 2009.
  59. 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.

  60. Parker on April 23, 2009.
  61. 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.

  62. Matt on April 23, 2009.
  63. 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!

  64. Parker on April 24, 2009.
  65. 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 ));

  66. Matt on April 25, 2009.
  67. 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.

  68. M Dilliott on May 2, 2009.
  69. Hi Dilliott, thanks for stopping by. I think its a simple typo mistake, try this:

    if ( is_page('members') )
    {
    paste the article code here
    };

  70. Matt on May 3, 2009.
  71. splendid, many thanks.

  72. M Dilliott on May 5, 2009.
  73. 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 !

  74. brian on May 14, 2009.
  75. 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!

  76. brian on May 14, 2009.
  77. 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?

  78. Luca Togni on June 19, 2009.
  79. 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
    }
    ?>

  80. Matt on June 19, 2009.
  81. Ok, thanks!

  82. Luca Togni on June 20, 2009.
  83. 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

  84. NetChaos on July 5, 2009.
  85. 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!

  86. Matt on July 5, 2009.
  87. Thanks for the tips, Matt

  88. NetChaos on July 8, 2009.
  89. @ NetChaos You are welcome :)
    good luck!

  90. Matt on July 13, 2009.
  91. 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.
    ?>

  92. Visualko on July 16, 2009.
  93. Hi Visualko, thanks for the comment and for sharing back how you did it.

  94. Matt on July 16, 2009.
  95. 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

  96. Fabien Thomas on July 27, 2009.
  97. YES… Thanks for this! does exactly what I’m trying to do!

  98. evilpepe on August 7, 2009.
  99. 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/

  100. anmari on August 11, 2009.
  101. You just saved me! You would believe my fight with WP database…

    Newbie stuff!

    Great tut. Thanks

  102. Joana Pereira on August 17, 2009.
  103. 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

  104. Joana Pereira on August 17, 2009.
  105. 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

  106. Amit on September 3, 2009.
  107. 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

  108. Amit on September 3, 2009.
  109. Hi Amit, Thanks for the comments and for posting your solution :)

  110. Matt on September 3, 2009.
  111. 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

  112. Amit on September 4, 2009.
  113. Matt, thank you so much! I needed this solution — you just saved me *hours* worth of puzzling. :)

  114. Corey on September 7, 2009.
  115. 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.

  116. Torsten on November 9, 2009.
  117. 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

  118. Bardo on December 9, 2009.
  119. 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

  120. Andy on March 5, 2010.
  121. 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?

  122. Andy on March 5, 2010.

Leave a Reply

About Me

Matt Varone - Matias Varone - sksmatt
HI there,

I'm a freelance creative web developer, UI designer and hobbyist musician.

Twitter Status

Flickr Gallery

    Blue WallClutter drawerCS4 Replacement iconsCS4 Replacement icons

Scrnshots Gallery

  • Screenshot from ScrnShots.com
  • Screenshot from ScrnShots.com
  • new site im working on
  • Screenshot from ScrnShots.com