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

i have 3 fields in invoice table:

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

i have run problem rounding. there's invoice following values. addition adds up, when displaying invoice data , generating pdf invoice, displayed data doesn't 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. total column calculated adding subtotal , tax_amount values in php. rounding done correctly 165.16 + 24.77 = 189.93 , not 189.94.

how can handle these situations? not case.

round stored values nearest penny. when you're calculating total or subtotal using php, sum rounded values.

if you're going displaying rounded aggregates way, it's best practice calculate exact same way avoid confusion.

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

another option having rounding charge on invoice account discrepancy, though may confuse people.

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

whatever choose do, consistent!