MySQL UPDATE query only updates one row using IN

Asked Oct 10 '17 at 13:45

Active Oct 10 '17 at 13:52

Viewed 47 times

I'm trying to execute a simple MYSQL query in PHP but for some reasons it doesn't work. I am trying to update multiple rows in my "notifications" table based on "notification_seen" row and "notification_id" row.

This is my notifications table:

    |user_id |notification_id|notification_seen|
    |________|_______________|_________________|
    |   1    | 1             |0                |
    |   1    | 2             |0                |
    |   1    | 3             |0                |
    |________|_______________|_________________|

What i have so far in PHP:

$uid = '1';
$not_id = '1,2,3';
$stmt = $this->conn->prepare("
UPDATE notifications 
   SET notification_seen = 1 
 WHERE user_id = :uid 
   AND notification_id IN (:not_id)
");
$stmt->execute(array(':uid'=>$uid, ':not_id' => $not_id));

For some reasons the above query only updates the last row in my table. Thank you very much.

edited Oct 10 '17 at 13:52

Filnor

1,290
2
23
28

asked Oct 10 '17 at 13:45

Mojo Allmighty

IN requires an array, you’re giving it one string. That probably will cause issues. – Sami Kuhmonen Oct 10 '17 at 13:47
Check https://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition – Saty Oct 10 '17 at 13:48
@SamiKuhmonen `in` won't take an array. – chris85 Oct 10 '17 at 13:48
[Prepared statements and IN clause](https://phpdelusions.net/pdo) – RiggsFolly Oct 10 '17 at 13:48
`1,2,3` is going in as a string, `'1,2,3'`, you need a placeholder for each value. – chris85 Oct 10 '17 at 13:49
Why don't you use a subquery in the IN clause? – Anandhu Nadesh Oct 10 '17 at 13:54

MySQL UPDATE query only updates one row using IN

0 Answers0