Support » Fixing WordPress » How can I retrieve list of users by author meta field?

  • Resolved pixelnate

    (@pixelnate)


    I am in desperate need of help and I am hoping that somebody can shed some light on where to start.

    A certain number of pages on this site are related to departments in this company’s operating structure and need to show contact information for the director assigned to the section that the page is related to. All the directors have been entered into the WP as authors. And since you cannot attach taxonomies to users in WP I have decided to set up an extra text field (“director”) where I can list out the page titles that each author is to appear as a director on.

    Everybody with me so far?

    I need to be able to retrieve all of the users where the page title matches one of the values in the “director” meta field. My big hurdle in getting this all working is trying to figure out how to put all of the “director” values from all of the authors into an array that I can I can use to run the loop to display the authors.

    Would one of you super-mega wordpress experts please help me figure out where to start with this? I have searched the interwebs high and low. There were umpteen places I that describe how to set up custom fields for authors and how to show authors on a page and display their custom meta data, and how to iterate over a list of all the authors on a site, but I have found nothing even closely related to finding authors by meta fields.

    This is the code for adding the director field to the user account:

    <td>
    	<input type="text" name="director" id="director" value="<?php echo esc_attr( get_the_author_meta( 'director', $user->ID ) ); ?>" class="regular-text" style="width: 492px;" /><br />
    	<span class="description">Please enter name(s) of the section(s) that the user is director of (separate entries with a comma).</span>
    </td>

    and to save it:

    function my_save_extra_profile_fields( $user_id ) {
    
    	if ( !current_user_can( 'edit_user', $user_id ) )
    		return false;
    		update_user_meta( $user_id, 'director', $_POST['director'] );
    }
Viewing 15 replies - 1 through 15 (of 25 total)
  • Here is something to get you started, but it is UNTESTED. First, get all the users and departments into an array of objects:

    $sql = "SELECT users.*, usermeta.*
    FROM $wpdb->users users, $wpdb->usermeta usermeta
    WHERE users.ID = usermeta.user_id
    AND usermeta.meta_key = 'director'";
    $users = $wpdb->get_results($sql);

    Now, assuming the post title is in $title, you can loop through the $users array and echo out the names:

    foreach ($users as $user) {
       if (strpos($user->meta_value,$title) !== false) {
          echo "<p>$user->firstname $user->lastname</p>";
       }
    }

    Thank you very much vtxyzzy. This at least gets me the array I needed to pull from. I am not getting the first and last names out of it, but I still need to play with it a bit.

    Again, thanks.

    OK, I must be dense or something. It does not appear that user first or last names are in the ‘users’ database.
    $user->firstname

    Doesn’t work.

    $user->first_name

    Doesn’t work.

    $user->user_firstname

    Doesn’t work.

    Where are the user first and last names stored?

    Sorry, my bad! The user names are in the user_meta table.

    Your query needs to look like this:

    $sql = "SELECT um1.meta_value as firtname, UM2.meta_value as lastname, UM3.meta_value as director
    FROM $wpdb->usermeta um1
    JOIN $wpdb->usermeta um2 ON (um1.user_id = um2.user_id AND um2.meta_key = 'last_name')
    JOIN $wpdb->usermeta um3 ON (um1.user_id = um3.user_id AND um3.meta_key = 'nickname')
    WHERE um1.meta_key = 'first_name'";

    That works great. Using strpos instead of breaking the director values up and putting them in an array was a stroke of genius.

    One question, though. Because the query is made up of all the joins, it only includes what is in the joins. Will this create a performance problem if I add another 5 or 6 keys to the join?

    Probably not unless your usermeta table has a lot of rows. If it does, you could do it slightly differently (UNTESTED):

    $sql = "SELECT * FROM $wpdb->usermeta
    WHERE user_id IN (SELECT um1.user_id FROM $wpdb->usermeta um1
                      WHERE um1.meta_key = 'director')
    AND meta_key IN ('first_name','last_name','director','otherkey')";
    $rows = $wpdb->get_results($sql);

    Then, loop through the rows and build your own array with one entry per user.

    $users = array();
    foreach ($rows as $row) {
       $users[$row->user_id][$row->meta_key] = $row->meta_value;
    }

    Now, you can search the $users array nearly the way you did before, using meta_key names instead of names you assigned.

    One caution with the strpos approach: you could get false hits if one title is embedded in another. For example, title1 = ‘The Money’ and title2 = ‘Show Me The Money’. There is a way around this (assuming your titles are separated by a delimiter) if you need it.

    if (strpos(",$user->meta_value,", ",$title,") !== false) {

    I’ll have to try that. Thanks again.

    One more question on this. While the latest query pulls all the meta data out of the usermeta table, I also need to get the user_id out of the table. How can I retrieve the id from the the usermeta table with the rest of the data?

    It should be in there already: $user->user_id

    Notice that the JOIN’s are on user_id.

    If I do a print_r on the $rows array I can see that, but if I do a print_r on the $users array from your example all I get are the meta field data.

    Could you perhaps give a little explanation of what this is doing?

    foreach ($rows as $row) {
    		   $users[$row->user_id][$row->meta_key] = $row->meta_value;
    		}

    I am having trouble grokking it. Perhaps I shoud just pull my values from the rows array?

    The user_id is the key to each item in the $users array.

    That piece of code loops through all the individual meta_key/meta_value rows and builds an array keyed on user_id. It takes many rows for each user and combines them into one array for each user_id.

    It is a little redundant, but you could put in an extra field in the $users array like this:

    foreach ($rows as $row) {
       $users[$row->user_id][$row->meta_key] = $row->meta_value;
       $users[$row->user_id]['user_id'] = $row->user_id;
    }

    Now, $user->user_id should work.

    I am apparently a just a disaster with custom PHP. This is what I have so far:

    $sql = "SELECT * FROM $wpdb->usermeta
        WHERE user_id IN (SELECT um1.user_id FROM $wpdb->usermeta um1 WHERE um1.meta_key = 'director')
        AND meta_key IN ('first_name','last_name','director','nickname')";
        $rows = $wpdb->get_results($sql);
    
    $users = array();
    
    foreach ($rows as $row) {
        $users[$row->user_id][$row->meta_key] = $row->meta_value;
        $users[$row->user_id]['user_id'] = $row->user_id;
    }
    
    foreach ($users as $user) {
        if (strpos($user->meta_value,$title) !== false) {
            echo "<p>$user->firstname $user->lastname</p>";
        }
    }

    And the last foreach loop responds by producing absolutely nothing, but it should. Any thoughts?

    I probably have somethin wrong in the code. As I said before UNTESTED. I will try to test and get back to you shortly.

    Now, you can search the $users array nearly the way you did before, using meta_key names instead of names you assigned.

    I think this is the problem:

    foreach ($users as $user) {
        if (strpos($user->meta_value,$title) !== false) {
            echo "<p>$user->first_name $user->last_name</p>";
        }
    }

    The user arrays are nested in the user array like they were in the rows array. So I think that would make the users array an object. Not sure how to make the user meta info usable in that case.

Viewing 15 replies - 1 through 15 (of 25 total)
  • The topic ‘How can I retrieve list of users by author meta field?’ is closed to new replies.