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:
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:
/** * 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:
- The name of the table that we are inserting data into.
- 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).
- Our PDO object.
Step-by-step explanation of what this custom function is doing:
- We set up an array called $rowsSQL. This array will contain SQL snippets that we will “implode” / “glue” at a later stage.
- 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.
- We grab that column names from the first row. Remember, all rows must have the same number of columns!
- 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.
- We construct our SQL statement using the $tableName variable and the SQL snippets that we stored in our $rowsSQL array.
- We prepare our statement.
- We loop through our $toBind array, which contains the row data that we need to bind.
- 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?
<?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.
Further reading on the PDO object: