php select to retrieve data from 2 tables and display on web page

Question

Table 1 - users example

ID   usr_login  user_email        display_name
1    john       john@gmail.com    John Jones    
2    steve      steve@att.com     Steve Jobs    
3    tom        tom@verizon.net   Tom Thumb

Table 2 - usermeta

umeta_id  user_id   meta_key    meta_value    
1         1         phone       8005551212    
2         1         email       john@gmail.com    
3         1         b_date      12/25    
20        2         phone       2025554567    
21        2         email       steve@att.com    
22        2         b_date      11/01    
40        3         phone       9095559876    
41        3         email       tom@verizon.net    
42        3         b_date      01/30

I am trying to display the fields on a webpage in this format

User1-DisplayName     User1-eMailAddress     User1-Phone     User1-Bdate
User2-DisplayName     User2-eMailAddress     User2-Phone     User2-Bdate
User3-DisplayName     User2-eMailAddress     User3-Phone     User3-Bdate

I can get data to display from each of the tables. But, I have not been able to get data to show from both tables together. I know enough to know that I should use a join, but can seem to get the select statement correct.

Thanks in advance, Kevin

Answer 1

Since you say you can already get data from the tables individually, I'm assuming all you need is the appropriate SQL query. To get the data you want, you need to join the users table to the usermeta table twice, once to get the phone value and once to get the b_date value. In each case the join condition is on the user_id value and the appropriate meta_key value:

SELECT u.display_name,
       u.user_email,
       p.meta_value AS phone,
       b.meta_value AS bdate
FROM users u
JOIN usermeta p ON p.user_id = u.ID AND p.meta_key = 'phone'
JOIN usermeta b ON b.user_id = u.ID AND b.meta_key = 'b_date'
ORDER BY u.ID

Output:

display_name    user_email          phone       bdate
John Jones      john@gmail.com      8005551212  12/25
Steve Jobs      steve@att.com       2025554567  11/01
Tom Thumb       tom@verizon.net     9095559876  01/30

Note I've assumed you get the user_email value from the users table; if you want to get that from usermeta as well, you would join a third time:

SELECT u.display_name,
       e.meta_value AS user_email,
       p.meta_value AS phone,
       b.meta_value AS bdate
FROM users u
JOIN usermeta e ON e.user_id = u.ID AND e.meta_key = 'email'
JOIN usermeta p ON p.user_id = u.ID AND p.meta_key = 'phone'
JOIN usermeta b ON b.user_id = u.ID AND b.meta_key = 'b_date'
ORDER BY u.ID

The output is the same for this query.

Demo on db-fiddle

Note also that I've assumed you have email, phone and b_date records in the usermeta table for each user. If you don't, you will need to change the JOINs into LEFT JOINs, and use a COALESCE to convert any resultant NULL values into empty strings e.g.

COALESCE(p.meta_value, '') AS phone

For example:

SELECT u.display_name,
       COALESCE(e.meta_value, '') AS user_email,
       COALESCE(p.meta_value, '') AS phone,
       COALESCE(b.meta_value, '') AS bdate
FROM users u
LEFT JOIN usermeta e ON e.user_id = u.ID AND e.meta_key = 'email'
LEFT JOIN usermeta p ON p.user_id = u.ID AND p.meta_key = 'phone'
LEFT JOIN usermeta b ON b.user_id = u.ID AND b.meta_key = 'b_date'
ORDER BY u.ID

Demo on db-fiddle