Restructure 2d query result into a multidimensional hierarchial associative array

Question

I am developing a CodeIgniter application and I need to restructure a query result coming from a model method so produce a multi-level, hierarchial associative array.

My model provides the following payload:

$array = [
    ['id' => 1, 'category' => 'Pizza', 'product' => 'Large Pizza', 'complement_type' => 'Bread', 'option' => 'Brown bread'],
    ['id' => 2, 'category' => 'Pizza', 'product' => 'Small Pizza', 'complement_type' => 'Bread', 'option' => 'White bread'],
    ['id' => 3, 'category' => 'Pizza', 'product' => 'Small Pizza', 'complement_type' => 'Ingredients', 'option' => 'Olives'],          
    ['id' => 4, 'category' => 'Salads', 'product' => 'Green Salad', 'complement_type' => 'Extras', 'option' => 'Bacon'],
    ['id' => 5, 'category' => 'Salads', 'product' => 'Cesars Salad', 'complement_type' => 'Extras', 'option' => 'Lettuce'],
];

I need to nest the data in a particular order:

category -> product -> complement_type = option

My desired structure:

array (
  'Pizza' => 
  array (
    'Large Pizza' => 
    array (
      'Bread' => 'Brown bread',
    ),
    'Small Pizza' => 
    array (
      'Bread' => 'White bread',
      'Ingredients' => 'Olives',
    ),
  ),
  'Salads' => 
  array (
    'Green Salad' => 
    array (
      'Extras' => 'Bacon',
    ),
    'Cesars Salad' => 
    array (
      'Extras' => 'Lettuce',
    ),
  ),
)

I think I have to loop it with a for() loop, maybe add a while() loop to get the changes in the categories, but I just can't seem to find the way to do it.

Would this help? http://stackoverflow.com/questions/8840319/build-a-tree-from-a-flat-array-in-php — Sergey Vidusov, Feb 20 '16 at 23:14
Two questions: 1) your original array is associative or not? 2) what mysql driver do you use to perform query: pdo, mysqli or mysql? — fusion3k, Feb 20 '16 at 23:27
im using codeigniter, i think is mysqli, look, you can enter this link https://dobleslash.com/TakeEatEasy/API/getRest?id=1 and see my results, its different data from what i put here because its in spanish, but i think you can get a pretty good idea — kastulo, Feb 20 '16 at 23:36

fusion3k · Accepted Answer · 2023-02-21T12:36:01.213

First of all: your original array — as posted is comment — if an array of objects: this preliminar note is to understand the syntax ->, otherwise obscure simply reading your main question.

Assuming your table results are stored in array $array, you can try in this way:

$result = array();
foreach( $array as $row )
{
    if( !isset( $result[ $row->category ] ) ) 
    {
        $result[ $row->category ] = array();
    }
    if( !isset( $result[ $row->category ][ $row->product ] ) )
    {
        $result[ $row->category ][ $row->product ] = array();
    }
    $result[ $row->category ][ $row->product ][ $row->complement_type ] = $row->option;
}

At beginning, I init an empty array. Then — through a foreach loop, I process each element of $array and — if primary key (the product) doesn't exist in the result array, I create it as empty array; then I perform same action for category subkey (Large Pizza, etc). At last, I add the complement type.

3v4l.org demo

mickmackusa · Answer 2 · 2023-02-25T12:25:50.273

This entire task can be accomplished in a single processing line of code via a body-less foreach loop implementing "array destructuring" syntax.

Code: (Demo)

$result = [];
foreach ($array as ['category' => $c, 'product' => $p, 'complement_type' => $t, 'option' => $result[$c][$p][$t]]);
var_export($result);

I have also demonstrated this technique at another page: While destructuring an array, can the same element value be accessed more than once?

For a more traditional look, you can push values into the result array path without conditionally declaring empty parent arrays.

Here is a snippet that iterates an array of objects in case you built your CI query result from the result() helper method. Demo

$result = [];
foreach ($array as $obj) {
    $result[$obj->category][$obj->product][$obj->complement_type] = $obj->option;
}
var_export($result);

Restructure 2d query result into a multidimensional hierarchial associative array

2 Answers2