CODEWITHSUNDEEP
mysqlsqldatabasefloating-point
user3301042
user3301042

Reputation: 352

using float-type value in SQL commands

$query2 = mysqli_query($con, "SELECT * FROM third WHERE id2=$id2 AND divid=3.0");

this is my SQL command and it works well. but if i wnt to use divid=3.1 (some float in here) instad of 3.0 it mysqli_num_rows returns 0. And i am perfectly sure i have many divid=3.1 column in my database.

`$query2 = mysqli_query($con, "SELECT * FROM third WHERE id2=20 AND divid=3.1");` //this givs me mysqli_num_rows == 0;

Is there something different using float-type in SQL commands?

Upvotes: 0

Views: 448

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26333

The difference with a FLOAT is that it's an approximate value. I just created a table with values 3 and 3.1, and when I tried querying for WHERE divid = 3.1 I didn't get any rows back either. I'm assuming that's because 3.1 ends up as something like 3.1000000000000001 or 3.0999999999999999.

Your options here are:

  • Change your table so the divid column is a DECIMAL type.

  • Cast divid to a DECIMAL when you use it in a WHERE clause:

    SELECT * FROM third WHERE id2=20 AND CAST(divid AS DECIMAL(10,1))=3.1

  • Round divid when querying:

    SELECT * FROM third WHERE id2=20 AND ROUND(divid, 1) = 3.1

I recommend the first option if it's not too late in the project to change a column type.

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562260

FLOAT and DOUBLE PRECISION data types have a rounding behavior by design. You can't expect them to behave like exact numeric values. See http://dev.mysql.com/doc/refman/5.6/en/problems-with-float.html

You should use DECIMAL (or its synonym NUMERIC) instead.

I tweeted about this last year: https://twitter.com/billkarwin/status/347561901460447232

If I had a dime for every time I've seen someone use FLOAT to store currency,
I'd have $999.997634. #ieee754jokes

Upvotes: 0

Related Questions