How to handle monetary values in PHP and MySql?
- by Songo
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:
How should the data be stored in the database? column type? size?
How should the data be handling in normal addition, subtraction. multiplication or division?
When should I round the values? How much rounding is acceptable if any?
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:
$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.
