PHP - Associative array levels from MySQL row values

Question

The model

I've got a database with multiple categories and values associated with objects, so an element X can have a "city" with value "Sevilla", a "country" with value "Spain", and so on. The database scheme is the following:

+------------+-------------+----------+
| element_id | category_id | value_id |
+------------+-------------+----------+
|          1 |           1 |        1 |
|          1 |           2 |        5 |
|          1 |           3 |        2 |
+------------+-------------+----------+

These, of course, are linked to other tables where I store the values of that IDs.

The beginning

The user can choose to see the elements grouped by their categories, in the order the user wants. So, a user may want to see the elements grouped by "country" and, then, by "city". Or maybe by "city", and then by "neighbourhood". And so on.

The user defines the order of the groups, the user decides "what goes inside what".

The result I got after querying database

I've got a result from a query (so, I've already done the query and this would be the information I would get with fetch_object()) with the following structure (and example data):

+----+-----------+-------------+
| id |  value    | category_id |
+----+-----------+-------------+
|  1 | Spain     |           1 |
|  1 | Sevilla   |           2 |
|  2 | Portugal  |           1 |
|  2 | Lisboa    |           2 |
|  3 | Spain     |           1 |
|  3 | Barcelona |           2 |
|  4 | Spain     |           1 |
|  4 | Sevilla   |           2 |
+----+-----------+-------------+

What I'd like

What I'd like to do is to end up having an associative array in PHP like the following:

array(2) {
  ["Portugal"]=>
  array(1) {
    ["Lisboa"]=>
    array(1) {
      [0]=>
      array(1) {
        ["id"]=>
        int(2)
      }
    }
  }
  ["Spain"]=>
  array(2) {
    ["Sevilla"]=>
    array(2) {
      [0]=>
      array(1) {
        ["id"]=>
        int(1)
      }
      [1]=>
      array(1) {
        ["id"]=>
        int(4)
      }
    }
    ["Barcelona"]=>
    array(1) {
      [0]=>
      array(1) {
        ["id"]=>
        int(3)
      }
    }
  }
}

So, by order of appearance, every element with the same ID as the previous one gets into a new child level of the associative array, and end up having an array of objects of all those elements which have that same path.

Keep in mind that there can be different levels of deep, so maybe (and that is chosen by the user), can be another value under "Sevilla", so I cannot have variable for everything that can come, because it depends on the user.

I've been struggling to find out how to do it but I have not been able to. I've found array_walk() with this question but, though it's a similar case, nothing comes to my mind regarding how to apply it to get the desired result.

Thank you.

Answer 1

This data structure is ugly. Think about changing your query instead. I wrote this for $result array iteration, but it's easy to switch into fetch_object while loop (don't know what kind of object you get).

$data = [];
$city = $country = $id = null;
foreach ($result as $row) {
    if ($id != $row['id']) {
        $id = $row['id'];
        $country = $row['value'];
        isset($data[$country]) or $data[$country] = [];
        continue;
    }

    $city = $row['value'];
    isset($data[$country][$city]) or $data[$country][$city] = [];
    $data[$country][$city][] = ['id' => $id];
}