Group and count product/inventory result set to form multidimensional hierarchical array

Question

How can I convert the rows of my database table into a multidimensional, hierarchical structure to count unique stock items?

My database resembles this:

CREATE TABLE stock (
    model VARCHAR(100),
    storage VARCHAR(100),
    color VARCHAR(100)
);

INSERT INTO stock VALUES
('iPhone 14 Pro', '128gb', 'Black'),
('iPhone 14 Pro', '256gb', 'Red'),
('iPhone 14 Pro', '128gb', 'Black'),
('iPhone 14 Pro', '256gb', 'Black'),
('iPhone 14 Pro', '256gb', 'Red'),
('iPhone 14 Pro', '128gb', 'White'),
('iPhone 13 Pro', '128gb', 'Black'),
('iPhone 14 Pro', '256gb', 'Red');

So my query's result set looks like this:

[
    ['model' => 'iPhone 14 Pro', 'storage' => '128gb', 'color' => 'Black'],
    ['model' => 'iPhone 14 Pro', 'storage' => '256gb', 'color' => 'Red'],
    ['model' => 'iPhone 14 Pro', 'storage' => '128gb', 'color' => 'Black'],
    ['model' => 'iPhone 14 Pro', 'storage' => '256gb', 'color' => 'Black'],
    ['model' => 'iPhone 14 Pro', 'storage' => '256gb', 'color' => 'Red'],
    ['model' => 'iPhone 14 Pro', 'storage' => '128gb', 'color' => 'White'],
    ['model' => 'iPhone 13 Pro', 'storage' => '128gb', 'color' => 'Black'],
    ['model' => 'iPhone 14 Pro', 'storage' => '256gb', 'color' => 'Red']
]

I want to create a parent-child structured array from the model, storage, and color values with the lowest level containing the total number of items with that specific combination of values.

[
    'iPhone 14 Pro' => [
        '128gb' => [
            'Black' => 2,
            'White' => 1,
        ],
        '256gb' => [
            'Red' => 3,
            'Black' => 1,
        ],
    ],
    'iPhone 13 Pro' => [
        '128gb' => [
            'Black' => 1,
        ],
    ],
]

I tried using some nested loops with too many foreach statements and it didn't work at all. I think I was using the array_push() method incorrectly because I never got the correct result

Please share the code that you have already tried by [edit]ing your question — Nick, Aug 28 '23 at 00:11
You want to do this with PHP or at DB with aggregate functions? Maybe something like `select model, storage, color, count(*) from table group by model, storage, color` — user3783243, Aug 28 '23 at 01:16
Related: [Convert array with 3-element rows into hierarchical array with 3 levels](https://stackoverflow.com/q/19911822/2943403) — mickmackusa, Aug 29 '23 at 21:59
Related: [Convert associative array to multidimensional array with value](https://stackoverflow.com/q/14514198/2943403) — mickmackusa, Sep 01 '23 at 07:54

score 1 · Accepted Answer · answered Aug 28 '23 at 00:52

Assuming the defining factors, like model, storage and color are columns in the database, you could create a simple function like the following

function generateProductArray($database) {
    $result = array();

    foreach ($database as $entry) {
        $model = $entry['model'];
        $storage = $entry['storage'];
        $color = $entry['color'];

        if (!isset($result[$model])) {
            $result[$model] = array();
        }

        if (!isset($result[$model][$storage])) {
            $result[$model][$storage] = array();
        }

        if (!isset($result[$model][$storage][$color])) {
            $result[$model][$storage][$color] = 0;
        }

        $result[$model][$storage][$color]++;
    }

    return $result;
}

You also have the ability to simplify the functionality/code using inbuilt PHP functions like array_reduce( ):

function generateProductArray($database) {
    return array_reduce($database, function ($result, $entry) {
        [$model, $storage, $color] = array_values($entry);

        $result[$model][$storage][$color] = ($result[$model][$storage][$color] ?? 0) + 1;

        return $result;
    }, []);
}

Which provides the desired output.

Array
(
    [iPhone 14 Pro] => Array
        (
            [128gb] => Array
                (
                    [Black] => 2
                )

            [256gb] => Array
                (
                    [Blue] => 1
                    [Red] => 2
                )

        )

)

Example

[It is not necessary to declare parent elements before pushing child elements when using square brace syntax. `extract()` is simpler than `[$model, $storage, $color] = array_values($entry);`](https://stackoverflow.com/a/77011754/2943403) — mickmackusa, Aug 30 '23 at 22:01

Cyclonecode · Answer 2 · 2023-08-28T01:10:09.277

Not sure how you data is structured, but something like this might work:

$data = [
  [
    'model' => 'iPhone 14 Pro',
    'storage' => '128gb',
    'color' => 'Black',
  ],
  [
    'model' => 'iPhone 14 Pro',
    'storage' => '128gb',
    'color' => 'Green',
  ],
  [
    'model' => 'iPhone 14 Pro',
    'storage' => '256gb',
    'color' => 'Blue',
  ],
  [
    'model' => 'iPhone 14 Pro',
    'storage' => '256gb',
    'color' => 'Blue',
  ],
];

$devices = [];
foreach ($data as $phone) {
  list($model, $storage, $color) = array_values($phone);
  $count = $output[$model][$storage][$color] ?? 0;
  $output[$model][$storage][$color] = ++$count;
}

The above would create an array like this:

Array
(
    [iPhone 14 Pro] => Array
        (
            [128gb] => Array
                (
                    [Black] => 1
                    [Green] => 1
                )

            [256gb] => Array
                (
                    [Blue] => 2
                )

        )

)

[`extract()` is simpler than `list($model, $storage, $color) = array_values($phone);`](https://stackoverflow.com/a/77011754/2943403) — mickmackusa, Aug 30 '23 at 22:00

mickmackusa · Answer 3 · 2023-09-01T07:56:19.587

All of the below snippets deliver the exact same result array.

If you are making a trip to the database solely to populate this aggregated, hierarchical data structure (you don't need to access the original rows itemized rows), then SQL is well-equipped to perform the counting.

I'll demonstrate how to use a GROUP BY query with COUNT() to simplify your result set, iterate over the retrieved rows, generate convenient variables with extract(), then make the hierarchical declarations.

Code: (PHPize Demo)

$sql = <<<SQL
SELECT model,
       storage,
       color,
       COUNT(*) count
FROM stock
GROUP BY model,
         storage,
         color
SQL;

$result = [];
foreach ($mysqli->query($sql) as $row) {
    extract($row);
    $result[$model][$storage][$color] = (int) $count;
}
var_export($result);

Of course, if you don't like that style, you can effectively write the same technique in the body of the loop.

This approach assumes you know exactly which columns of data that you want to collect and you want to hardcode the specific order of the columns in the parent-child relationships.

If you prefer to work with a non-aggregated result set, iterate over the retrieved rows, generate convenient variables with extract(), then perform the counting operation by adding 1 to the stored count (or zero if encountering the element for the first time).

Code: (PHPize Demo)

$result = [];
foreach ($mysqli->query("SELECT * FROM stock") as $row) {
    extract($row);
    $result[$model][$storage][$color] = ($result[$model][$storage][$color] ?? 0) + 1;
}
var_export($result);

If you prefer functional code styling, use array_reduce(), but you'll need to explicitly fetch the result set. It's too verbose for my taste versus a classic foreach() loop.

Code: (PHPize Demo)

var_export(
    array_reduce(
        $mysqli->query("SELECT * FROM stock")->fetch_all(MYSQLI_ASSOC),
        function($result, $row) {
            extract($row);
            $result[$model][$storage][$color] = ($result[$model][$storage][$color] ?? 0) + 1;
            return $result;
        }
    )
);

Finally, if you want to enjoy a fully dynamic snippet and rely on the order of the columns in the result set to dictate the levels of the hierarchical structure, you can use reference variables in a nested loop.

Code: (PHPize Demo)

$result = [];
foreach ($mysqli->query("SELECT model, storage, color FROM stock") as $row) {
    $ref = &$result;
    foreach ($row as $value) {
        $ref = &$ref[$value];
    }
    $ref = ($ref ?? 0) + 1;
}
var_export($result);

_{All of the techniques found in this answer could also be received by asking your favorite code-generating AI tool. However, I found that when I checked with ChatGPT, it wrote needless isset() checks on each level as demonstrated in @Darren's answer. It also incorrectly implemented the reference-based technique. Before asking a question on Stack Overflow without a coding attempt, spend 120 seconds and ask your favorite AI tool for help.}

score -1 · Answer 4 · answered Aug 28 '23 at 09:51

Creating a multidimensional array to represent different combinations of attributes can be achieved using nested loops.

$models = ['iPhone 14 Pro'];
$storages = ['128gb', '256gb'];
$colors = ['Black', 'Blue'];

$result = [];

foreach ($models as $model) {
    foreach ($storages as $storage) {
        foreach ($colors as $color) {
            $result[] = [
                'model' => $model,
                'storage' => $storage,
                'color' => $color
            ];
        }
    }
}

// Now $result contains all combinations of model, storage, and color

print_r($result);

your result will look like this

Array
(
    [0] => Array
        (
            [model] => iPhone 14 Pro
            [storage] => 128gb
            [color] => Black
        )

    [1] => Array
        (
            [model] => iPhone 14 Pro
            [storage] => 128gb
            [color] => Blue
        )

    [2] => Array
        (
            [model] => iPhone 14 Pro
            [storage] => 256gb
            [color] => Black
        )

    [3] => Array
        (
            [model] => iPhone 14 Pro
            [storage] => 256gb
            [color] => Blue
        )
)

Your sample input structure does not resemble the asker's input structure. — mickmackusa, Aug 29 '23 at 02:37
And your result is actually what the OP already has in their database. This answer is not helpful. — mickmackusa, Aug 30 '23 at 22:16

Group and count product/inventory result set to form multidimensional hierarchical array

4 Answers4

All of the below snippets deliver the exact same result array.