php PDO insert batch multiple rows with placeholders

Question

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

Answer 1

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 = []; $insertData = []; $n = 0; foreach ($data as $row) {     $insertQuery[] = '(:memberID' . $n . ', :programID' . $n . ')';     $insertData['memberID' . $n] = $memberid;     $insertData['programID' . $n] = $row;     $n++; }  if (!empty($insertQuery)) {     $sql .= implode(', ', $insertQuery);     $stmt = $db->prepare($sql);     $stmt->execute($insertData); }