FLOAT problems with update

[jonki]

I have a very simple question - I am using php to update my MySQL database so that when someone pays my I simply type in that quantity next to their name and click submit.
It is then sent to this code:

$result = mysql_query("UPDATE db SET moneyowed=moneyowed-$paid where user_id='$user'");

It does the Maths correctly but instead of say leaving a "4" it will put 3.999999998 or something........

I need to have a float in my database because I need to calculate to the nearest cent ... is there anyway of specifying in my database that I only want to 2 decimal places? Or maybe a better way of doing the maths so that I don't get these discrepancies.

Thanks

Jon

[nemonoman]

SET moneyowed=round(moneyowed-$paid,2)

[Roger_Ramjet]

The best way to do money is with decimal arithmetic. PHP has the bcmath functions to enable you to do this. http://uk.php.net/manual/en/ref.bc.php

Many people will say, 'Ah, but you only need 2 decimal places. Using float and round() is good enough; you don't need the higher precision' (as my ISP has just said to me when I asked them to turn the bcmath functions ON)

Rubbish. Precision ALWAYS matters when it comes to money. Believe me, I've been down this road before with other database systems and languages. You start doing some simple calculations and comparisons and you get the wrong results. That is why eg. IBM mainframes had the packed-decimal data type and MS Access has the Currency data type. MySQL has the decimal data type for this purpose as well, and you can choose the number of decimal places you want to use.

Floating point numbers are never any good for accounts, and should NEVER be used to store or manipulate money.