Advanced Wordpress SQL Query

Question

after 4 hours I am not able to find solution, how to correctly write the SQL query to echo data from two tables.

I have two tables and I need to "JOIN" theirs data.

First table is wp_users and the second is wp_usermeta.

From wp_users all I need is user_email (So it would be SELECT user_email FROM wp_users).

But I need to join the other table called wp_usermeta where I need a lot of stuff like first_name, last_name, phone_number, schoolid, schoolname etc.

And here is the problem :-)

In wp_usermeta I need to read data from two columns - meta_key and meta_value. Column meta_key stores the name of the variable (for example schoolid and column meta_value stores the value of the meta_key (for example XXXXXXXX).

I have this code:

 <table>
        <tr><th>Email Address</th><th>First Name</th></tr>
    <?php
$teacher_table = "SELECT wp_users.user_email, wp_usermeta.meta_value 
 FROM wp_users INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
 WHERE wp_usermeta.meta_key = 'nickname'";


$results_table_main = $wpdb->get_results($teacher_table);

 foreach ($results_table_main as $value){
  echo '<tr><td>'.$value->user_email .'</td>';
 echo '<td>'.$value->meta_value .'</td>';
 } ?>
    </table>

This works, but I have no idea how to modify it to get more meta_keys with their meta_values.

Any ideas?

Answer 1

In WP, you rarely need to "write SQL queries", unless you work with custom tables, instead use its API...

WP_User_Query will join for you all meta data attached to each user and return to you plain user objects, so why not use it instead?

Also, see get_user_meta()...