Organizational Research By

Surprising Reserch Topic

php pdo insert batch multiple rows with placeholders using -'php,insert,pdo,bulkinsert'


php pdo insert batch multiple rows with placeholders  using -'php,insert,pdo,bulkinsert'

I am looking to do multiple inserts using PHP PDO.

The closest answer I have found is this one

how-to-insert-an-array-into-a-single-mysql-prepared-statement

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

php.net 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);
    }
}
$stmt->execute();


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

asked Sep 8, 2015 by rajesh
0 votes
33 views



Related Hot Questions

4 Answers

0 votes
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√Ę
answered Sep 8, 2015 by rajesh
0 votes
I'm assuming you are using InnoDB so this answer is only valid for that engine (or any other transaction-capable engine, meaning MyISAM isn't included). By default InnoDB runs in auto-commit mode. That means each query is treated as its own contained transaction. To translate that to something us mortals can understand, it means that every INSERT query you issue will force hard-disk to commit it by confirming it wrote down the query information. Considering how mechanical hard-disks are super slow since their input-output operation per second is low (if I'm not mistaken, the average is 300ish IO's), it means your 50 000 queries will be - well, super slow. So what do you do? You commit all of your 50k queries in a single transaction. It might not be the best solution for various purposes but it'll be fast. You do it like this: $dbh->beginTransaction(); $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)"); foreach($valuesToInsert as $insertRow) { // now loop through each inner array to match bound values foreach($insertRow as $column => value) { $stmt->bindParam(":$column", value); $stmt->execute(); } } $dbh->commit();
answered Sep 8, 2015 by rajesh
0 votes
A little modifications in solution provided by N.B $stmt->execute() should be outside of inner loop because you may have one or more columns that need to bind before calling $stmt->execute() else you 'll get exception "Invalid parameter number: number of bound variables does not match number of token". 2nd "value" variable were missing dollar signs. function batchinsert($sql,$params){ try { db->beginTransaction(); $stmt = db->prepare($sql); foreach($params as $row) { // now loop through each inner array to match bound values foreach($row as $column => $value) { $stmt->bindParam(":$column", $value); } $stmt->execute(); } db->commit(); } catch(PDOExecption $e) { $db->rollback(); } } Test: $sql = "INSERT INTO `test`(`name`, `value`) VALUES (:name, :value)" ; $data = array(); array_push($data, array('name'=>'Name1','value'=>'Value1')); array_push($data, array('name'=>'Name2','value'=>'Value2')); array_push($data, array('name'=>'Name3','value'=>'Value3')); array_push($data, array('name'=>'Name4','value'=>'Value4')); array_push($data, array('name'=>'Name5','value'=>'Value5')); batchinsert($sql,$data);
answered Sep 8, 2015 by rajesh
0 votes
Move execute inside of the loop. $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)"); foreach($valuesToInsert as $insertRow) { $stmt->execute($insertRow); } If you experience any problems with this such recommended way, you have to ask a question, describing these certain problems.
answered Sep 8, 2015 by rajesh

...