PHP: Best practise / API Function to consolidate SQL data 1:N to a 2d array

I often come across a situation where I have a 1:N relation, e.g. a table of items and another table with additional metadata / attributes for every item.

Consider this example:

users
+-----------+-----------+
| user_id   | username  |
+-----------+-----------+
| 1         |  max      |
| 2         |  john     |
| 3         |  elton    |
| 4         |  tom      |
| 5         |  dave     |
+-----------+-----------+

user_profile_data
+-----+-----------+-----------+
| uid | field_id  | field_val |
+-----+-----------+-----------+
|  1  | 1         |  a        |
|  1  | 2         |  b        |
|  2  | 1         |  c        |
|  2  | 2         |  d        |
|  3  | 1         |  e        |
|  3  | 2         |  f        |
|  3  | 3         |  g        |
|  4  | 1         |  h        |
|  4  | 4         |  i        |
+-----+-----------+-----------+

Now I have two questions: I want to select extended user-data, every (uid, field_id) combination is unique, usually I do it this way:

SELECT u.user_id, u.username, upd.field_id, upd.field_val
FROM users u
LEFT JOIN user_profile_data upd ON u.user_id = upd.uid

I get a row for every user/field combination and would need to “re-sort” in php because usually I want to have an array which contains every User with a Subarray of extended attributes, for example like this:

$users = array(1 => array('username' => max, 'data' => array(1 => 'a', 2 => 'b')), 2 => array('username' => 'john', ...));

Is there a standardized way of simplifying this and/or what’s the best-practise in such cases? Is there already something build-in in PHP (not an external SQL framework)?

Thanks

Answer

Something like this should work (didn’t test, sorry)

$users = [];
foreach($results as $result) { // assuming $results contains DB results
    if (!isset($users[$result['user_id']])) {
        $user[$result['user_id']] = [];
        $user[$result['user_id']]['username'] = $result['username'];
        $user[$result['user_id']]['data'] = [];
    }
    $user[$result['user_id']]['data'][$result['field_id']] = $result['field_val'];
}

This is not generic code, you should adapt it for each table schema, but I do not know a simplier way to do it. Or you spam your SQL server by not doing “JOIN” query… :/ (I think your sql is better 🙂 )