How to handle precision when dealing with currency in MySQL and PHP

Question

I have three fields in an invoice table:

subtotal    decimal(12,4)
tax_amount  decimal(12,4)
total       decimal(12,4)

I have run into a problem with rounding. So there's an invoice with the following values. The addition adds up, but when displaying the invoice data and generating a pdf for the invoice, the displayed data doesn't always add up.

           stored value     displayed (rounded)
                                value
subtotal    165.1610         165.16 
tax_amount  24.7742          24.77
total       189.9352         189.94

The stored values add up. The total column is calculated by adding the subtotal and tax_amount values in PHP. The rounding was done correctly but 165.16 + 24.77 = 189.93 and NOT 189.94.

How can I handle these situations? This is not always the case.

Answer 1

Round the stored values to the nearest penny. When you're calculating a total or subtotal using PHP, sum up the rounded values.

If you're going to be displaying rounded aggregates a certain way, it's probably best practice to calculate it the exact same way to avoid confusion.

You could display the aggregate items as not being rounded, and then round the final sum for display purposes. This may play more nicely in the long run if you're storing fractional pennies in the database.

Another option could be having a rounding charge on the invoice to account for the discrepancy, though that may confuse some people.

That being said, programmatically speaking, it's best to avoid fractional pennies whenever possible.

Whatever you choose to do, be consistent!