How can I retrieve list of users by author meta field?

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.