0

24151.40 - 31891.10 = -7739.699999999997

I grab these two numbers from a MySQL table with the type as decimal(14,2) 24151.40 31891.10 It is saved exactly as stated above and it echos exactly like that in PHP. But the minute I subtract the second value from the first value, I get a number -7739.699999999997 instead of -7,739.7. Why the extra precision? And where is it coming from?

Anderson
  • 101
  • 4
  • 12

3 Answers3

5

From an article I wrote for Authorize.Net:

One plus one equals two, right? How about .2 plus 1.4 times 10? That equals 16, right? Not if you're doing the math with PHP (or most other programming languages):

echo floor((0.2 + 1.4) * 10); // Should be 16. But it's 15!

This is due to how floating point numbers are handled internally. They are represented with a fixed number of decimal places and can result in numbers that do not add up quite like you expect. Internally our .2 plus 1.4 times 10 example computes to roughly 15.9999999998 or so. This kind of math is fine when working with numbers that do not have to be precise like percentages. But when working with money precision matters as a penny or a dollar missing here or there adds up quickly and no one likes being on the short end of any missing money.

The BC Math Solution

Fortunately PHP offers the BC Math extension which is "for arbitrary precision mathematics PHP offers the Binary Calculator which supports numbers of any size and precision, represented as strings." In other words, you can do precise math with monetary values using this extension. The BC Math extension contains functions that allow you to perform the most common operations with precision including addition, subtraction, multiplication, and division.

A Better Example

Here's the same example as above but using the bcadd() function to do the math for us. It takes three parameters. The first two are the values we wish to add and the third is the number of decimal places we wish to be precise to. Since we're working with money we'll set the precision to be two decimal palces.

echo floor(bcadd('0.2', '1.4', 2) * 10); // It's 16 like we would expect it to be.
John Conde
  • 217,595
  • 99
  • 455
  • 496
  • Is there no better way to do ecommerce math? BC looks like it will get messy. – Anderson May 04 '12 at 16:49
  • It's not messy really. Just some functions that ensure precision math. They're not complicated to use IMHO. – John Conde May 04 '12 at 16:50
  • It's not that it is complicated, just hate the fact I have to add this function every time I need to handle money. – Anderson May 04 '12 at 16:52
  • For example adding an array of money values would no longer be easy as array_sum – Anderson May 04 '12 at 16:54
  • Ideally it would be that easy. – John Conde May 04 '12 at 16:55
  • 1
    @anderson so why not write functions similar to the ones avaialble in php using the BC functionality. array_bc_sum for example. For loop through the array adding the digits together, and there you have it a new function that works like the old function but with precision. – thenetimp May 04 '12 at 17:01
  • @JohnConde how come we dont have to do the bcmul function for the 1.6 * 10 – John Smith Apr 19 '13 at 15:06
1

PHP doesn't have a decimal type like MySQL does, it uses floats; and floats are notorious for being inaccurate.

To cure this, look into number_format, e.g.:

echo number_format(24151.40 - 31891.10, 2, '.', '');

For more accurate number manipulation, you could also look at the math extensions of PHP:

http://www.php.net/manual/en/refs.math.php

Ja͢ck
  • 170,779
  • 38
  • 263
  • 309
  • wow really? is there anyway to store decimal types into the MySQL table that won't require me to use number_format every time I am using a value selected from MySQL? – Anderson May 04 '12 at 16:37
  • @Anderson You can retrieve from MySQL without having to do anything; it's only needed after you have manipulated (add / subtract) the value and need to write it back into the database. In fact, when it comes out of MySQL it's still a string :) – Ja͢ck May 04 '12 at 16:44
  • so this would occur even if i were to convert the string into a number? – Anderson May 04 '12 at 16:50
  • @Anderson Yes, it would convert from string to float in your case. But, again, that's only if you intend to manipulate the value. – Ja͢ck May 04 '12 at 17:05
1

This has to do with general float / double precision rates, which scientifically relates to 1.FRACTAL * 2^exponential power. Being that there's a prefix of 1, there's technically no such thing as zero, and the closest value you can obtain to 0 is 1.0 * 2 ^ -127 which is .000000[127 0s]00001

By rounding off your answer to a certain precision, the round factor will give you a more precise answer

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_round

Bryan
  • 6,682
  • 2
  • 17
  • 21