Not adding 1, but 2, 3 or 4 when UPDATEing mysql with PHP

Question

I have this table called classes:

+------------+----------+------+-----+----------------+
| Field      | Type     | Null | Key | Extra          |
+------------+----------+------+-----+----------------+
| class_id   | int(3)   | NO   | PRI | auto_increment |
| class_level| int(1)   | YES  |     |                |
| class_name | char(1)  | YES  |     |                |
+------------+----------+------+-----+----------------+

With data inside like this:

+----------+-------------+------------+
| class_id | class_level | class_name |
+----------+-------------+------------+
| 1        | 0           | N          |
| 2        | 1           | A          |
| 3        | 1           | B          |
| 4        | 2           | C          |
| 5        | 2           | D          |
| 6        | 3           | E          |
| 7        | 3           | F          |
+----------+-------------+------------+

With PHP I want to increment all values inside class_level except 0. So I made this PHP/MySQL function:

mysql_query("UPDATE classes SET class_level = (class_level + 1) WHERE class_level != 0") or die(mysql_error());

This (what is weird) does not add 1 to each class_level except theese equal to 0, but adds 2 or 3 or 4! I haven't found a rule, that this script would add either 2 or 3 or 4. This is RANDOMLY picked. And there is no error outputted too.

All it does it adds randomly 2 or 3 or 4 to each row.

So, to debug it, I have done this PHP code to add to each one by one:

$query = mysql_query("SELECT * FROM `classes` WHERE `class_level` != 0");

while ($row = mysql_fetch_assoc($query)) {
    $class_id = $row['class_id'];

    $class_level = $row['class_level'];
    $class_level = $class_level + 1;
    var_dump($class_level);

    mysql_query("UPDATE `classes` SET `class_level` = '$class_level' WHERE `class_id` = '$class_id'") or die(mysql_error());
}

The output from var_dump is:

int(2) int(2) int(3) int(3) int(4) int(4)

But in database in table I get following result:

+----------+-------------+------------+
| class_id | class_level | class_name |
+----------+-------------+------------+
| 1        | 0           | N          |
| 2        | 4           | A          |
| 3        | 4           | B          |
| 3        | 5           | C          |
| 4        | 5           | D          |
| 5        | 6           | E          |
| 6        | 6           | F          |
+----------+-------------+------------+

This is an empty file with just MySQL connection and the code above, so there is no loop above it.

Here is my version information: PHP version: 5.2.12, MySQL Client API version 5.1.44. Note that I cannot install mysqli nor PDO.

EDIT:

Just after executing the MySQL query I have outputted data from table, and the result was, as it should be. But in table itself (or on refresh with code just for output) there was 3 added, not 1!

EDIT 2:

I tried executing this MySQL query from command line (aka Webmin tool for SQL commands) and the result was, as it should be: 1 was added.

Answer 1

EDIT

Added SQL Fiddle demonstration: http://sqlfiddle.com/#!9/efa05b/1

create table classes 
( class_id    int(3) not null auto_increment primary key comment 'pk'
, class_level int(1)
, class_name  char(1)
)
;
insert into classes (class_id,class_level,class_name) values
 ('1','0','N')
,('2','1','A')
,('3','1','B')
,('4','2','C')
,('5','2','D')
,('6','3','E')
,('7','3','F')
;
update classes set class_level = (class_level + 1) where class_level != 0
;

A query verifies that expected result is returned. Each row (other than row with class_level=0) has been updated, with class_level incremented by exactly 1.

select * from classes order by class_id

class_id class_level class_name
-------- ----------- ----------
1        0           N
2        2           A
3        2           B
4        3           C
5        3           D
6        4           E
7        4           F

---

original answer

Assuming that there isn't a BEFORE/AFTER UPDATE trigger on the table, given the SQL statement being executed:

UPDATE classes SET class_level = (class_level + 1) WHERE class_level != 0

The most logical explanation for the behavior is that the SQL statement is being executed multiple times within the function, or, the function is being called multiple times.

To verify this, you could temporarily turn on the MySQL general log, run the test, disable the general log, and review... and you're going to find multiple executions of the statement. If you don't have access to the MySQL server, then the next best would be to echo out a line immediately before the statement is executed; you're going to see that line echoed out multiple times.

Highly unlikely that this is a bug in the MySQL database. (Verify there isn't a TRIGGER defined on the table.)

Also, test that statement using a different client, like mysql command like client, or phpmyadmin. And verify the statement is working correctly.

---

FOLLOWUP

Given that your test of the SQL statement from a different client gives the expected results, I think this demonstrates this isn't a problem with the MySQL database or the SQL statement.

For some reason, that SQL statement is being executed multiple times. As a next step in debugging, I would add some more code. I'd temporarily create a "log" table, using MyISAM engine, containing an auto_increment id, a datetime column, and an informational string:

 CREATE TABLE debug_log
 ( id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
 , dt   DATETIME
 , info VARCHAR(40)
 ) Engine=MyISAM

Then add another SQL statement in your function to insert a row to that table, immediately before and/or after the execution of the UPDATE statement

INSERT INTO debug_log (dt, info) VALUES (SYSDATE(), 'my function before update')

Then, run the test, and see how many rows get inserted into the debug_log table.

To get an error thrown if this statement is executed more than one time, supply a fixed value for the id column, e.g.

INSERT INTO debug_log (id, dt, info) 
VALUES (123, SYSDATE(), 'my function before update')

With the fixed value for id, if that statement gets called a second time, then MySQL will throw a duplicate key exception.

As I mentioned previously, based on the information provided, I suspect that your function is being called multiple times. (I don't have sufficient information to actually make that determination; that's just a hunch.)

Answer 2

Here is the simple solution below

<?php
  print("<h1>BEFORE</h1>");
  $q =mysql_query("select *from classes ");
  while($row=mysql_fetch_array($q)){
  print("$row['class_id'] - $row['class_level'] - row[$class_name] <br/>");
  }

 mysql_query("UPDATE classes SET class_level = class_level+1 WHERE class_level>0") or die(mysql_error()); 


 print("<h1>AFTER</h1>");

  $q =mysql_query("select *from classes ");
  while($row=mysql_fetch_array($q)){
  print("$row['class_id'] - $row['class_level'] - row[$class_name] <br/>");
  }


?>

You dnt need to write any single php line to do what you are actually want to do.

Ok i have updated the code try this way. First it will get data & show . Secondly ,it will update the data .finally,display the data. Try this way hope you can find your problem.

Answer 3

I solved this by simply doing something like this:

mysql_query("UPDATE classes SET class_level = 2 WHERE class_level = 1");
mysql_query("UPDATE classes SET class_level = 3 WHERE class_level = 2");
mysql_query("UPDATE classes SET class_level = 4 WHERE class_level = 3");

I have just those three classes so it gets the job done.

It isn't the way I wanted to go with, but it works. The bug was really odd and I'd rather not go back to it. I hope this helps someone though.

---

P.S. How could I possibly not think about that in the first place XD