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?
lexshineAsked:
Who is Participating?
 
adrpoConnect With a Mentor Commented:

Hi,

I tested this as hernst42 did and for both of us it increments only once.
I really believe you execute the statement twice for some unknown reason.

Cheers,
za-k/
mysql> create table dbname(id int, read_count int);
Query OK, 0 rows affected (0.13 sec)
 
mysql> insert into dbname values(10, 1);
Query OK, 1 row affected (0.05 sec)
 
mysql> select * from dbname;
+------+------------+
| id   | read_count |
+------+------------+
|   10 |          1 |
+------+------------+
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;
+------+------------+
| id   | read_count |
+------+------------+
|   10 |          2 |
+------+------------+
1 row in set (0.00 sec)
 
mysql>

Open in new window

0
 
adrpoCommented:

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/
0
 
adrpoCommented:
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/
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
hernst42Commented:
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

0
 
lexshineAuthor Commented:
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.





0
 
lexshineAuthor Commented:
adrpo:

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

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

za-k/
0
 
lexshineAuthor Commented:
I dont see anything like that. Where would I find something ike that?
0
 
adrpoCommented:

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/
0
 
adrpoCommented:

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/
0
 
lexshineAuthor Commented:
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.
0
 
lexshineAuthor Commented:
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.

0
 
lexshineAuthor Commented:
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



0
 
lexshineAuthor Commented:
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
0
All Courses

From novice to tech pro — start learning today.