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 COMES TO 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?

Nick Coons · Accepted Answer · 2017-08-12 23:29:54Z

1

What you want can be done with one query, but it could get messy. You may want to consider making it multiple queries. But here's what it looks like with a single query:

SELECT wp_users.user_email,
       usermeta_firstname.meta_value AS first_name,
       usermeta_lastname.meta_value AS last_name
  FROM wp_users
         INNER JOIN wp_usermeta AS usermeta_firstname
                 ON wp_users.ID = usermeta_firstname.user_id
                AND usermeta_firstname.meta_key = 'first_name'
         INNER JOIN wp_usermeta AS usermeta_lastname
                 ON wp_users.ID = usermeta_lastname.user_id
                AND usermeta_lastname.meta_key = 'last_name'

Then just keep rejoining the wp_usermeta table for each field needed. Note that if you're going to use INNER JOIN then all of the values must exist. If you want to account for potentially missing values, use LEFT JOIN instead.

edited Aug 12, 2017 at 23:29

answered Aug 12, 2017 at 22:58

Nick Coons

3,6921 gold badge21 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jaroslav Huss Over a year ago

Both of the solution will not work... I have to somehow find the way to create query, which takes a meta_value according to defined meta_key -> and this is from only one table. So lets say, I need schoolid -> than I have to select wp_usermeta -> user_metakey("schoolid") and from this defined metakey I need its meta_value. It soo complicated. Anyway, thanks for advices :-)

Nick Coons Over a year ago

Sorry, I misunderstood the question. I re-wrote my answer, so that should help.

Bradley Peterson Over a year ago

Nice! Now how do I take those results and INSERT them into a new table called "compiled_user_data"?

jh1711 · Accepted Answer · 2017-08-12 23:20:12Z

1

I feel I'm missing something. But you can just remove the WHERE clause from your sql query to get all meta values.

Or select the values you want with ORs.

WHERE wp_usermeta.meta_key = 'nickname'
OR wp_usermeta.meta_key = 'fist_name'
/*...*/

Edit:

In both cases you need to SELECT meta_key as well, so you know what the value is. Then I would use PHP to transform the result to your needs.

edited Aug 12, 2017 at 23:20

answered Aug 12, 2017 at 23:05

jh1711

2,3381 gold badge14 silver badges20 bronze badges

3 Comments

Jaroslav Huss Over a year ago

I made this code: <table> <tr><th>Email Address</th><th>Nick Name</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' OR wp_usermeta.meta_key = 'first_name' "; $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> //no work

jh1711 Over a year ago

What do you mean by no work? Also I took first_name from your question. I don't know if it might be firstname or First Name or ....Please make sure that you use the right meta key.

Jaroslav Huss Over a year ago

It works! A bit in a different way than I want to, but it works :-) Thank you.

Collectives™ on Stack Overflow

Advanced Wordpress SQL Query

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related