Sometimes, we want to run PDO prepared statement to insert multiple rows in a single query with PHP.
In this article, we’ll look at how to run PDO prepared statement to insert multiple rows in a single query with PHP.
How to run PDO prepared statement to insert multiple rows in a single query with PHP?
To run PDO prepared statement to insert multiple rows in a single query with PHP, we’ve to add the placeholders for all the rows in the SQL string.
For instance, we write
$datafields = array('fielda', 'fieldb');
$data[] = array('fielda' => 'value', 'fieldb' => 'value');
$data[] = array('fielda' => 'value', 'fieldb' => 'value');
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();
$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();
to create the placeholders
function that combines strings in $result
with thew $separator
with implode
.
And then we use a foreach loop to add the question marks for each row.
Then we put the $question_marks
into the $sql
string.
Next, we call prepare
to create a prepared statement object with $sql
.
And then we call execute
with $insert_values
to replace the question marks with values.
Finally, we call commit
to commit the transaction.
Conclusion
To run PDO prepared statement to insert multiple rows in a single query with PHP, we’ve to add the placeholders for all the rows in the SQL string.