PHP/MySQL: Best money operations/storing practices??

Question

Hi all!

First of all sorry for my English, it's not my native language.

So, I am planning to make an application (PHP/MySQL) which deals a lot with money, and I am thinking about how to store and operate with the money, referring to PHP float data type and MySQL decimal.

I was thinking of two options. One of them is to operate and store money in integer cents format ($dollars * 100) in order not to deal with float inprecisions and to store it in the DB as integer too. The other one is to store in DB as decimal and to use BC Math in PHP for calculations.

So I googled all the night to find out which is the best option to use and didn't find a clear answer. The only reasonable option that I've seen was the integer cents one (which I don't really like because it would imply a lot of converting from dollars to cents and viceversa before every display in the browser and before storing in the DB).

Also, people have complained about MySQL decimal (MySQL stores decimals as strings, operates them as floats etc.), but that were old posts. According to MySQL documentation, the current version handles decimals correctly, the only complaint was that it truncates the fraction of the values that exceeds the declared fraction length (e.g. if you store a value of 12.326 in a column declared decimal(9,2)), but from my investigations it rounds it instead of just truncating (12.326 becomes 12.33), which is correct in my opinion.

And, I didn't find any recommendation on storing money as decimals and make calculations using PHP BCMath, and in my opinion this is because few people know about BC and GMP math functions.

So, what would be the best option to use, considering precision, speed (BCMath calculations speed, MySQL decimal speed vs integer) and programming comfort?

I would appreciate any answer ;)
John

Answer 1

I'd definitely go for using ints and routing everything through a data object (ORM) style that then handles all conversion for you. The client code using the data object will never need to do conversion and won't care, while you won't have problems with storage as ints are handled easily by the DB. Furthermore, you can then add whatever other methods are needed for the money object (like conversions between money types, etc) quite easily.

Answer 2

I also had trouble finding information about BCMath, so I researched it and wrote my own article about it: http://www.exploringbinary.com/base-conversion-in-php-using-bcmath/ .

(I'm not taking the stance that you should use BCMath -- I'm just giving you information.)