7

I have an customer invoice table in my MySQL database with a DECIMAL(10,2) field called price.

When fetching these values in php and calculating a sum amount,

ex: in the script 

$totalAmount = 0; // initialised them to 

   while(records){              

       $amount = $inv_amount - ($pay_amount + $onamount); //float i guess. 2.22, 14.22
       $totalAmount = $totalAmount + $amount; //float i guess. 2.22, 14.22 ..etc

    }

when echo $totalAmount; it has a slight error in the final amount 0.01 however when dealing which large datasets around 20,000 this error becomes very considerable such as 200+

what is the safest way to do this when dealing with prices and such with these numbers in PHP? Or will I end up with potential rounding errors and things like that which are common when working with floating point data types?

is using 

round 
number_format

is the most suitable solution for this type of a financial application ?

laurent
  • 88,262
  • 77
  • 290
  • 428
mahen3d
  • 7,047
  • 13
  • 51
  • 103

7 Answers7

11

Indeed, floating point numbers are not precise.
Either calculate in cent (multiply by 100 and calculate in integers), or calculate in strings using BC Math.

deceze
  • 510,633
  • 85
  • 743
  • 889
4

Try this:

$totalAmount = number_format($totalAmount, 2, '.', '');
2

If you need accuracy of 2 decimal points:

  1. multiply value by 100
  2. do your operations
  3. divide by 100 and use number_format where appropriate
Ja͢ck
  • 170,779
  • 38
  • 263
  • 309
1

You could use number_format

Ex:(for two decimal points)

$number = 12345.5667;
echo $result = number_format($number, 2);

Or you could use round function in MySql:

ROUND(number,2)
Vimalnath
  • 6,373
  • 2
  • 26
  • 47
0

have you tried to

round(totalAmount, 2, PHP_ROUND_HALF_ODD);

it should see last decimal number - if it's odd - rounds down, if even - up

Stalker
  • 4,352
  • 2
  • 21
  • 19
0

Well, to begin with, why sum in a php loop when you can do that in mysql? Moreover, just use integer multiplied by 100 and then divide by 100 once you need the final result.

Andrius Naruševičius
  • 8,348
  • 7
  • 49
  • 78
0

I recommend doing calculations on SQL Level with queries or views.

Firstly, using 64 bit integers on PHP is risky because; on overflow, it switches to float and you lose precision. When it's money, the problem is more serious. You should acually calculate a column with sql and just get the value from there. Money is a long type and it will often convert to float on php and your people will lose or win some cents depending on the mood of php engine. You can't work with decimal type on php, even if you multiply by 100 and keep it as an integer, long numbers with overflow will be converted to float automatically. Calculate your values on MYSQL if you want to use decimal numbers or convert your field into int.

If your server has an 32 bit cpu, the WORD length is 32 bit so integers in php engine are 32 bit integers. This makes the likelihood of overflow higher. On an 64 bit system, you can work more comfortably.

Using a round function on a money value is ridiculous and unprofessional in this case. Don't do it.

Reading this document will help you a lot.

php documentation of integers says

Integer overflow

If PHP encounters a number beyond the bounds of the integer type, it will be interpreted as a float instead. Also, an operation which results in a number beyond the bounds of the integer type will return a float instead.

Calculation on SQL level will ensure precision for money type. Getting the money type to php will result in a float value, and you can multiply it by 100 and divide by 100 later BUT it increases the rish of overflow again because php will use an 32 bit float number for storage. If you are using 32 bit float why is the data field decimal? So it's inconsistent if you do that. an 32 bit number i already not so big, and an 32 bit float loses some of its capacity on floating point, so it's more likely to reach over capacity when you multiply by 100.

USE SQL

Uğur Gümüşhan
  • 2,455
  • 4
  • 34
  • 62
  • Moving calculations to the SQL level sometimes isn't the best idea because the SQL server's job is to query and return data. It is the job of the application server to process that data. For handling large numbers in PHP see http://stackoverflow.com/questions/211345/working-with-large-numbers-in-php – Bailey Parker Jun 20 '12 at 09:01
  • @PhpMyCoder do you have any sound arguments for that or are you being humorous. – Uğur Gümüşhan Jun 20 '12 at 09:10
  • @PhpMyCoder that guy is talking about writing his own class for a numeric2 type, I am talking about a solution like two columns added to the query, probably would take one line or two. – Uğur Gümüşhan Jun 20 '12 at 09:13
  • Using MySQL to do your computations is like entering basic math into Google search instead of using the physical calculator beside your computer. The database is intended to store, query, and return data. The application is intended to process that data. – Bailey Parker Jun 20 '12 at 09:16
  • @PhpMyCoder that's moving the data between nodes in the multi tier arthictecture, rather than doing it in one place. Why would you load all the data if you are interested in the sum of it? Please think about it. – Uğur Gümüşhan Jun 20 '12 at 09:21