mysql command - subtract (-1)

[adammc]

Hi,

Can anyone please help me with this sql query?

This code is for use in a shopping cart, I am not sure how to get it to subtract '1' from the 'quantity' row in the table.

[php]
include("../CMS/script-files/db-connect.php");

$SQL = " UPDATE products SET";
$SQL = $SQL . " quantity = '? MINUS 1' WHERE id = '$id'";

#execute SQL statement
$result = mysql_db_query($db,"$SQL",$cid);

# check for error
if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n"); }

mysql_close($cid);
[/php]

[AndreJul]

Try This:
UPDATE products SET quantity = quantity - 1 WHERE id = '#*$!';

[adammc]

Hi,

Thanks for the reply :)

What type does the 'quantity' field need to be for this to work?

I did a test and quantity now equals 'quantity - 1' instead of a number?

[AndreJul]

Of course it would
IF you use varchar or char data types, you will update it into the string
You use something like this right?

UPDATE table SET field='a string' WHERE.....

to enable quantity -1 to work, you have to use number data types such as Byte,Integer etc...., so it would be like:

UPDATE table SET field=field-1 WHERE.....

Take note of the missing quote. Quotes are only used when filling in or comparing string while when you deal with numeric data types DO NOT use quotes.

Regards

AndreJul