Czajkowski Dariusz Czajkowski Dariusz - 6 months ago 12
SQL Question

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

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

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