Consolidate Query Results into Single Index

Question

I have a table (usermeta - 2nd below) that is causing my trouble. I need to get 2 values from this table in 1 query and return the results in a single array index.

One table (users) contains the following:

 ID  |    user_email    | display_name  |
-----+------------------+---------------|
  8  | bob@bobjones.com | bob jones     |
  9  | rob@robsmith.com | rob smith     |

Another table (usermeta) contains:

 user_id  |   meta_key   |  meta_value  |
----------+--------------+--------------|
    8     | phone_number |  4441234433  |
    8     | rep_id       |    abc123    |
    9     | phone_number |  5552323322  |
    9     | rep_id       |    xyz456    |

My SQL query:

function get_this() {
    global $db;
    $get = $db->get_results("   
        SELECT 
            um.meta_key, um.meta_value, um.user_id, user.user_email, user.display_name  
        FROM 
            users as user 
        LEFT OUTER JOIN 
            usermeta as um 
        ON 
            user.ID = um.user_id  
        WHERE 
            um.meta_key='rep_id'  
        OR
            um.meta_key='phone_number' 
        ");
    return $get;
}

Calling the function: $foo = get_this(); returns this array:

Array
(
    [0] => stdClass Object
        (
            [meta_key] => phone_number
            [meta_value] => 4441234433
            [user_id] => 8
            [user_email] => bob@bobjones.com
            [display_name] => bob jones
        )

    [1] => stdClass Object
        (
            [meta_key] => rep_id
            [meta_value] => abc123
            [user_id] => 8
            [user_email] => bob@bobjones.com
            [display_name] => bob jones
        )

    [2] => stdClass Object
        (
            [meta_key] => phone_number
            [meta_value] => 5552323322
            [user_id] => 9
            [user_email] => rob@robsmith.com
            [display_name] => rob smith
        )

    [3] => stdClass Object
        (
            [meta_key] => rep_id
            [meta_value] => xyz456
            [user_id] => 9
            [user_email] => rob@robsmith.com
            [display_name] => rob smith
        )

)

The problem I am having is in consolidating the data based on user_id. I'd like to return an array that includes both their phone number AND their rep id along with their user_id, user_email and display_name in one index so I can print something like: bob jones, bob@bobjones.com, 4441234433, abc123

I clearly do not know what question to ask as I've spent 3 days now researching this site and the Internet and trying various combinations of CASE, AND, OR, WHERE, UNION, GROUP etc. to no avail. I am happy to manage this on the PHP end but have failed in that department as well.

Bill_Karwin_20860 · Answer

You could put two different meta values on the same row of result by joining twice to the meta table:

SELECT user.user_id, user.user_email, user.display_name,  
 um1.meta_value AS `rep_id`,
 um2.meta_value AS `phone_number`
FROM users AS user 
LEFT OUTER JOIN usermeta AS um1
 ON user.ID = um1.user_id AND um1.meta_key = 'rep_id'
LEFT OUTER JOIN usermeta AS um2
 ON user.ID = um2.user_id AND um2.meta_key = 'phone_number'

Consolidate Query Results into Single Index

1 Answers