Problem updating MySQL field using PHP

Question

I have a request:

Options UPDATE 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 of 5. No, it seems like it is called twice because I repeat the material to check this and get only one echo. Is there any way to get this, so PHP will execute this request only once on the Refresh page?

EDIT : thanks for the answers, I use regular MySQL, not MySQLi or PDO. Another thing I discovered is that when the request is executed, it works when you start at 0 and update to 1, but then after that it goes 3, 5, 7, ...

Answer 1

Another option: if you use firefox in general and have firbug installed, you need to disable the cache. For some reason, firbug leads to two dB calls. It took weeks to figure out where I work, since QA gets all kinds of strange results when testing. he was the only one with firebug.

Answer 2

There are several SQL interfaces in PHP for many different brands of database. You did not specify the PHP code that you use to execute the query, and you did not specify which database brand you are using.

In some SQL interfaces in PHP, creating an instruction implicitly executes SQL. Then you have the opportunity to get the results (if it was a SELECT statement). If your statement was SELECT or DELETE, it is likely that no harm was done, although there is no need to execute the statement twice. If your expression was INSERT or UPDATE, you may find that it has entered into force twice.

For example, using PDO:

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

Answer 3

This answer is redundant, but you can make sure it runs twice by including the binary logs in your mysql (remember, overkill!). Then you can use the mysqllog tool to view these files and see if the query has been executed twice.

Everyone believes that somewhere in your code it is requested twice :) I use such a request and it works fine. Another thing, I have a wrapper class for my PEAR_DB object. In the case of a request, it can display the requests (and timestamps + stack trace) that were used when rendering the current page. I use it to search for duplicate requests (like yours) and slow updates.

Answer 4

First you need to make the correct request:

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

This request should work correctly. Perhaps you received an error in the PHP code, and this request is executed twice?

Answer 5

You just need to put brackets around votes + 1 , for example:

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

I could also put LIMIT 1 at the end.

Also, tried to run a query outside of your PHP, for example, through PHPMyAdmin? Also, try repeating your SQL as a whole before running it ... you may just find the error.

Answer 6

I just created a database table to validate this query, I have to say that it works fine on my side.

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

I think my code will run twice in your application, can you try printing sql as it runs. You can also run the output SQL query from the database.

In conclusion, the sql statement perfectly executes dual application scripts.

Answer 7

I just guess, but maybe this code is executed once for each resource used on the page? Therefore, if you have an image or iframe that uses the same code, both resources execute it once, as a result of which the field is updated twice.

I only had this last week in my PDO based session handler, my test counter worked twice because the site logo was also served by php, and I updated the test counter in the auto_prepend_file file.