Problem with updating a MySQL field with PHP

Question

I have a query:

UPDATE choices SET votes = votes + 1 WHERE choice_id = '$user_choice'

But when I execute it in my script, the votes field is updated twice, so the votes will go from 4 to 6 instead to 5. It doesn't seem that it is getting called twice because I echo out stuff to test this and only get one echo. Is there a way to have it so PHP will only execute this query once per page "refresh"?

EDIT: Thanks for the responses, I'm using regular MySQL, no MySQLi or PDO. Another thing I found is that when doing the query, it works when you start out with 0 and update to 1, but then after that it goes 3, 5, 7, ...

Answer 1

There are several SQL interfaces for many different brands of database in PHP. You haven't shown the PHP code you use to execute the query, nor have you identified which brand of database you use.

In some SQL interfaces in PHP, creating the statement implicitly executes the SQL. Then you have the opportunity to fetch results (if it was a SELECT statement). If your statement was a SELECT or DELETE, it's likely that no harm was done, though it's unnecessary to execute the statement twice. If your statement was an INSERT or UPDATE, though, you may find it has taken effect twice.

For example, using PDO:

$pdo = new PDO(...options...);
$stmt = $pdo->query('UPDATE ...'); // executes once
$stmt->execute(); // executes a second time

Answer 2

This answer is an overkill, but you can make sure it's executed twice, by enabling the binary logs in your mysql (remember, overkill!). You can then use mysqllog tool to view these files and see if the query was executed twice.

Everybody believes somewhere in your code it's being queried twice :) I'm using such query and it's working perfectly. Another thing, I have a wrapper class for my PEAR_DB object. If asked for, it can output the queries (and timestamps+stack trace) that were used when rendering the current page. I use it to find duplicate queries (like yours) and slow updates.

Answer 3

First you need to make proper query:

UPDATE `choices` SET `votes` = `votes` + 1 WHERE `choice_id` = '$user_choice'

This query must work properly. Maybe you got error in PHP code and this query executes two times?

Answer 4

You may just need to put brackets around the votes + 1 such as:

UPDATE choices SET votes = (votes + 1) WHERE choice_id = '$user_choice';

I might also put a LIMIT 1 at the end.

Also, have tried running the query outside of your PHP, like through PHPMyAdmin? Also, try echoing out your SQL in whole before running it...you may just find an error.

Answer 5

One other option is, if you are using firefox at all and have firbug installed you need to disable your cache. For some reason firbug results in two calls to the dB. Took weeks to figure this out where I work as QA was getting all kinds of strange results when testing. he was the only one with firebug.

Answer 6

I just created the a database table to test this query, I must say it is running fine on my side.

UPDATE choices SET votes = ( votes +1 ) WHERE choice_id = '1'

I think the code my be executed twice in your application, can you try and print out the sql as it is being ran. You can also run the outputted sql statement natively against your database.

To conclude , The sql statement is fine just double your application scripts.

Answer 7

I'm just guessing here but maybe this code is executed once for every resource used on a page? So if you have an image or iframe that uses the same code both resources execute it once, resulting in the field being updated twice.

I had this just last week in my PDO based session handler, my test counter would fire twice because the site logo is also served by php and i was updating the test counter in the auto_prepend_file.

Answer 8

Hi guys ... not sure if the OP has been solved this problem, but I would just like to confirm that the Firebug issue suggested by Don appears to be the best solution. Worked for me.

D