How to insert into a table all combinations of values in PHP arrays with MySQL

Question

Suppose I have a table T with 3 columns like this:

x | y | z
---------

I also have PHP arrays of variable lengths $x, $y and $z. Let's say for the purpose of illustration that $x = array("A1", "A2"), $y = array("B1", "B2") and $z = array("C1", "C2").

What the best way to insert into T all combinations of $x, $y and $z, where the value of each column in each row is taken from the array with the same name? Thus, in my example, if T was empty beforehand, it should look like this after this operation:

x  |  y  |  z
-------------
A1   B1    C1
A1   B2    C1
A1   B1    C2
A1   B2    C2
A2   B1    C1
A2   B2    C1
A2   B1    C2
A2   B2    C2

Again, the length of each array is not fixed in advance and needn't be the same for all of them, so the solution can't assume a specific length for those arrays.

One way to do that is to create a two-dimensional array in PHP that contains all the combinations, use implode to generate the VALUES clause of the INSERT INTO statement and execute the resulting query, but I would be surprised if there wasn't a way to do this entirely in SQL and if this weren't more efficient. To be honest, performance isn't really an issue for what I'm doing (because there aren't going to be more than a few hundreds combinations at most in each case), but I'm still curious.

EDIT: Based on the replies, it seems I wasn't clear enough about what I was asking for, so let me elaborate. I know how to prepare one or several SQL queries to insert all the possible combinations into T by looping over the arrays in PHP. But what I would like to know is how to write a SQL query based on the inputs in the PHP arrays that will generate the cartesian product and insert that product into T, which I'm guessing would be more efficient because presumably MySQL is going to generate the cartesian product faster than I could in PHP.

Answer 1

I think the most straight-forward way to do this would be to have three nested loops that generate the VALUES of the INSERT statement.

The following code is meant to be illustrative. Take care that you don't run into common SQL vulnerabilites like e.g. SQL Injections (as mentioned in some of the comments below) should you ever put this code to production!

Try it like this:

<?php
$x = ["A1", "A2"];
$y = ["B1", "B2"];
$z = ["C1", "C2"];

foreach ($x as $i) {
    foreach ($y as $j) {
        foreach ($z as $k) {
            echo "INSERT INTO `T` (`x`, `y`, `z`) VALUES ('$i', '$j', '$k');\n";
        }
    }
}
?>

The output of this would be

INSERT INTO `T` (`x`, `y`, `z`) VALUES ('A1', 'B1', 'C1');
INSERT INTO `T` (`x`, `y`, `z`) VALUES ('A1', 'B1', 'C2');
INSERT INTO `T` (`x`, `y`, `z`) VALUES ('A1', 'B2', 'C1');
INSERT INTO `T` (`x`, `y`, `z`) VALUES ('A1', 'B2', 'C2');
INSERT INTO `T` (`x`, `y`, `z`) VALUES ('A2', 'B1', 'C1');
INSERT INTO `T` (`x`, `y`, `z`) VALUES ('A2', 'B1', 'C2');
INSERT INTO `T` (`x`, `y`, `z`) VALUES ('A2', 'B2', 'C1');
INSERT INTO `T` (`x`, `y`, `z`) VALUES ('A2', 'B2', 'C2');

Upate:

To solve this entirely in MySQL you could use CROSS JOIN to get a cartesian product (A B C). You will first have to define the three sets (A, B and C) and insert your data like this:

CREATE TABLE `A` (`x` VARCHAR(100));
CREATE TABLE `B` (`y` VARCHAR(100));
CREATE TABLE `C` (`z` VARCHAR(100));
INSERT INTO `A` VALUES('A1'), ('A2');
INSERT INTO `B` VALUES('B1'), ('B2');
INSERT INTO `C` VALUES('C1'), ('C2');

Now you can query the cartesian product with CROSS JOIN:

SELECT * FROM `A` CROSS JOIN `B` CROSS JOIN `C`;
+------+------+------+
| x    | y    | z    |
+------+------+------+
| A1   | B1   | C1   |
| A2   | B1   | C1   |
| A1   | B2   | C1   |
| A2   | B2   | C1   |
| A1   | B1   | C2   |
| A2   | B1   | C2   |
| A1   | B2   | C2   |
| A2   | B2   | C2   |
+------+------+------+
8 rows in set (0,00 sec)

And I just learned from the MySQL Reference Manual that CROSS JOIN is an syntactic equivalent for JOIN aswell as INNER JOIN so these would work the same way:

 SELECT * FROM `A` JOIN `B` JOIN `C`;
 SELECT * FROM `A` INNER JOIN `B` INNER JOIN `C`;

I hope that this is a better answer for what you were aiming at :-)