Link to home
Start Free TrialLog in
Avatar of lexshine
lexshine

asked on

iincrementing a value in Mysql

I am issuing the following command in PHP to a Mysql db

mysql_query("UPDATE dbname SET read_count=read_count + 1 WHERE id = 10");


The problem is, that the number in the field read_count increments by 2 everytime. So if read_count = 0, after the command it will say read_count=2

I have the db field set to INT with no other attribute. Is this a MYSQL bug or am I missing something?
Avatar of Adrian Pop
Adrian Pop
Flag of Sweden image


Hi,

You could try like this:
select (@prevReadCount:=read_count) from dbname where id=10;
update dbname set read_count=@prevReadCount+1 where id=10;

Cheers,
za-k/
Hi,

Do you have some triggers that might run the update twice or something?
Or by any chance you run the update twice and you don't notice it?
Otherwise your version should just work fine.

Try: EXPLAIN UPDATE dbname SET read_count=read_count + 1 WHERE id = 10;

Cheers,
za-k/
The code is ok (and used it in the same way), but are you sure that SQL isn't executed twice for any reasons?
mysql> select * from dbname;
+------------+------+
| read_count | id   |
+------------+------+
|          0 |   10 |
+------------+------+
1 row in set (0.00 sec)
 
mysql> UPDATE dbname SET read_count=read_count + 1 WHERE id = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from dbname;
+------------+------+
| read_count | id   |
+------------+------+
|          1 |   10 |
+------------+------+
1 row in set (0.00 sec)

Open in new window

Avatar of lexshine
lexshine

ASKER

It appears that the DB write itself is causing the strange increment.

When a regular $count++ is setup without the write, and the result is echoed the variable increments normally verifying the script does not execute twice.





adrpo:

I tried your solution and it is incrementing twice with that code..

Check if you don't have any trigger BEFORE UPDATE or AFTER UPDATE on that particular table.

za-k/
I dont see anything like that. Where would I find something ike that?

You have to login into mysql database using mysql client, then:
> show triggers;
You will get a list of triggers.
Look into the list for the table name, in your case 'dbname'.

If you have a trigger on the 'dbname' on update,
that might cause the double increment.

Cheers,
za-k/

What is the output from:
EXPLAIN UPDATE dbname SET read_count=read_count + 1 WHERE id = 10;
It might tell you more than the normal stuff.
Try to run this query or the
UPDATE dbname SET read_count=read_count + 1 WHERE id = 10;
one via mysql client, not via php, see what is happening.
It might be that your php is executed twice.

za-k/
I wasnt able to return any results with what you suggested.

I see nothing about anything being triggered, it is a normal table setup.

Mysql is not auto incrementing unless I issue the Mysql + command then it is incrementing by 2.
ASKER CERTIFIED SOLUTION
Avatar of Adrian Pop
Adrian Pop
Flag of Sweden image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
yes, on the command line I get 1 increment as well. I have to try and see if the page is somehow being called again. So far I dont see how.

Today, with no changes this is now updating by 1.

The code doing the update is the same, I am not sure if changes at other areas of the site affected it, but I will be looking into it.

adrpo: 500 to you for sticking with me through it.. thnx



I will try and figure out the abnormality from last noight and let you know. I tested the script nad it was only executing once. Why it was writing twice to the db is still unknown