PHP PDO: Array in Update SQL WHERE IN () clause

问题:

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

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?

回答1:

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

Instead, do something like

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