Page 1 of 1

[SOLVED] - UPDATE query - Help Needed

Posted: Tue Feb 06, 2007 8:36 pm
by netpants
OK I am trying to subtract 10% from the value of the uOffensiveMen Table if the value is greater than 10.

Code: Select all

$db->query("UPDATE users SET uOffensiveMen=uOffensiveMen-(uOffensiveMen*0.10) WHERE uRace=1 AND uOffensiveMen>10");
I start with the value of 20 in my table and it subtracts 2 the first time, but then when I run it again it still subtracts 2, and run again it will only subtract 2 again over and over until the value is below 10.

What am I doing wrong?

Posted: Tue Feb 06, 2007 11:06 pm
by phpice
Sounds like a data type issue. It looks like MySQL is doing integer math instead of using floats/decimals. If you intend to subtract a rounded whole number every time, then I think your formula is correct. For example, 10% of 18 = 1.8, which rounds to 2 when it is automatically cast as an integer.

However, if you want to use floating point numbers, then you should first confirm that "uOffensiveMen" in the table is set to the correct data type, something like decimal(10,2). MySQL will evaluate the expression based on the field type.

Posted: Tue Feb 06, 2007 11:22 pm
by RobertGonzalez
I agree. You should change your data type to decimal or some other floating point type data type so when the math is calculated it is not made into an integer.

Posted: Wed Feb 07, 2007 9:37 am
by netpants
Well it does work fine the first time, but if I subtract 10% from 12, it still takes two away, or is it always going to round up, and should I try starting out with a higher number and see what it does.

Posted: Wed Feb 07, 2007 9:45 am
by netpants
Ok I booted it up to 200 and did the query and it took 20 the first time, then 18 the second time, so it appears to be working all this time lol. Thanks for your help and insight.