PHP PDO：数组中更新SQL WHERE IN（）子句 [英] PHP PDO: Array in Update SQL WHERE IN () clause

问题描述

我试图把ID号的数组，并更新与该ID号的每一行。 PHP PDO code如下：

I'm trying to take an array of ID numbers and update every row with that ID number. PHP PDO code follows:

private function markAsDelivered($ids) {
  $update = $this->dbh->prepare("
     UPDATE notifications
     SET notified = 1
     WHERE notification_id IN (
        :ids
     )
  ");
  $ids = join(',', $ids);
  Logger::log("Marking the following as delivered: " . $ids, $this->dbh);
  $update->bindParam(":ids", $ids, PDO::PARAM_STR);
  $update->execute();
}

然而，当该运行时，仅在该列表中的第一项是得到更新，虽然正在记录多个ID号。如何修改这个更新多个行？

However, when this is run, only the first item in the list is getting updated, although multiple ID numbers are being logged. How do I modify this to update more than one row?

推荐答案

一个占位符只能重新present单个原子值。它还挺工作的原因是因为价值的mysql看到的是形式为123456，这是间$ P $点作为一个整数，但丢弃一旦遇到非数字部分（逗号）字符串的其余部分。

A placeholder can only represent a single, atomic value. The reason it kinda works is because the value mysql sees is of the form '123,456' which it interprets as an integer, but discards the rest of the string once it encounters the non numeric part(the comma).

相反，像做

$list = join(',', array_fill(0, count($ids), '?'));
echo $sql = "...where notification_id IN ($list)";
$this->dbh->prepare($sql)->execute(array_values($ids));

这篇关于PHP PDO：数组中更新SQL WHERE IN（）子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！