Viewed   1.6k times

I am looking to do multiple inserts using PHP PDO.

The closest answer I have found is this one


However the example thats been given uses ?? instead of real placeholders.

I have looked at the examples on the PHP doc site for place holders pdo.prepared-statements

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

Now lets say I wanted to achieve the above but with an array

$valuesToInsert = array(
  0 => array('name' => 'Robert', 'value' => 'some value'),
  1 => array('name' -> 'Louise', 'value' => 'another value')

How would I go about it with PDO and multiple inserts per transaction?

I imagine it would start of with a loop?

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");

foreach($valuesToInsert as $insertRow){

    // now loop through each inner array to match binded values
    foreach($insertRow as $column => value){
        $stmt->bindParam(":{$column}", value);

However the above does not work but hopefully will demonstrate what im trying to achieve



First of all, ? symbols are real place-holders (most drivers allow to use both syntaxes, positional and named place-holders). Secondly, prepared statements are nothing but a tool to inject raw input into SQL statements—the syntax of the SQL statement itself is unaffected. You already have all the elements you need:

  • How to insert multiple rows with a single query
  • How to generate SQL dynamically
  • How to use prepared statements with named place-holders.

It's fairly trivial to combine them all:

$sql = 'INSERT INTO table (memberID, programID) VALUES ';
$insertQuery = array();
$insertData = array();
$n = 0;
foreach ($data as $row) {
    $insertQuery[] = '(:memberID' . $n . ', :programID' . $n . ')';
    $insertData['memberID' . $n] = $memberid;
    $insertData['programID' . $n] = $row;

if (!empty($insertQuery)) {
    $sql .= implode(', ', $insertQuery);
    $stmt = $db->prepare($sql);
Monday, August 29, 2022

You're probably looking for lastInsertId. "Returns the ID of the last inserted row or sequence value".

$insertedId = $system->db->lastInsertId() ;
Friday, November 4, 2022

Here is the right way:

function INSERT($db)
    $values = array('question goes here', -1, 'a|b|c|d|e', '01', '0|', '0|', '0|', '0', '2', 'info text', '1', '23.01.2014', '-1', '28.12.2013 15:04:03');
    $stmt = $db->prepare("INSERT INTO _mcqs VALUES(?,?,?,?)"); // adjust number of ?s
  • do not connect in the every function, but use sole connection for all the application
  • do not use try..catch to log an error - PHP will handle it better
  • do no bother with listing all the fields
  • do not bind each value separately if you already have an array
Monday, September 19, 2022

Multiple Values Insert with PDO Prepared Statements

Inserting multiple values in one execute statement. Why because according to this page it is faster than regular inserts.

$datafields = array('fielda', 'fieldb', ... );

$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);
$data[] = array('fielda' => 'value', 'fieldb' => 'value' ....);

more data values or you probably have a loop that populates data.

With prepared inserts you need to know the fields you're inserting to, and the number of fields to create the ? placeholders to bind your parameters.

insert into table (fielda, fieldb, ... ) values (?,?...), (?,?...)....

That is basically how we want the insert statement to look like.

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);

Although in my test, there was only a 1 sec difference when using multiple inserts and regular prepared inserts with single value.

Saturday, September 10, 2022

Leave the columns you don't wanto to fill with null

INSERT INTO mytable(`People`, `Places`, Things, Ideas) 
VALUES ('40', '15', null, null),(null, null, 100, 20)
Tuesday, December 13, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :