CODEWITHSUNDEEP
mysqlsqlselectfloating-point
giancy9
giancy9

Reputation: 41

mysql compare float numbers ignored

I am trying to compare two floating numbers. I have already seen a number of questions here on stackoverflow but I didn't find a solution yet.

$sql = "SELECT price FROM list
        WHERE price != '".(float)$price."'";

Price is "6.30" In DB I want to get all results except the one with price 6.30. But it ignores it completely.

I know it has something to do with floats. I cannot edit DB table, this is not an option unfortunately.

Is there any other way to do this by just using mysql?

Upvotes: 2

Views: 2039

Answers (2)

Israel
Israel

Reputation: 1454

This will work:

$sql = "SELECT price FROM list WHERE price NOT LIKE '".(float)$price."'";

It because my SQL treat to a FLOAT differently the INT or CHAR.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

It is very dangerous to compare floating point numbers, because values that look the same could be slightly different. One method is:

WHERE ABS(price - $price) < 0.001

The problem with this is that it cannot use an index. You can instead use:

WHERE price >= $price - 0.001 and price > $price + 0.001

Of course, 0.001 is an arbitrary measure of tolerance. You might want a smaller or larger value.

And, I should mention that decimal/numeric values are safe for comparison.

Upvotes: 3

Related Questions