Floating point precision for currency: using "round" function in MySQL query vs PHP vs Javascript

Question

I am working with dollar amounts. In MySQL database the following fields fee and rate(percentage) are DECIMAL type with 2 decimal precision.

SELECT ROUND(fee * (1- rate/100))),2 ) as profit
from products

Since query is just returning the values instead of saving them in variables, does the precision problem* still exist (that comes with PHP or JS)? If so is it best to round the floating point number in PHP or JS?

*Yes I mean precision issue that occurs when saving double, e.g., 1.5 may be saved as 1.49999999

Answer 1

Others may have alluded to this, but I wanted to let you know my system for handling money calculations in PHP.

I use integers. The thing is that I have each increment represent the highest precision I need. For most of my applications, this is hundredths of a dollar, or one cent. However, you can have it be millionths or whatever you need.

So in practice, with the precision being in hundredths, $.01 is represented as 1, $.10 is represented as 10, $1.00 is represented as 100, and so forth. This really gets rid of the rounding issue as you are going to be manipulating integers only, since the decimal part of any computation will be truncated. This is OK, though, since the integer represents the finest precision you need.

Admittedly, this takes a bit more developing to handle, but rounding should not be one of the issues that crop up.