how to "one column of table minus 1"?

Question

i have a TABLE named FLY,i have a column in it named FLY_NUM.im trying to decrease the value of this column in a mysql query(in PHP). something like this :

mysql_query("UPDATE fly SET  `fly_Num` = `fly_Num` -1 WHERE fly_ID ='1' ");

this is wrong!everytime this query run,FLY_NUM column set to zero.how can i do such a thing?

Answer 1

Your query is valid and correct. There is also no need to put a space between the - and the 1.

Test case:

CREATE TABLE fly (fly_id int, fly_num int);

INSERT INTO fly VALUES (1, 1);
INSERT INTO fly VALUES (1, 2);
INSERT INTO fly VALUES (1, 3);
INSERT INTO fly VALUES (1, 4);
INSERT INTO fly VALUES (1, 5);
INSERT INTO fly VALUES (1, 6);
INSERT INTO fly VALUES (1, 7);

Update Query:

UPDATE fly SET `fly_Num` = `fly_Num` -1 WHERE fly_id ='1';

Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

New table contents:

SELECT * FROM fly;

+--------+---------+
| fly_id | fly_num |
+--------+---------+
|      1 |       0 |
|      1 |       1 |
|      1 |       2 |
|      1 |       3 |
|      1 |       4 |
|      1 |       5 |
|      1 |       6 |
+--------+---------+
7 rows in set (0.00 sec)

It works even if you use a varchar for the fly_num column:

CREATE TABLE fly (fly_id int, fly_num varchar(10));

INSERT INTO fly VALUES (1, '1');
INSERT INTO fly VALUES (1, '2');
INSERT INTO fly VALUES (1, '3');
INSERT INTO fly VALUES (1, '4');
INSERT INTO fly VALUES (1, '5');
INSERT INTO fly VALUES (1, '6');
INSERT INTO fly VALUES (1, '7');

Update Query:

UPDATE fly SET `fly_Num` = `fly_Num` -1 WHERE fly_id ='1';

Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

New table contents:

SELECT * FROM fly;

+--------+---------+
| fly_id | fly_num |
+--------+---------+
|      1 |       0 |
|      1 |       1 |
|      1 |       2 |
|      1 |       3 |
|      1 |       4 |
|      1 |       5 |
|      1 |       6 |
+--------+---------+
7 rows in set (0.00 sec)

Answer 2

The problem is in your PHP code.

Are you running this in a loop? Another way of asking this would be do you do anything special in you code when fly_num = 0? Phpmyadmin uses the same mysql connector as you code so the problem must be in your code not in your query.

NOTE: Another possible problem in your PHP is that you're including the file multiple times.

Every time its included it will call the database and decrement by 1.