PDO: Prepared Multi-Inserts

This is a short tutorial on how to carry out a multi-insert with PHP’s PDO object. In this particular example, I will also be using prepared statements to guard against SQL injection.

Our example MySQL table looks like this:

1 2 3 4 5 6

CREATE TABLE IF NOT EXISTS `people` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `name` varchar(120) COLLATE utf8_unicode_ci NOT NULL,   `dob` date NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

As you can see, I’ve kept it extremely simple. Because id is an AUTO_INCREMENT column, the only columns that we need to worry about are name and dob.

For this example, I’ve created a custom function that you can reuse to your heart’s content:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

/** * A custom function that automatically constructs a multi insert statement. * * @param string $tableName Name of the table we are inserting into. * @param array $data An "array of arrays" containing our row data. * @param PDO $pdoObject Our PDO object. * @return boolean TRUE on success. FALSE on failure. */ function pdoMultiInsert($tableName, $data, $pdoObject){          //Will contain SQL snippets.     $rowsSQL = array();       //Will contain the values that we need to bind.     $toBind = array();          //Get a list of column names to use in the SQL statement.     $columnNames = array_keys($data[0]);       //Loop through our $data array.     foreach($data as $arrayIndex => $row){         $params = array();         foreach($row as $columnName => $columnValue){             $param = ":" . $columnName . $arrayIndex;             $params[] = $param;             $toBind[$param] = $columnValue;         }         $rowsSQL[] = "(" . implode(", ", $params) . ")";     }       //Construct our SQL statement     $sql = "INSERT INTO `$tableName` (" . implode(", ", $columnNames) . ") VALUES " . implode(", ", $rowsSQL);       //Prepare our PDO statement.     $pdoStatement = $pdoObject->prepare($sql);       //Bind our values.     foreach($toBind as $param => $val){         $pdoStatement->bindValue($param, $val);     }          //Execute our statement (i.e. insert the data).     return $pdoStatement->execute(); }

This function takes in three parameters:

1. The name of the table that we are inserting data into.
2. The data that we are inserting (in this case, it is a multi-dimensional array, with each array representing a row that we want to insert).
3. Our PDO object.

Step-by-step explanation of what this custom function is doing:

1. We set up an array called $rowsSQL. This array will contain SQL snippets that we will “implode” / “glue” at a later stage.
2. We set up an array called $toBind. This array will contain the parameters and values that we need to bind to our prepared statement before execution.
3. We grab that column names from the first row. Remember, all rows must have the same number of columns!
4. We loop through our $data array. Inside this loop, we construct our parameter names and we store the values that we need to bind. We also create the SQL snippets / strings that will be used in our multiple insert statement.
5. We construct our SQL statement using the $tableName variable and the SQL snippets that we stored in our $rowsSQL array.
6. We prepare our statement.
7. We loop through our $toBind array, which contains the row data that we need to bind.
8. We execute our prepared statement (this is where the multi-insert actually occurs).

Inserting multiple rows into MySQL with PDO.

OK, so our custom function is setup and it is ready for business. How do we use it?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

<?php   //Connect to MySQL with PDO. $pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');   //An array of arrays, containing the rows that we want to insert. $rowsToInsert = array(     array(         'name' => 'John Doe',         'dob' => '1993-01-04',     ),     array(         'name' => 'Jane Doe',         'dob' => '1987-06-14',     ),     array(         'name' => 'Joe Bloggs',         'dob' => '1989-09-29',     ) );   //An example of adding to our "rows" array on the fly. $rowsToInsert[] = array(     'name' => 'Patrick Simmons',     'dob' => '1972-11-12' );   //Call our custom function. pdoMultiInsert('people', $rowsToInsert, $pdo);

As you can see, it’s actually pretty simple. We create a multidimensional array that contains multiple associative arrays that represent our rows! We supply that array to our custom function, which carries out the multi-insert.