5

I'm trying to fetch both the first_name and last_name of all users at the same time, using the wpdb-object and the wp_usermeta table. I can't seem to figure out how to get both in the same query. Below is what I've got so far.

global $wpdb;
    $ansatte = $wpdb->get_results("SELECT user_id, meta_value AS first_name FROM wp_usermeta WHERE meta_key='first_name'");

    foreach ($ansatte as $ansatte) {
        echo $ansatte->first_name;
    }

Using the above code I'm able to echo out the first names of all users, but i would like for the last_name to be available aswell, like so;

foreach ($ansatte as $ansatte) {
        echo $ansatte->first_name . ' ' $ansatte->last_name;
    }

Any ideas?

1
  • 2
    If you must use raw SQL, which I advise against, don't hard code the table names, and use those provided by the $wpdb object, else you risk compatability issues across sites and multisite installs
    – Tom J Nowell
    Jun 2, 2015 at 22:46

3 Answers 3

6

I can't find a clean, native way to pull this data. There are a couple of ways I can think of to do this: First, something like:

$sql = "
  SELECT user_id,meta_key,meta_value
  FROM {$wpdb->usermeta} 
  WHERE ({$wpdb->usermeta}.meta_key = 'first_name' OR {$wpdb->usermeta}.meta_key = 'last_name')";
$ansatte = $wpdb->get_results($sql);
var_dump($sql);
$users = array();
foreach ($ansatte as $a) {
  $users[$a->user_id][$a->meta_key] = $a->meta_value;
}
var_dump($users);

You can then do:

foreach ($users as $u) {
    echo $u['first_name'].' '.$u['last_name'];
}

... to echo your user names.

The second way, a more pure SQL way, is what you were attempting:

$sql = "
  SELECT {$wpdb->usermeta}.user_id,{$wpdb->usermeta}.meta_value as first_name,m2.meta_value as last_name
  FROM {$wpdb->usermeta} 
  INNER JOIN {$wpdb->usermeta} as m2 ON {$wpdb->usermeta}.user_id = m2.user_id
  WHERE ({$wpdb->usermeta}.meta_key = 'first_name' 
  AND m2.meta_key = 'last_name')";
$ansatte = $wpdb->get_results($sql);

foreach ($ansatte as $ansatte) {
  echo $ansatte->first_name . ' ' . $ansatte->last_name;
}

You could also use get_users() or WP_User_Query to pull users but the meta_data you want isn't in the data returned and it would take more work to retrieve it.

6
  • Doesn't it return all users with fist_name = Me AND last_name = Myselfandi?
    – sakibmoon
    Jun 2, 2015 at 18:53
  • Yes, it does. Isn't that what you asked for?
    – s_ha_dum
    Jun 2, 2015 at 18:55
  • 1
    I didn't ask that question and I don't think that's what this question asks for. Please take a closer look at the question. I believe it's an interesting question.
    – sakibmoon
    Jun 2, 2015 at 18:57
  • Yes, you are correct @sakibmoon
    – s_ha_dum
    Jun 2, 2015 at 18:58
  • Thank you! So, if i remove those two values I'll be getting what I'm after? I've figured out an alternative solution, quering for the user_id and using get_user_meta($ansatte->user_id, 'first_name', true) and get_user_meta($ansatte->user_id, 'last_name', true) to fetch both names. But thats probably not ideal.
    – danjah
    Jun 2, 2015 at 19:20
6

This is a straight MySQL pivot answer for phpMyAdmin assuming your Wordpress table prefix is "wp_";

SELECT
  t1.id, t1.user_email,
  MAX(CASE WHEN t2.meta_key = 'first_name' THEN meta_value END) AS first_name,
  MAX(CASE WHEN t2.meta_key = 'last_name' THEN meta_value END) AS last_name 
FROM wp_users AS t1 
INNER JOIN wp_usermeta AS t2 ON t1.id = t2.user_id
GROUP BY t1.id, t1.user_email;
0
0

This is the correct approach using just SQL:

SELECT id
     , (SELECT MAX(meta_value) FROM wp_usermeta WHERE ID = wp_usermeta.user_id AND meta_key = 'first_name') first_name
     , (SELECT MAX(meta_value) FROM wp_usermeta WHERE ID = wp_usermeta.user_id AND meta_key = 'last_name') last_name
  FROM wp_users

Here are approaches that don't work:

  • JOIN once for each metadata field does not work because there is no UNIQUE key on wp_usermeta(user_id, meta_key)
  • Using subquery SELECT without MAX fails because there is no UNIQUE key on wp_usermeta(user_id, meta_key)

And for some reason this is faster than using a single select and join.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.