1

I'm new to PDO, and I am trying to make a button, where, when the user pushes it, the value of a corresponding number is increased by one. 

$statement = "UPDATE table SET counter=? WHERE id=?";
$z = $db->prepare($statement);
$z->execute(array($counter+1,$id));

$id is being sent correctly, but I don't know how to add one to the counter.

000
  • 26,951
  • 10
  • 71
  • 101
Noah T
  • 235
  • 4
  • 12
  • 6
    can't you just use: `"UPDATE table SET counter=counter + 1 WHERE id=?"` – Samuel Cook Jul 15 '13 at 16:55
  • @SamuelCook: Ideally in a serializable transaction...otherwise, you can't really trust the result. – cHao Jul 15 '13 at 16:57
  • @cHao, Samuel's answer does not require a transaction of any kind, it is an atomic operation and is completely predictable. – Geoffrey Jul 15 '13 at 17:07
  • @Geoffrey: You have a link to info about that? I haven't found anything in the docs yet about it... – cHao Jul 15 '13 at 17:15
  • @cHao: see http://stackoverflow.com/questions/4358732/is-incrementing-a-field-in-mysql-atomic – Geoffrey Jul 15 '13 at 17:17

1 Answers1

7

You can let MySQL perform the update for you, no need to fetch the counter and update it in MySQL..

UPDATE table SET counter = counter + 1 WHERE id = ?

This is also atomic, so multiple hits at the same time wont cause counts to be missed. If you need to insert the record if it does not exist first, you could change this query to the following, provided you have a unique/primary key on the 'id' column:

INSERT INTO table (id, counter) VALUES (?, 1) ON DUPLICATE KEY UPDATE counter = counter + 1

This will both deal with inserting the record if it does not exist already, and if it does, update it. This is more efficient then executing multiple queries to check if the record exists first and insert if missing.

See here for more information: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Geoffrey
  • 10,843
  • 3
  • 33
  • 46
  • Where's the requirement for `INSERT` or `UPDATE`? – Jason McCreary Jul 15 '13 at 16:59
  • @JasonMcCreary - Answer was just updated to cover the initial requirement as well as this handy extra. – Geoffrey Jul 15 '13 at 17:03
  • If `counter` isn't a field in a table of just counters (say, if it's a table of user info), it'd be a pretty bad idea to just up and create a new record if an incorrect ID is passed... – cHao Jul 15 '13 at 17:03
  • @cHao - Please see the part that reads `If you need to insert the record if it does not exist first`. – Geoffrey Jul 15 '13 at 17:06
  • Thanks. I have it set to have a default 0 if the field is empty, so I'm just using the first one, but they'll both be useful. – Noah T Jul 15 '13 at 17:09