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

Question

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?

possible duplicate of PHP PDO: Can I bind an array to an IN() condition? — CharlesB
– CharlesB, Commented May 22, 2012 at 6:02

goat · Accepted Answer · 2012-05-12 02:47:24Z

6

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

answered May 12, 2012 at 2:47

goat

31.9k7 gold badges76 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related