Query on two different tables in Wordpress

Question

Query on two different tables in Wordpress

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I'm having a code, where it returns me some data from all users of the wp_usermeta table, (in this case I'm just pulling the Full Name, and the Capabilities , however to get my table fuller, add users email, which is in the wp_users table, how can I make a query that takes the values from both tables? Here is the code:

<?php
global $wpdb;

$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' OR {$wpdb->usermeta}.meta_key = 'wp_capabilities')";
$ansatte = $wpdb->get_results($sql);

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

foreach ($users as $u) {
    echo $u['first_name'].' '.$u['last_name'].' '.$u['wp_capabilities'].'<br>';
}
?>

php mysql wordpress

asked by anonymous 18.08.2017 / 16:21

2 answers

Join of two tables query php + mysql Does anyone explain this message from git, after a pull to develop it?

score 1 · Answer 1

You do not have to do a SQL query directly for this. You can use get_users to get a list, and get_user_by() if you want a specific user.

Both return WP_User objects that have all the information you are looking for.

score 0 · Answer 2

You need to join between the tables:

This query returns the column you want:

SELECT um.user_id, um.meta_key, um.meta_value, u.user_email
FROM wp_usermeta um 
       inner join wp_users u
          on u.ID = um.user_id
  WHERE meta_key = 'first_name' 
     OR meta_key = 'last_name' 
     OR meta_key = 'wp_capabilities'