UPDATE SET with minus

[Bradley99]

Hi,

 

I'm trying to update a table when a form is submitted, so that the column I'm updating is updated by minus one of the values in the form.

Here's my update code:

mysql_query("UPDATE users SET bonusp = 'bonusp-$bonus' WHERE id='$user->id'")
or die (mysql_error());

 

$bonus has a value of either 0 or 1.

 

When i submit with the code i have, it just sets the bonusp in users table to 0.

 

Thanks

[Bradley99]

Sorted, I keep wasting posts because 2 minutes later i figure the answers, goddam!

 

I used

$newbonus=$user->bonusp-$bonus;

 

Then in query used

SET bonusp = '$newbonus'

 

Thanks

[Jessica]

It's great that you're able to figure it out on your own I've had plenty like that.

[Barand]

For future reference, don't put the expression in quotes

 

SET bonusp = bonusp-$bonus

 

NOT

 

SET bonusp = 'bonusp-$bonus'

[Psycho]

EDIT: Barand beat me to it, but I'll submit this nonetheless since I included some add'l info.

 

Also, you *could* have done the subtraction in the query. The problem was that you were creating the value as a string by putting quotes around the mathematical expression:

UPDATE users SET bonusp = 'bonusp-$bonus' WHERE id='$user->id'

 

Therefore the value inside the quote marks would be something like '5-2Normally you can put a number inside quotes for input into a numeric field, but it's not really proper. It is only because the MySQL engine can interpret something like '5' as a number. But, if you do any mathematical expression in quotes the MySQL engine cannot resolve that string value into a numeric value. This is no different than how PHP would have handled that:

 

$var1 = '5';
$var2 = '5 - 2'; //This will not be evaluated
$var3 = 5 - 2; //This will be evaluated

echo $var1; // 5
echo $var2; // 5 - 2
echo $var3 // 3

 

So, you could have just changed the query to

[color=#0000BB]mysql_query[/color][color=#007700]([/color][color=#DD0000]"UPDATE users SET bonusp = bonusp-[/color][color=#0000BB]$bonus[/color][color=#DD0000] WHERE id='[/color][color=#0000BB]$user[/color][color=#007700]->[/color][color=#0000BB]id[/color][color=#DD0000]'"[/color][color=#007700])[/color]

[Bradley99]

Thanks Barand & Psycho!