I have a currency column in a MySQL table defined as DECIMAL(19,4). It is not a floating point number so there is no problem with loss of precision.
My question is how can I fetch it into PHP, manipulate it, and store it back into MySQL without precision loss. By default if I fetch it and manipulate it I believe it becomes a float.
My hypothesis is that I can use the following:
$price = $price * 100;
$price = (int) $price;
//do some manipulation here
$price = $price / 100;
//store back into MySQL database
Does this work?
I have also looked at storing as an integer and using BCMath, but am wondering if the above would work just as well.
This is too long for a comment.
For "normal" currencies and "normal" amounts of money, then double precision/decimal(19,4) is fine. The precision for a double is 15 digits of precision. That is a value like this 123,456,789,012,345. Even at a fraction of a US cent, that represents up to 123 billion quite accurately. Conversion between a decimal and double using rounding should be fine.
In other words, if you are working with amounts up to a million currency units and want precision down to one thousandth of the unit, then double precision should be fine. If this is not the case, you might need a special math library to handle the arithmetic. (Postgres has one built-in.)
There are situations where such a range isn't quite enough. When Turkish Lira traded at over 1,000,000 to a dollar, then the range up to 1/1000 of a unit would only be less than one million dollars -- which might not be sufficient. Or, if you are valuing financial portfolios, particularly using nominal values of trades, then the range might not be sufficient.