How to run PDO prepared statement to insert multiple rows in a single query with PHP?

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.