Consolidate Query Results into Single Index-CodePudding

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  | [email protected] | bob jones     |
  9  | [email protected] | 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] => [email protected]
            [display_name] => bob jones
        )

    [1] => stdClass Object
        (
            [meta_key] => rep_id
            [meta_value] => abc123
            [user_id] => 8
            [user_email] => [email protected]
            [display_name] => bob jones
        )

    [2] => stdClass Object
        (
            [meta_key] => phone_number
            [meta_value] => 5552323322
            [user_id] => 9
            [user_email] => [email protected]
            [display_name] => rob smith
        )

    [3] => stdClass Object
        (
            [meta_key] => rep_id
            [meta_value] => xyz456
            [user_id] => 9
            [user_email] => [email protected]
            [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, [email protected], 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.

CodePudding user response：

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'