PDO Prepared Inserts multiple rows in single query

by

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);
$stmt->execute($insert_values);
$pdo->commit();

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

More Related Contents:

  1. Can I bind an array to an IN() condition in a PDO query?
  2. PHP – Using PDO with IN clause array
  3. PDO prepared statement – what are colons in parameter names used for?
  4. pdo prepared statements with wildcards
  5. Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?
  6. Get last insert id after a prepared insert with PDO
  7. How do I use pdo’s prepared statement for order by and limit clauses?
  8. Insert multiple rows with PDO prepared statements
  9. Replacing mysql_* functions with PDO and prepared statements
  10. PHP PDO::bindParam() data types.. how does it work?
  11. Binding params for PDO statement inside a loop
  12. PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table
  13. Calling stored procedure with Out parameter using PDO
  14. When *not* to use prepared statements?
  15. Which tokens can be parameterized in PDO prepared statements?
  16. Retrieve (or simulate) full query from PDO prepared statement
  17. PDO error: SQLSTATE[HY000]: General error: 2031
  18. Use of PDO in classes [duplicate]
  19. Error while using PDO prepared statements and LIMIT in query [duplicate]
  20. How to apply bindValue method in LIMIT clause?
  21. Call to a member function bind_param() on a non-object [duplicate]
  22. mysqli: can it prepare multiple queries in one statement?
  23. Binding multiple values in pdo
  24. What is the advantage of using try {} catch {} versus if {} else {}
  25. ERROR: SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it
  26. Call to undefined method PDO::execute()
  27. return one value from database with mysql php pdo
  28. Trying to access array offset on value of type bool
  29. How to get a query error from prepare() in PDO PHP?
  30. PHP PDO how to run a multiple query request?

Leave a Comment