PHP PDO使用占位符插入批处理多行 [英] php PDO insert batch multiple rows with placeholders

问题描述

我希望使用PHP PDO进行多次插入.

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.

我看了PHP文档站点上用于占位符的示例

I have looked at the examples on the PHP doc site for place holders

php.net pdo.prepared-statements

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

如何处理PDO和每笔交易多次插入?

我想它会以循环开始吗?

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

推荐答案

首先，?符号 是真实的占位符(大多数驱动程序允许使用位置和命名位置这两种语法) -持有人).其次，准备好的语句不过是将原始输入注入SQL语句的工具-SQL语句本身的语法不受影响.您已经拥有了所需的所有元素:

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:

• 如何通过单个查询插入多行
• 如何动态生成SQL
• 如何将准备好的语句与命名占位符一起使用.

将它们全部组合起来相当简单:

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;
    $n++;
}

if (!empty($insertQuery)) {
    $sql .= implode(', ', $insertQuery);
    $stmt = $db->prepare($sql);
    $stmt->execute($insertData);
}

这篇关于PHP PDO使用占位符插入批处理多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！