How to handle monetary values in PHP and MySql?

Question

I've inherited a huge pile of legacy code written in PHP on top of a MySQL database. The thing I noticed is that the application uses doubles for storage and manipulation of data.

Now I came across of numerous posts mentioning how double are not suited for monetary operations because of the rounding errors. However, I have yet to come across a complete solution to how monetary values should be handled in PHP code and stored in a MySQL database.

Is there a best practice when it comes to handling money specifically in PHP?

Things I'm looking for are:

1. How should the data be stored in the database? column type? size?
2. How should the data be handling in normal addition, subtraction. multiplication or division?
3. When should I round the values? How much rounding is acceptable if any?
4. Is there a difference between handling large monetary values and low ones?

Note: A VERY simplified sample code of how I might encounter money values in everyday life (various security concerns were ignored for simplification. Of course in real life I would never use my code like this):

$a= $_POST['price_in_dollars']; //-->(ex: 25.06) will be read as a string should it be cast to double?
$b= $_POST['discount_rate'];//-->(ex: 0.35) value will always be less than 1
$valueToBeStored= $a * $b; //--> any hint here is welcomed 

$valueFromDatabase= $row['price']; //--> price column in database could be double, decimal,...etc.

$priceToPrint=$valueFromDatabase * 0.25; //again cast needed or not?

I hope you use this sample code as a means to bring out more use cases and not to take it literally of course.

Bonus Question If I'm to use an ORM such as Doctrine or PROPEL, how different will it be to use money in my code.

Answer 1

It can be quite tricky to handle numbers with PHP/MySQL. If you use decimal(10,2) and your number is longer or has higher precision it will be truncated without error (unless you set proper mode for your db server).

To handle large values or high precision values you can use library like BCMath it will allow you to make basic operation on the large numbers and keep required precision.

I'm not sure what exactly calculations you will make but you have to also keep in mind that (0.22 * 0.4576) + (0.78 * 0.4576) will not equal 0.4576 if you will not use proper precision through the process.

Maximum size of DECIMAL in MySQL is 65 so it should be more than enough for any purpose. If you use DECIMAL field type it will be returned as string regardless of the use of an ORM or just plain PDO/mysql(i).

How should the data be stored in the database? column type? size?

DECIMAL with precision you need. If you are using exchange rates then you will need at least four decimal places

How should the data be handling in normal addition, subtraction. multiplication or division?

Use BCMath to be on save side and why using float may not be a good idea

When should I round the values? How much rounding is acceptable if any?

For monetary values normal two decimal places are acceptable but you may need more if for example you are using exchange rates.

Is there a difference between handling large monetary values and low ones?

Depends on what you mean by large. There is definitely a difference between handling numbers with high precision.

Answer 2

A simple work around is to store them as integers. 99.99 stored as 9999. If this won't work (and there are many reasons why this could be a bad choice) you can use type Decimal. http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html on the mysql side. On the php side i found this https://stackoverflow.com/questions/3244094/decimal-type-in-php which might be what you are after.

Bonus question: Hard to say. The orm is going to work based on the data types chosen. I would say that you could do some stuff with abstraction to help but this specific issue isn't addressed simply by moving to an ORM.

Answer 3

I'll try to put my experience in this:

Things I'm looking for are:

How should the data be stored in the database? column type? size?

I've been using DECIMAL(10,2) for mysql without problems (8 completes and 2 decimals == 99.999.999,99 == huge amount), but this depends on the money range you'll need to cover. Huge amounts should be taken with extra care (OS max float values for example). On the decimal part I use 2 values to avoid truncate nor rounding values. Taking about money there're few cases where you need more decimals (in which case you need to make sure the user will work with all of them, otherwise is useless data)

How should the data be handling in normal addition, subtraction. multiplication or division?

Work with one currency and a exchange table (with dates). This way you ensure that you'll always have the correct amount saved. An extra: save complete values and create a view with calculation results. This will help you fixing values on the fly

When should I round the values? How much rounding is acceptable if any?

Again, depends on your system money range. Always think in KISS terms unless you need to fall in the currency exchange's mess

Is there a difference between handling large monetary values and low ones?

Depending on your OS and programming languages you always need to check you max and min values