php PDO insert batch multiple rows with placeholders

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

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

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