Selecting float number from FLOAT() column with PHP and mysqli

Question

Good day. I have a problem with selecting float number from MySQL database using bind variables of mysqli class.

The type of price column is FLOAT(9,2) and it's value 1.01

Simplified piece of code:

$stmt = $dbh->prepare('SELECT price FROM goods WHERE id=5');
$stmt->execute();
$stmt->bind_result(&$price);
$stmt->fetch();
echo $price;
// 1.0099999904633

As I understood mysqli automatically casts it to double. But why?

When I change type of column in table to DOUBLE(9,2) everything goes fine and the $price value is exactly 1.01.

Is there any way to obtain 1.01 value in $price variable having FLOAT(9,2) as column type (without rounding functions)?

Thank you.

It's always advisable to use DECIMAL rather than FLOAT when working with monetary values, becaue this helps prevent those little rounding errors that occur with floats in any language - http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems or even to store the value as integer cents rather than decimal dollars — Mark Baker, Mar 18 '11 at 16:59
1.01 cannot be represented as a floating point number exactly. As you see, the nearest you can come is 1.0099999.... If you're storing money values in the database, don't use floats. Use a DECIMAL type, or an int and convert your dollar/cents values to just cents. — Marc B, Mar 18 '11 at 16:59

score 0 · Answer 1 · answered Oct 22 '14 at 19:37

As noted above:

1.01 cannot be represented as a floating point number exactly. As you see, the nearest you can come is 1.0099999.... If you're storing money values in the database, don't use floats. Use a DECIMAL type, or an int and convert your dollar/cents values to just cents

Selecting float number from FLOAT() column with PHP and mysqli

1 Answers1