homepage Welcome to WebmasterWorld Guest from 54.161.185.244
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Miscalculation by PHP
brokaddr



 
Msg#: 4428825 posted 11:22 pm on Mar 13, 2012 (gmt 0)

I'm not sure if the miscalculation is my fault, PHP or MySQL's.

Code:
$usa_query = tep_db_query("SELECT count(o.orders_id) as totalrecs, sum(round(ot.value,2)) as usatotal FROM ".TABLE_CANCELLED_ORDERS_TOTAL." as ot LEFT JOIN ".TABLE_CANCELLED_ORDERS." as o on(ot.orders_id=o.orders_id) where o.date_purchased LIKE '".$year."-".$month."%' && (o.orders_status='3' || o.orders_status='2099') && (o.delivery_country='United States' || o.delivery_country='Canada') && o.payment_method LIKE '%Credit%' && ot.class='ot_total'");
$usapp = tep_db_fetch_array($usa_query);
$usaamt = $usapp['usatotal']*.025;
$usatotal = $usapp['totalrecs']*0.30;
$usa = $usaamt+$usatotal;


I've checked the numbers from the spreadsheet in Excel, and the numbers generated off the site are always off by a few cents - I assume it has something to do with the decimal calculations, but I can't figure out how to circumvent it.

I've tried to add number_format($usa,2); over every mathematical result and I end up with the same (incorrect) amounts.

Any suggestions as to where my formula might be wrong?

 

brokaddr



 
Msg#: 4428825 posted 3:32 am on Mar 14, 2012 (gmt 0)

Found the following post, which seems relative:
[stackoverflow.com...]

However, changing stored dollar amounts (2.9900) for example; isn't possible due to the way the site's software is constructed.

On sum() calls where there isn't additional math involved, the amounts match up perfectly with Excel. The query with additional percentage formulas as I've posted above, have me stumped.

lostdreamer



 
Msg#: 4428825 posted 9:19 am on Mar 14, 2012 (gmt 0)

#1: I would never ROUND first, then SUM... you could lose small bits of cents there.

If the amounts are off by a few cents, I'd start looking there.

sum(round(ot.value,2)) as usatotal
becomes:
round(sum(ot.value),2) as usatotal

Let me know ;)

eelixduppy

WebmasterWorld Senior Member eelixduppy us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4428825 posted 1:02 pm on Mar 14, 2012 (gmt 0)

[php.net...]

Additionally, if you need higher precision there are other options:

BC: [php.net...]
GMP: [php.net...]

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4428825 posted 3:46 pm on Mar 14, 2012 (gmt 0)

numbers generated off the site are always off by a few cents


You're probably seeing the effects of floating point precision, which not only affects PHP, it affects just about every programming language and is machine specific. Every processor has a floating point precision placeholder*
25.00000000000001

and has different decimal places for different for different machines. That placeholder throws off the decimal calculations. It used to drive me nuts. I usually do

sprintf("%.2f",intval($raw_num * 100)/100));

... because rounding rounds down, ceiling functions round up, the above usually gets it right, and is identical in perl and php. In Javascript it's num.toFixed(2) instead of sprintf().

*The precise terms and lanaguage is probably inaccurate, but that's the gist.

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4428825 posted 5:49 pm on Mar 14, 2012 (gmt 0)

Something I have learned over the years is that is it easiest to store the smallest possible unit and then convert up.

So in this case storing pennies instead of dollars would help because you aren't dealing with decimals.

150 cents is easier to deal with than 1.5 dollars, especially when performing math operations using SQL. I am not sure how far long your application is but you should consider this an option.

brokaddr



 
Msg#: 4428825 posted 7:03 pm on Mar 14, 2012 (gmt 0)

I sincerely appreciate the replies! I've spent the better part of the past several days racking my brain and researching why this was happening.

After seeing your guys' posts & that this is a known issue, I figured out how to import these fees into my db, rather than calculate on the fly. (No longer 2.9900, but 2.99 now) - unfortunately, I still ran into the same miscalculation issue!

Then I took lostdreamer's advice and re-ordered my sum() and round() - the numbers are now exact. :)


On a related note, does number_format($var,2) have the same "float" issue, or is this strictly a way to add commas and decimals into the correct spaces?
(I'm extremely new to php currency/math calculations; until now I've only ever done typical scripting & database-related stuff - so I apologize if that's a ridiculous question)

lostdreamer



 
Msg#: 4428825 posted 10:45 am on Mar 15, 2012 (gmt 0)

AFAIK everything with decimals has a "floating point" and every language has a "floating point issue"

number_format is just to make sure that comma's and dots are in the right place so PHP doesnt think that 2,900 is 2900 instead of 2.9 (or the other way around).

lostdreamer



 
Msg#: 4428825 posted 10:48 am on Mar 15, 2012 (gmt 0)


... because rounding rounds down, ceiling functions round up...


Actually, ROUND() goes up and down (0.5 => 1, 0.49 => 0)
CEIL only goes up
FLOOR only goes down

PCInk

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4428825 posted 12:13 pm on Mar 15, 2012 (gmt 0)

To put it simply why this is happening. Computers cannot store 0.1 or 0.01 as decimal fractions accurately. Imagine you are adding three numbers together in our normal decimal system using fractions and decimals:

1) One third + One third + One third = 1
2) Replace one third with decimals, this is 0.333333 rounded to six decimal places
3) 0.333333 + 0.333333 + 0.333333 = 0.999999
4) Notice, no matter how many 3's you round to, it will never actually equal one, even those it is close!
5) The more calculations you do with these rounded numbers, the greater the error of accuracy

Your computer is doing to same thing with numbers that it cannot store such as "0.01". As suggested earlier, always store currency in pennies/cents converting to dollars/pounds/euros as you print the item to the screen.

To solve your issue here, you could try to convert the numbers to pennies before any mathematical calculation. So use int(ot.value*100) - integer to get rid rogue conversion fractions. See if this works, though the output will be in cents/pennies. Then, from the cents/pennies, you can convert to dollars/pounds after all the calculations have been done.

enigma1

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4428825 posted 4:57 pm on Mar 17, 2012 (gmt 0)

You should be using the tep_round function after your calculations and specify 2 decimals for the rounding. If you search the function in your files it is used extensively.

lexipixel

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4428825 posted 2:38 am on Mar 23, 2012 (gmt 0)

To keep the rounding errors consistent and manageable, I try replace all division with multiplication, e.g.-

Instead of:

100 / 3 = 33.333...

I'd Use:

100 * .3333 = 33.33

It's not perfect, but you can control (or at least know what to expect) from the results.

(FOOTNOTE: This is why grocery stores sell things "3 For $2.00"... instead of trying to figure out exactly how much one costs, they know you'll take the mentally lazy route and just buy three).

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved