WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] How can I retrieve list of users by author meta field? (26 posts)

  1. pixelnate
    Member
    Posted 3 years ago #

    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'] );
    }
  2. vtxyzzy
    Member
    Posted 3 years ago #

    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>";
       }
    }
  3. pixelnate
    Member
    Posted 3 years ago #

    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.

  4. pixelnate
    Member
    Posted 3 years ago #

    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?

  5. vtxyzzy
    Member
    Posted 3 years ago #

    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'";
  6. pixelnate
    Member
    Posted 3 years ago #

    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?

  7. vtxyzzy
    Member
    Posted 3 years ago #

    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) {

  8. pixelnate
    Member
    Posted 3 years ago #

    I'll have to try that. Thanks again.

  9. pixelnate
    Member
    Posted 3 years ago #

    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?

  10. vtxyzzy
    Member
    Posted 3 years ago #

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

    Notice that the JOIN's are on user_id.

  11. pixelnate
    Member
    Posted 3 years ago #

    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?

  12. vtxyzzy
    Member
    Posted 3 years ago #

    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.

  13. pixelnate
    Member
    Posted 3 years ago #

    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?

  14. vtxyzzy
    Member
    Posted 3 years ago #

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

  15. vtxyzzy
    Member
    Posted 3 years ago #

    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>";
        }
    }
  16. pixelnate
    Member
    Posted 3 years ago #

    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.

  17. vtxyzzy
    Member
    Posted 3 years ago #

    I just tested this, and it works (except I had to fake the 'director' value):

    foreach ($users as $user) {
       $firstname = $user['first_name'];
       $lastname = $user['last_name'];
       $director = $user['director'];
        if (strpos($director,$title) !== false) {
            echo "<p>$firstname $lastname</p>";
        }
    }
  18. pixelnate
    Member
    Posted 3 years ago #

    It does indeed work. Brilliantly. Thank you.

    But I have one more request. Could you please show me how to do a join on the query to include fields from the user table so that I can make use of the a user's email when showing the rest of the user's info?

    This is what the whole of the code looks like now:

    $title = get_the_title();
    
    $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',
            'sub_director',
            'job_title',
            'office_phone_area',
            'office_phone_prefix',
            'office_phone_suffix',
            'mobile_phone_area',
            'mobile_phone_prefix',
            'mobile_phone_suffix',
            'fax_area',
            'fax_prefix',
            'fax_suffix'
        )";
        $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) {
        $director_id = $user['user_id'];
        $firstname = $user['first_name'];
        $lastname = $user['last_name'];
        $director = $user['director'];
        $sub_director = $user['sub_director'];
        $job_title = $user['job_title'];
        $email = $user['user_email'];
        $phone1 = $user['office_phone_area'];
        $phone2 = $user['office_phone_prefix'];
        $phone3 = $user['office_phone_suffix'];
        $mphone1 = $user['mobile_phone_area'];
        $mphone2 = $user['mobile_phone_prefix'];
        $mphone3 = $user['mobile_phone_suffix'];
        $fax1 = $user['fax_area'];
        $fax2 = $user['fax_prefix'];
        $fax3 = $user['fax_suffix'];
    
        if (strpos($director,$title) !== false) {
            echo userphoto_thumbnail($director_id);
            echo "<p><span>$firstname $lastname</span>";
            if ($job_title !== '') { echo "<br /><span class=\"position\">$job_title</span>"; }
            if ($phone1 !== '' && $phone2 !== '' && $phone3 !== '') { echo "<br /><span class=\"phone\">($phone1) $phone2 - $phone3</span>"; }
            if ($mphone1 !== '' && $mphone2 !== '' && $mphone3 !== '') { echo "<br /><span class=\"phone\">($mphone1) $mphone2 - $mphone3</span>"; }
            if ($fax1 !== '' && $fax2 !== '' && $fax3 !== '') { echo "<br /><span class=\"phone\">($fax1) $fax2 - $fax3</span>"; }
    
        }
    }
  19. vtxyzzy
    Member
    Posted 3 years ago #

    I think this should do it:

    $sql = "SELECT $wpdb->usermeta.*, u.user_email, u.display_name FROM $wpdb->usermeta
    JOIN $wpdb->users u ON ($wpdb->usermeta.user_id = u.ID)
        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',
            'sub_director',
            'job_title',
            'office_phone_area',
            'office_phone_prefix',
            'office_phone_suffix',
            'mobile_phone_area',
            'mobile_phone_prefix',
            'mobile_phone_suffix',
            'fax_area',
            'fax_prefix',
            'fax_suffix'
        )";
       $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;
            $users[$row->user_id]['user_email'] = $row->user_email;
            $users[$row->user_id]['display_name'] = $row->display_name;
        }
  20. pixelnate
    Member
    Posted 3 years ago #

    This works perfectly! Thank you very much.

    Do you have a paypal account? I'd like to buy you a beer.

  21. vtxyzzy
    Member
    Posted 3 years ago #

    Thanks for the offer, but just donate to your favorite charity. And, please use the dropdown at top right to mark this topic 'Resolved'.

  22. pixelnate
    Member
    Posted 3 years ago #

    OK, I have just one more question. How can I order the results of the query by the user 'last_name' values?

    $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',
    			'sub_director',
    			'job_title',
    			'office_phone_area',
    			'office_phone_prefix',
    			'office_phone_suffix',
    			'mobile_phone_area',
    			'mobile_phone_prefix',
    			'mobile_phone_suffix',
    			'fax_area',
    			'fax_prefix',
    			'fax_suffix'
    		)
    		ORDER BY $wpdb->usermeta . 'last_name' ASC
    		";

    This isn't working.

  23. vtxyzzy
    Member
    Posted 3 years ago #

    That won't work because the last_name occurs in only one row out of many for each user_id. You have to sort the $users array after collecting the data for each user into a single entry, which is done with this code:

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

    I will have to test a sort of the $users array and post it later.

  24. vtxyzzy
    Member
    Posted 3 years ago #

    OK - here is the code to sort the $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;
       $users[$row->user_id]['user_email'] = $row->user_email;
       $users[$row->user_id]['display_name'] = $row->display_name;
    }
    function sort_by_lastname ($a, $b) {
      return strnatcmp($a['last_name'],$b['last_name']);
    }
    usort($users,'sort_by_lastname');

    Now the $users array should be in order by last_name. But, note that it is no longer keyed on user_id!! Instead, it is keyed on an index number. The user_id is now just a field in each row: $users['user_id'].

  25. pixelnate
    Member
    Posted 3 years ago #

    Once again, you save my life on this project. Thanks.

    It just goes to show me that I need to bone up on PHP. I went looking for something that did what usort does and couldn't find it. Help me, though, understand how the strnatcmp command works in that function. Is it comparing every last name against every other last name? The whole $a $b this what is confusing me. Strnatcmp only accepts two parameters, so you can't just do a usort on the $users array, but I wouldn't have thought you could use it in that way.

  26. vtxyzzy
    Member
    Posted 3 years ago #

    usort calls the function 'sort_by_lastname' with pairs of array elements which are called $a and $b in the function.

    If $a['last_name'] is less than $b['last_name'], strnatcmp returns a number less than zero. That number less than zero tells usort that the first element should sort lower than the second.

    If $a['last_name'] is greater than $b['last_name'], strnatcmp returns a number greater than zero. That number greater than zero tells usort that the first element should sort higher than the second.

    usort sends pairs of elements until the array is in order.

    And, if your offer of a beer still holds, my daughter needs heart surgery, so if you would go to emily.mcdspot.com and make a small donation, it would be greatly appreciated.

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.