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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.