[SOLVED] - UPDATE query - Help Needed

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
netpants
Forum Commoner
Posts: 39
Joined: Wed Nov 15, 2006 1:21 pm

[SOLVED] - UPDATE query - Help Needed

Post 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?
Last edited by netpants on Wed Feb 07, 2007 9:45 am, edited 1 time in total.
phpice
Forum Newbie
Posts: 4
Joined: Sun Feb 04, 2007 1:57 pm

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
netpants
Forum Commoner
Posts: 39
Joined: Wed Nov 15, 2006 1:21 pm

Post 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.
netpants
Forum Commoner
Posts: 39
Joined: Wed Nov 15, 2006 1:21 pm

Post 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.
Post Reply