CODEWITHSUNDEEP
phpmysqlwordpress
Jaroslav Huss
Jaroslav Huss

Reputation: 103

Advanced Wordpress SQL Query

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?

Upvotes: 0

Views: 648

Answers (2)

Nick Coons
Nick Coons

Reputation: 3692

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.

Upvotes: 1

jh1711
jh1711

Reputation: 2328

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.

Upvotes: 1

Related Questions