MySQL SET query (x = x - y)

Question

In tables I have a field width type floor.

And when I do query in PHP, I want to minus from this value another number, for example:

$number = 14.3;
mysql_query("UPDATE field SET count = count - $number WHERE name = '$name');

I need to minus this $number from count, but query nothig do. I tried to put it into brackets, but the same result.

$number = intval($number); then use it inside query `count` = (`count`-$number) — , Feb 10 '13 at 11:46
@Akam since `$number` is initially just being placed into a text string, there's no need to be concerned with how `PHP` views it. In either case it's just text by the time it gets to MySQL. — mah, Feb 10 '13 at 11:52

John Woo · Accepted Answer · 2013-02-10T11:38:30.957

2

COUNT is a reserved keyword, try adding backticks. You also need to close your PHP code with double quotes.

mysql_query("UPDATE field SET `count` = `count` - $number WHERE name = '$name'");

if you don'e like to use backtick, an alternative is to supply an alias on the table.

mysql_query("UPDATE field a SET a.count = a.count - $number WHERE name = '$name'");

edited Feb 10 '13 at 11:38

answered Feb 10 '13 at 11:33

John Woo

you have also a syntax error on your php code. add double quote at the end of `name = '$name'` – John Woo Feb 10 '13 at 11:37
Double quote isn't important, all works perfect without it. For example if I minus just 1 from count, then it will work. – user2058653 Feb 10 '13 at 11:40

score 1 · Answer 2 · answered Feb 10 '13 at 11:36

1

You are missing double quote. Try this:

mysql_query("UPDATE field SET `count` = `count` - $number WHERE name = '$name'");

answered Feb 10 '13 at 11:36

Hamlet Hakobyan

2 Answers2