currency issues with number format

[omoutop]

Hello and thanks for any tips.

In a product page we offer our customers the ability to check the prices in their own currency (or any currency they want out of 10 supported). Unfortunatly in some cases there seems to be 1 cent deviation.

Stored Data:
- We have the price stored in database as decimal(8,2) (and this is Euro price)
- We store the rate between Euro and other currencies in our database as decimal(8,4).

Show price option:
- All prices printed on screen are based in number_format($price, 2) format

Problem:
in one page we see one price while on the next page we see another price - the first one +/- 1 cent
For example, in one page we see $32.44 while in the next page we see $32.43.
Take note that this issue isn't constant. It happens rarely, but still it needs fixing.

More details:
- First page: calculation of basic price in euro -> show price in selected currency -> submit euro price in next page if the product is selected
- Second page: loads basic price in euro from database -> show price in selected currency

What can I do to avoid this deviation?

[jatar_k]

if you are using the same function for the calculation each time then it can't be possible, unless it is using inexact data or possibly a bad typing whiuch can sometimes happen in php's on the fly typing

look through your functions/operations to possibly increase precision.

you could use round instead of number_format
you could use more decimal places for your values
you could verify col types and look at variable values before and after various functions to be sure it isn't retyping

just some ideas

[omoutop]

thanks for the ideas

i will monitor how the round() behaves.
the rest options have already been taken into consideration before posting here

[rocknbil]

Unfortunatly in some cases there seems to be 1 cent deviation.

I think you may be experiencing the issue with floating point precision. That one cent think absolutely kills me in Javascript, but it's inherent in all languages and is more related to the machine processing the math than any particular language. Here's my nightmare with floating point precision in Javascript [webmasterworld.com].

I am Euro-ignorant, is there a reason you need this?

We store the rate between Euro and other currencies in our database as decimal(8,4).

Because if you can round that off before it gets stored and store it as 8,2, it's likely to go away. When rounding, look for floor and ceil functions, in case round rounds down only (varies with language.)

[penders]

I am Euro-ignorant, is there a reason you need this?

We store the rate between Euro and other currencies in our database as decimal(8,4).

Because if you can round that off before it gets stored and store it as 8,2, it's likely to go away.

But this is a conversion rate, not an amount of currency. In fact I would question whether 4dp of accuracy is enough?

eg. Converting 32.44 EUR to GBP (rounding to 2dp) assuming a rate of 0.878550 (6dp)

EUR ---- RATE ---- GBP 
32.44 * 0.878550 = 28.98 
32.44 * 0.8786 = 28.99 (0.01 out) 
32.44 * 0.88  = 29.04 (0.06 out)

Depending on how many products you're selling, that could add up to quite a difference.

Although this wouldn't explain why the amount would be 0.01 out just going from one page to another, assuming you're using the same rate each time?!

Floating point precision could be the issue - the way floating point numbers are actually stored - as rocknbil suggests.

- First page: calculation of basic price in euro -> show price in selected currency -> submit euro price in next page if the product is selected
- Second page: loads basic price in euro from database -> show price in selected currency

When you "submit euro price in next page" are you submitting a rounded or full figure?
What's the difference between "calculation of basic price in euro" and "loads basic price in euro"?

[omoutop]

@rocknbil: "floating point precision".. that i didnt knew. Thanks for the tip.

@penders: the 4 digits precision isnt my choise. I was asked to do it that way by my client. If i was to choose, i would sell only in euro price, no other currency.
calculation of basic price in euro = function to do all the calculations and show the price in product page
loads basic price in euro = select stored prices from database

When product page loads, I have a function to do some calculations for the basic price (product main price + 3 more prices depending on user choises).
User sees the currency price = basic price modified by selected currency
When user clicks/selects the product, i pass the basic price as a hidden field to the nect page, i store it along with some other data and redirect user to the final page

Anyway, thank you all for for your notes. As i stated above i have changed my function from number_format() to round() and will monitor how this behaves.