Outputting every possible product option

Question

I used to be able to get my head around things like this in my sleep, but since leaving the dev world 7+ years ago, I've lost the dev brain...

My scenario is that I'm trying to output every possible product option that a customer can select, and the resulting SKU - made up of each option SKU appended to the product SKU.

The data isn't stored very well, as it's a pretty archaic site.

Below is an example of how the data is stored in MySQL, and what I am trying to achieve in PHP. I've limited it to one product with many options.

products.id	products.sku
1	a

options.id	options.product_id
1	1
2	1
3	1
4	1

option_values.id	option_values.option_id	option_values.value
1	1	b
2	1	c
3	1	d
4	2	e
5	2	f
6	3	g
7	3	h
8	4	i
9	4	j
10	4	k

Iterating over every possible combination of option(s), and output the resulting SKUs;

Possible SKUs
abegi
acegi
adegi
abfgi
acfgi
adfgi
abehi
acehi
adehi
abegj
acegj
adegj
abegk
acegk
adegk
[etc]

It seems very simple when I write it out like this, which makes me wonder if I'm missing something...

I'm currently iterating over every product, and for each product every option, then for each option every value, but obviously this doesn't cater for every possible scenario.

DB Fiddle - https://www.db-fiddle.com/f/vHWiKsKi9WUvvDwAa6pqw6/0

Thank you!

Answer 1

The function to create the cartesian product of all the options is inspired by the answers to this question.

<?php

function all_skus(array $product) {
    $skus = [];
    $result = [[]];

    foreach ($product['options'] as $key => $option) {
        $append = [];
        foreach ($result as $options) {
            foreach ($option as $value) {
                $append[] = $options + [$key => $value];
            }
        }
        $result = $append;
    }

    foreach ($result as $option_set) {
        $skus[] = $product['sku'] . implode($option_set);
    }

    return $skus;
}


$pdo = new PDO(/* your stuff here */);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

$res = $pdo->query('
            SELECT `o`.`product_id`, `p`.`sku`, `ov`.`option_id`, `ov`.`value`
            FROM `products` `p`
            JOIN `options` `o` ON `p`.`id` = `o`.`product_id`
            JOIN `option_values` `ov` ON `o`.`id` = `ov`.`option_id`'
        );

$nested = [];
foreach ($res as $row) {
    $nested[$row->product_id]['options'][$row->option_id][] = $row->value;
    $nested[$row->product_id]['sku'] = $row->sku;
}

$skus = [];
foreach ($nested as $i => $product) {
    $skus = array_merge($skus, all_skus($product));
    unset($nested[$i]);
}

var_dump($skus);

If you are only interested in the sku strings you can reduce the function to:

function all_skus(array $product) {
    $result = [$product['sku']];

    foreach ($product['options'] as $option) {
        $append = [];
        foreach ($result as $options) {
            foreach ($option as $value) {
                $append[] = $options . $value;
            }
        }
        $result = $append;
    }

    return $result;
}

I am sure someone can provide a more efficient answer but this does produce the output requested, based on your example data.