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.

• Jimmy Hoffa: I don't know PHP but knowing terminology is invaluable in these scenarios for google-fu, the term you're looking for is "arbitrary precision" to which google responds with php.net/manual/en/book.bc.php
• Michael Borgwardt: @Jimmy Hoffa: arbitary precision is generally not what you need, something that can accurately represent and work with decimal fractions is. Of course, you frequently find both combined, but e.g. the decimal type in C# has limited precision but is perfectly suited for monetary values.
• Jimmy Hoffa: @MichaelBorgwardt right, I forget about rational types because so many languages don't have them. Good call.
• Pieter B: Having worked a lot with currencies and legacy code, I'd say go for storing it in integers and using cents instead of dollars. Beware though, 32 integers can hold a surprisingly small amount.4) a 32 bit integer can only hold an amount of 21 million if unsigned and using cents.
• Carson63000: As an aside, your sample code that shows prices and discounts being POSTed terrifies me. Hopefully it's so simplified it doesn't reflect real usage, but at face value, it looks like you're trusting the browser to tell you what the price of an item is by POSTing back a hidden field or some such.

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.

• Florian Margaine: FWIW, Drupal Commerce uses the 9999 trick to store its prices.
• Songo: "and there are many reasons why this could be a bad choice" Would you plz share some of the problems to this practice?
• Michael Borgwardt: @Songo: see floating-point-gui.de/formats/integer
• Ominus: @MichaelBorgwardt Thank for the info, Songo sorry for the delay, hurricane took us out :)

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

• Songo: Thanks for the answer, but if I have to handle something like $valueToBeStored= $a * $b; if $a and $b are both read as decimals from the database I think they will be cast to double in PHP right? will that affect the numbers?
• Alwin Kesler: $a and $b are taken from db? so, in my example you don't need to ever store $valueToBeStored because you'll always have the source $a and $b data. So you can work programatically the value in a function or so or create a mysql view with the column result. This way, if any value has to be changed you don't have to worry about modifying multiple places (error prone)