Problem
This form of SQL is now being used on MySQL to insert several rows of values in a single query:
INSERT INTO `tbl` (`key1`,`key2`) VALUES ('r1v1','r1v2'),('r2v1','r2v2'),...
According to my PDO research, using prepared statements rather than static queries should provide me with more security.
As a result, I’d like to know if prepared statements may be used to construct “inserting numerous rows of values using a single query.”
If so, could you tell me how I may put that into practice?
Asked by hoball
Solution #1
Insert Multiple Values Using PDO Prepared Statements
Multiple values can be inserted into a single execute statement. Why? Because it is faster than ordinary insertion, according to this page.
$datafields = array('fielda', 'fieldb', ... );
$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);
$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);
You presumably have a loop that populates data if you have more data values.
erting to, and the amount of fields to bind your parameters, use the? placeholders.
insert into table (fielda, fieldb, ... ) values (?,?...), (?,?...)....
That is essentially how the insert statement should appear.
Now, the code:
function placeholders($text, $count=0, $separator=","){
$result = array();
if($count > 0){
for($x=0; $x<$count; $x++){
$result[] = $text;
}
}
return implode($separator, $result);
}
$pdo->beginTransaction(); // also helps speed up your inserts.
$insert_values = array();
foreach($data as $d){
$question_marks[] = '(' . placeholders('?', sizeof($d)) . ')';
$insert_values = array_merge($insert_values, array_values($d));
}
$sql = "INSERT INTO table (" . implode(",", $datafields ) . ") VALUES " .
implode(',', $question_marks);
$stmt = $pdo->prepare ($sql);
$stmt->execute($insert_values);
$pdo->commit();
Although, in my tests, the difference between using many inserts and typical prepared inserts with a single value was only 1 second.
Answered by Herbert Balagtas
Solution #2
Mr. Balagtas’ answer, somewhat clarified…
Versions released recently Multi-row INSERT statements are supported by MySQL and PHP PDO.
If you’re inserting into a three-column table, the SQL will look something like this.
INSERT INTO tbl_name
(colA, colB, colC)
VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?) [,...]
Even with a multi-row INSERT, ON DUPLICATE KEY UPDATE works as expected; append this:
ON DUPLICATE KEY UPDATE colA = VALUES(colA), colB = VALUES(colB), colC = VALUES(colC)
Your PHP code will follow the usual $pdo->prepare($qry) and $stmt->execute($params) PDO calls.
$params is a one-dimensional array that contains all of the values to send to the INSERT.
It should have 9 items in the above example; PDO will treat each set of three as a single row of values. (Inserting three rows of three columns each equals a nine-element array.)
The code below is written for readability rather than efficiency. If you’d like, you can use the PHP array_*() functions to better map or traverse through your data. Whether or not you can use transactions is obviously determined by the type of MySQL table you’re working with.
Assuming:
// setup data values for PDO
// memory warning: this is creating a copy all of $dataVals
$dataToInsert = array();
foreach ($dataVals as $row => $data) {
foreach($data as $val) {
$dataToInsert[] = $val;
}
}
// (optional) setup the ON DUPLICATE column names
$updateCols = array();
foreach ($colNames as $curCol) {
$updateCols[] = $curCol . " = VALUES($curCol)";
}
$onDup = implode(', ', $updateCols);
// setup the placeholders - a fancy way to make the long "(?, ?, ?)..." string
$rowPlaces = '(' . implode(', ', array_fill(0, count($colNames), '?')) . ')';
$allPlaces = implode(', ', array_fill(0, count($dataVals), $rowPlaces));
$sql = "INSERT INTO $tblName (" . implode(', ', $colNames) .
") VALUES " . $allPlaces . " ON DUPLICATE KEY UPDATE $onDup";
// and then the PHP PDO boilerplate
$stmt = $pdo->prepare ($sql);
$stmt->execute($dataToInsert);
$pdo->commit();
Answered by jamesvl
Solution #3
For what it’s worth, many users have suggested iterating through INSERT statements rather than constructing out a single string query like the selected answer did. I decided to do a quick test with only two fields and a simple insert statement:
<?php
require('conn.php');
$fname = 'J';
$lname = 'M';
$time_start = microtime(true);
$stmt = $db->prepare('INSERT INTO table (FirstName, LastName) VALUES (:fname, :lname)');
for($i = 1; $i <= 10; $i++ ) {
$stmt->bindParam(':fname', $fname);
$stmt->bindParam(':lname', $lname);
$stmt->execute();
$fname .= 'O';
$lname .= 'A';
}
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "Completed in ". $time ." seconds <hr>";
$fname2 = 'J';
$lname2 = 'M';
$time_start2 = microtime(true);
$qry = 'INSERT INTO table (FirstName, LastName) VALUES ';
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?)";
$stmt2 = $db->prepare($qry);
$values = array();
for($j = 1; $j<=10; $j++) {
$values2 = array($fname2, $lname2);
$values = array_merge($values,$values2);
$fname2 .= 'O';
$lname2 .= 'A';
}
$stmt2->execute($values);
$time_end2 = microtime(true);
$time2 = $time_end2 - $time_start2;
echo "Completed in ". $time2 ." seconds <hr>";
?>
While the overall query itself took milliseconds or less, the latter (single string) query was consistently 8 times faster or more. If this was built out to say reflect an import of thousands of rows on many more columns, the difference could be enormous.
Answered by JM4
Solution #4
When the $data array is tiny, Herbert Balagtas’ Accepted Answer works nicely. The array merge function gets excessively slow with bigger $data arrays. My $data array test file contains 28 columns and around 80,000 lines. It took 41 seconds to finish the final script.
Using array push() instead of array merge() to construct $insert values resulted in a 100X speedup, with an execution time of 0.41s.
The problematic array_merge():
$insert_values = array();
foreach($data as $d){
$question_marks[] = '(' . placeholders('?', sizeof($d)) . ')';
$insert_values = array_merge($insert_values, array_values($d));
}
Instead of using array merge(), you can construct the following two arrays:
//Note that these fields are empty, but the field count should match the fields in $datafields.
$data[] = array('','','','',... n );
//getting rid of array_merge()
array_push($insert_values, $value1, $value2, $value3 ... n );
The following are some examples of how to use these arrays:
function placeholders($text, $count=0, $separator=","){
$result = array();
if($count > 0){
for($x=0; $x<$count; $x++){
$result[] = $text;
}
}
return implode($separator, $result);
}
$pdo->beginTransaction();
foreach($data as $d){
$question_marks[] = '(' . placeholders('?', sizeof($d)) . ')';
}
$sql = "INSERT INTO table (" . implode(",", array_keys($datafield) ) . ") VALUES " . implode(',', $question_marks);
$stmt = $pdo->prepare($sql);
$stmt->execute($insert_values);
$pdo->commit();
Answered by Chris M.
Solution #5
Two possible approaches:
$stmt = $pdo->prepare('INSERT INTO foo VALUES(:v1_1, :v1_2, :v1_3),
(:v2_1, :v2_2, :v2_3),
(:v2_1, :v2_2, :v2_3)');
$stmt->bindValue(':v1_1', $data[0][0]);
$stmt->bindValue(':v1_2', $data[0][1]);
$stmt->bindValue(':v1_3', $data[0][2]);
// etc...
$stmt->execute();
Or:
$stmt = $pdo->prepare('INSERT INTO foo VALUES(:a, :b, :c)');
foreach($data as $item)
{
$stmt->bindValue(':a', $item[0]);
$stmt->bindValue(':b', $item[1]);
$stmt->bindValue(':c', $item[2]);
$stmt->execute();
}
I would use the second solution if all of the data for all of the rows is contained in a single array.
Answered by Zyx
Post is based on https://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query