1

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?

giancy9
  • 31
  • 10
  • You can't compare floats like this. You need a fixed representation, like something with `sprintf` and predictable rounding. That could be `6.29999999381238`. – tadman Nov 14 '16 at 17:20
  • 1
    floats are not precise. you should be using number(X,2), decimal or something for currency if supported. Doubles and floats are for VERY large or very small numbers that don't require precision. If in table list you're storing price as float or double, you've done it wrong. Price should be currency, or number with a specific number of decimals. For a more detailed answer http://stackoverflow.com/questions/13030368/best-data-type-to-store-money-values-in-mysql – xQbert Nov 14 '16 at 17:20
  • thanks a lot for your answers – giancy9 Nov 14 '16 at 17:24

2 Answers2

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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.

Israel
  • 1,165
  • 11
  • 11