Do ... for every possible combination of recordsets

Question

I need to write a php script that does ... something on every possible combination of a mysql recordset

Example:
Product "Car"
Option 1: Fuel
Option 1 values: Gasoline, Petrol
Option 2: Color
Option 2 values: Red, Blue, Green
Option 3: Doors
Option 3 values: 3, 5

I need something that does some code for each possibile combination between all options/values pair, like

Combination 1: Gasoline, Red, 3
Combination 2: Gasoline, Red, 5
Combination 3: Gasoline, Blue, 3
Combination 4: Gasoline, Blue, 5
...
Combination 10: Petrol, Blue, 5
..
Combination 100: Petrol, Green, 5

Of course, option numbers and option values numbers are dynamically generated

How should I proceed?

When you post something like this, people like to see that you've put in some effort. What exactly have you tried? — David Kiger, Feb 24 '13 at 20:43
Hi, actually I haven't tried anything, it's part of a 1000 rows php script, I tought about nested foreachs with number of foreachs=number of options (I have them in a table) but I was wondering if there's a better way — Carmine, Feb 24 '13 at 20:46

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

What you are proposing on your question is a cartesian product.

If you have an static number of arrays, you can use @Moo-Juice code, but for more complex logic, where you have to use a large amount of arrays, or even if you don't know how many arrays there will be, you can check out this question, where the first answer elaborates on an algorithm to generate the cartesian product of an arbitrary number of arrays.

edited May 23 '17 at 11:49

Community

1
1

answered Feb 24 '13 at 20:39

asermax

3,053
2
23
28

1

Also, considering this is coming from MySQL, you might also consider getting MySQL to return the cartesian product directly via a `CROSS JOIN`. Something along the lines of `SELECT * FROM FuelType CROSS JOIN ColorOption CROSS JOIN DoorOption`. – Rickard Andersson Feb 24 '13 at 20:43
thanks a lot, I'll try both ways depending of what my code needs and update this post accordingly – Carmine Feb 24 '13 at 20:48
1

@Carmine I hadn't seen the mysql tag on your question, but seems like Rickard did and his suggestion is probably the best you can do in this case, mainly because that way you can delegate the generation of the cartesian product to your DBMS instead of doing it yourself in code. – asermax Feb 24 '13 at 21:14

score 0 · Answer 2 · answered Feb 24 '13 at 20:31

0

The easiest way to do this would be in a loop for each "option". This code assumes the values for each option-set have been preloaded in to arrays.

$optionSet1 = array("Gasoline", "Petrol");
$optionSet2 = array("Red", "Blue", "Green");
$optionSet3 = array(3, 5);

foreach($optionSet1 as $o1)
{
   foreach($optionSet2 as $o2)
   {
       foreach($optionSet3 as $o3)
       {
          // Do your thing with $o1, $o2, and $o3
       }
   }
}

answered Feb 24 '13 at 20:31

Moo-Juice

38,257
10
78
128

thanks a lot, I'll try both ways depending of what my code needs and update this post accordingly – Carmine Feb 24 '13 at 20:48

Do ... for every possible combination of recordsets

2 Answers2