Using PDO Prepared Statement and Incrementing a column value

Question

I've read this thread: Issues incrementing a field in MySQL/PHP with prepared statements but didn't see the answer to my problem.

PDOStatement Object
(

    [queryString] => UPDATE user_alerts SET notif = ? + 2 WHERE ( user_id = ? )   

)

$stmt->execute( array( 'notif', '1' ) );

As far as I can tell, all this is correct.

When the above code executes, it sets the notif column equal to 2 irregardless of what the value of the notif column is. It's as if the SQL is reading like SET notif = 2 instead of SET notif = notif + 2

I haven't been able to figure it out and would really appreciate help!

Answer 1

Using parameters is not just a simple text replacement. You can't replace a column name with a parameter. MySQL will interpret your query as if you had written this:

UPDATE user_alerts SET notif = 'notif' + 2 WHERE ( user_id = ? ) 

The string 'notif' is converted to zero for the addition.

Try this query instead:

UPDATE user_alerts SET notif = notif + 2 WHERE ( user_id = ? )