如何使用PHP和PDO将数组插入单个MySQL预处理语句

Question

在在线注册期间，客户可以选择多个课程以进行注册。这些课程是三位数的整数，并存储在一个数组中。

例如:

我想要注册编号为155、165、175和185的课程。

我的数组设置如下：

$data = array();

$data[] = 155;

$data[] = 165;

$data[] = 175;

$data[] = 185;

在将这些信息插入关联表时，我还需要包括来自注册的其他部分的附加元素：

例如，如果我正在执行单个程序插入语句，则如下所示：

$stmt = $db->prepare("INSERT INTO table SET memberID=?, programID=?, date_added=NOW()");
$stmt->execute(array($memberid, 155));

通常我会为上面的数组创建一个简单的循环，该循环将调用多个 SQL 语句的实例并执行，例如：

for($j = 0; $j < (count($data)-1); $j++) {
   $stmt = $db->prepare("INSERT INTO table SET memberID=?, programID=?, date_added=NOW()");
   $stmt->execute(array($memberid, $data[$j]));
}

我确实意识到上面的代码是无效的（$data[$j]），但是正在寻找正确的调用方式。

之前也有人告诉过我，构建一个单一的动态SQL语句比上述的多个调用更好。我的第一次尝试可能是这样的：

$sql = array(); 
foreach( $data as $row ) {
    $sql[] = '("'.$memberid.'", "'.$row[$j].'", NOW()")';
}
mysql_real_query('INSERT INTO table (memberid, programid) VALUES '.implode(',', $sql));

但是对于PDO，我不太确定它的工作方式，特别是占位符（？）。

有什么建议吗？

- JM4

---

不要多次运行准备工作。一旦准备好了，您可以使用不同的值多次执行它。 - Powerlord

Answer 1

您可以通过编程方式构建查询...：

$sql = 'INSERT INTO table (memberID, programID) VALUES ';
$insertQuery = array();
$insertData = array();
foreach ($data as $row) {
    $insertQuery[] = '(?, ?)';
    $insertData[] = $memberid;
    $insertData[] = $row;
}

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

- ircmaxell

---

谢谢你的建议。我正在尝试它。implode 函数出现了错误，但我会告诉你结果的。 - JM4

非常好。感谢你的帮助！根据上面的示例，对于date_added字段进行思考，创建一个新的$insertdata[] = NOW()元素将无法正常插入（因为PDO会将其视为一个varchar输入，并且按照字面意义而不是mysql日期格式处理）。通常我会简单地生成查询语句：date_added = NOW()但是对于上述的值，是否可以这样做呢？我知道我可以直接运行php date(Y-m-d H:i:s)，但是想知道NOW()是否可行。 - JM4

你让它变得如此简单 +1。尊重。 - user1607528

Answer 2

2个解决方案

// multiple queries
$stmt = $pdo->prepare('INSERT INTO table SET memberID=:memberID, programID=:programID, date_added=NOW()');
$data = array(155, 165, 175, 185);
foreach($data as $d) {
    $stmt->execute(array(':memberID' => $memberid, ':programID' => $d));
}

并且

// one query
$data = array(155, 165, 175, 185);
$values = array();
foreach($data as $d) {
    $values[] = sprintf('(%d, %d, NOW())', $d, $memberid);
}
$sql = sprintf('INSERT INTO table (memberID, programID, date_added) VALUES %s', implode (', ', $values));
$pdo->exec($sql);

- Xavier Barbosa

---

感谢您的帖子，虽然第一个答案是否比后者进行了更多的查询并且对数据库的负担更大？（大多数人告诉我不要这样使用）。我将测试后者，因为它似乎更适合我的需求。值得注意的是：我完全没有做多个查询的问题，只是被告知它“较慢”（对于最多8条记录，一秒钟对我来说无关紧要）。 - JM4

多个查询会慢大约十毫秒，但它们更易读和更容易调试。 - Xavier Barbosa

Answer 3

你所需要的是如何进行批量插入，这与PDO本身关系不大，更多地涉及到SQL。

你只需要像使用*_query一样做，同时构建你的批量插入查询和参数数组。

$placeholder = array();
$values = "?, ?, ?, ...";
$args = array();
foreach ($arrays as $array) {
  $placeholder[] = $value;
  $args[] = $array['col1'];
  $args[] = $array['col2'];
  $args[] = $array['col3'];
  ...
}    
$sql = "INSERT INTO table (col1, col2, ... ) VALUES ("
     . implode('), (', $placeholder)
     . ")"; 
$stmt = $db->prepare($sql);
$db->execute($sql, $args);

这是一个丑陋但可行的算法，我想。

- Pierre

---

你很好地努力使用了正确的参数。虽然还有改进的空间，但绝对是一个不错的起点。 - Julie Pelletier