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

Answer 1

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