php array into combinations

Question

I could crack this one possibly alone, but my mind wonders after Brexit, so I decided to ask these nice people around. I have this array (or SQL results) of hardware options:

cId  cName      vId  vName
1    Processor  679  3.5GHz 6-Core
1    Processor  680  3.0GHz 8-Core
1    Processor  681  2.7GHz 12-Core
2    Memory     682  16GB
2    Memory     683  32GB
2    Memory     684  64GB
5    HDD        685  256GB
5    HDD        686  512GB
5    HDD        687  1TB
11   Graphics   688  D500 with 3GB
11   Graphics   689  D700 with 6GB

and I'm looking for the most effective way to list all combinations of options, starting with

3.5GHz 6-Core, 16GB, 256GB, D500 with 3GB

and ending with

2.7GHz 12-Core, 64GB, 1TB, D700 with 6GB

which is, I believe, 54 lines. Is there a quick way with PHP?

Or in MySQL. The input data are, in fact, in two tables - one table has category ids and names, and another has category ids, value ids, and value names (there are, of course, also model ids for another computers, but let's just stick with this Mac Pro for now).

Edit: Please note that the number of hardware categories is flexible - eg a MacBook could have only Memory and HDD options. The number of categoris should not be limited.

This is much more generalised input:

c1 = array ( v1, v2 )
c2 = array ( v3, v4, v5)

where the number of both categories, or values within a category, is flexible. So this is the result, six combinations of values:

c1 v1, c2 v3
c1 v1, c2 v4
c1 v1, c2 v5
c1 v2, c2 v3
c1 v2, c2 v4
c1 v2, c2 v5

When I add one more category

c5 = array ( v6, v7)

there will be twelve combinations:

c1 v1, c2 v3, c5 v6
c1 v1, c2 v3, c5 v7
c1 v1, c2 v4, c5 v6
c1 v1, c2 v4, c5 v7
c1 v1, c2 v5, c5 v6
c1 v1, c2 v5, c5 v7
c1 v2, c2 v3, c5 v6
c1 v2, c2 v3, c5 v7
c1 v2, c2 v4, c5 v6
c1 v2, c2 v4, c5 v7
c1 v2, c2 v5, c5 v6
c1 v2, c2 v5, c5 v7

The SQL data

are in two tables. The first one describes which hardware categories are available for given computer model:

modelID catID name
1       1     Processor
1       2     Memory
2       1     Processor

so here the computer model 2 has only one option - different processor.

Table two describes which options are available (optID is unique):

modelID catID optID name
1       1     1     i3
1       1     2     i5
1       2     3     4GB
1       2     4     8GB
1       2     5     16GB
2       1     6     i3
2       1     7     i5
2       1     8     i7

So here we can have one computer with six different combinations, and another with three. What I need as an output is a sequence of the combinations, because I will query an API for a SKU and price of these combinations. The query will be something like:

1. select model modelID and reset data (eg ?modelID=1)

2. choose catID and optID (eg ?modelID=1&catID=1&optID=1 is choosing Processor: i3)

3. repeat 2 for all available categories (so here once more with Memory: 4GB)

4. query the SKU and price (so now it would get results for i3, 4 GB)

5. repeat from 1 for all combinations for this model - six times for modelID 1 and three times for modelID 2.

This would be the total list of requests for my table above:

select?modelID=1
choose?modelID=1&catID=1&optID=1
choose?modelID=1&catID=2&optID=3
price?

select?modelID=1
choose?modelID=1&catID=1&optID=1
choose?modelID=1&catID=2&optID=4
price?

select?modelID=1
choose?modelID=1&catID=1&optID=1
choose?modelID=1&catID=2&optID=5
price?

select?modelID=1
choose?modelID=1&catID=1&optID=2
choose?modelID=1&catID=2&optID=3
price?

select?modelID=1
choose?modelID=1&catID=1&optID=2
choose?modelID=1&catID=2&optID=4
price?

select?modelID=1
choose?modelID=1&catID=1&optID=2
choose?modelID=1&catID=2&optID=5
price?

select?modelID=2
choose?modelID=2&catID=1&optID=6
price?

select?modelID=2
choose?modelID=2&catID=1&optID=7
price?

select?modelID=2
choose?modelID=2&catID=1&optID=8
price?

The result would be nine SKUs with the options stored in an array.

result = array (
  sku, price, modelID, array ( catID => optID )
)

Recursion is most likely the way to go as the number of iterations is uncertain. I found this article here which is bigger candidate for a duplicate, but I still didn't get my solution. I will probably have to make the combinations manually.

Epilogue

Eventually, the link above was very helpful about recursions but it wasn't doing everything I wanted. And at that point I realised I don't need always all the combinations - the API remembers the position of catID, so I only need to change those I have to. And it also means less calls (22 vs 33) to the API:

select?modelID=1

choose?catID=1&optID=1
choose?catID=2&optID=3
price?

choose?catID=2&optID=4
price?

choose?catID=2&optID=5
price?

choose?catID=1&optID=2
choose?catID=2&optID=3
price?

choose?catID=2&optID=4
price?

choose?catID=2&optID=5
price?

select?modelID=2

choose?catID=1&optID=6
price?

choose?catID=1&optID=7
price?

choose?catID=1&optID=8
price?

And this is the code:

<?php
function possibilities($input) {
    $current = array_shift($input);
    foreach ($current['optID'] as $optid) {
        echo "choose?catID=", $current['catID'], "&optID=", $optid, PHP_EOL;
        if (empty($input)) {
            echo "price?", PHP_EOL, PHP_EOL;
        } else {
            possibilities($input);
        }
    }
}

$models[] = array(
    'modelID' => 1,
    'options' => array(
        array('catID' => 1, 'optID' => array(1, 2)),
        array('catID' => 2, 'optID' => array(3, 4, 5))
        )
    );
$models[] = array(
    'modelID' => 2,
    'options' => array(
        array('catID' => 1, 'optID' => array(6, 7, 8))
        )
    );

foreach ($models as $model) {
    echo "select?modelID=", $model['modelID'], PHP_EOL, PHP_EOL;
    possibilities($model['options']);
}
?>

So I was able to get all the prices in a table. And I had to parse it again for all the options in a table row. I'm sure there are people who know not only how to do it in one step - but even sort the combinations to achieve total minimum of API calls.

Answer 1

After your question has highly been altered I tried to come up with another solution but I failed. I tried to create something like this. After a couple of versions I came to this:

$allCombinations = array();

$yourDB1[0] = array("modelID" => 1, "catID" => 1,"name" => "Processor");
$yourDB1[1] = array("modelID" => 1, "catID" => 2,"name" => "Memory");
$yourDB1[2] = array("modelID" => 2, "catID" => 1,"name" => "Processor");

$yourDB2[0] = array("modelID" => 1, "catID" => 1, "optID" => 1, "name" => "i3");
$yourDB2[1] = array("modelID" => 1, "catID" => 1, "optID" => 2, "name" => "i5");
$yourDB2[2] = array("modelID" => 1, "catID" => 2, "optID" => 3, "name" => "4GB");
$yourDB2[3] = array("modelID" => 1, "catID" => 2, "optID" => 4, "name" => "8GB");
$yourDB2[4] = array("modelID" => 1, "catID" => 2, "optID" => 5, "name" => "16GB");
$yourDB2[5] = array("modelID" => 2, "catID" => 1, "optID" => 6, "name" => "i3");
$yourDB2[6] = array("modelID" => 2, "catID" => 1, "optID" => 7, "name" => "i5");
$yourDB2[7] = array("modelID" => 2, "catID" => 1, "optID" => 8, "name" => "i7");

$allModels = array();
$sizeDB1 = count($yourDB1);
$sizeDB2 = count($yourDB2);

for($i = 0; $i < $sizeDB2; $i++)
{
    $allModels[$yourDB2[$i]["modelID"]][] = $yourDB2[$i];
}

$models = array_unique(array_keys($allModels));
$amountModels = count($models);

for($i = 0; $i < $amountModels; $i++)
{
    $amountCatIDs = count($allModels[$models[$i]]);

    $allCat = array();
    for($j = 0; $j < $amountCatIDs; $j++)
    {
        $allCat[$allModels[$models[$i]][$j]["catID"]][] = 1;
    }

    $amountCat = count($allCat);
    $cat = array_key($allCat);
    for($j = 0; $j < $amountCat; $j++)
    {
        //Here it is tricky because of multiple possible options of hardware
    }
}

After a lot of consideration I came to the conclusion, that another structure would be helpful. Is it an option to alter the structure? If so I suggest a structure like

Model Table:

modelID   cpuIDs   ramIDs   hdIDs   gpuIDs
1         2,5      1,2,3    1       1,2
2         2,5      1,2      1,2,4   2,3
3         3,6      2,3      2,3     1,3

CPU Table:

cpuID    name    Specs      Other
1        i3      2,0 GHz    5. generation
2        i3      1,9 GHz    4. generation
3        i5      2,5 GHz    4. generation

And the rest as the above. This will help you a lot if it is applicable for you. There is a solution for your situation but I assume it is not trivial and especially hard to maintain. Having a well strucutred database will save you a lot of trouble on the long term, but finding this is not trivial as well.

Or every model needs a default hardware for each kind if it can not be chosen. This would solve the problem where I stopped working, see the comment in the code