CODEWITHSUNDEEP
phpmysql
mansoondreamz
mansoondreamz

Reputation: 493

mysql zero and empty values

In a table I have value zero(0) in a particular field in some records but when I am using a query to avoid empty values in this field, it is not considering field with 0 value. How to overcome this?

SELECT
    *
FROM wr
WHERE   dir <> ''
AND UNIX_TIMESTAMP(CONCAT(date, ' ', time)) 
BETWEEN '$time_start_unix' AND '$time_end_unix

the field dir is a decimal filed with two decimal point. Values can be blank as well as 0 to 360. I do not want blank or empty values but need zero value records

Upvotes: 2

Views: 2615

Answers (3)

Ibrahim Hammed
Ibrahim Hammed

Reputation: 880

This should work

SELECT
    *
FROM wr
WHERE   dir IS NOT NULL
AND UNIX_TIMESTAMP(CONCAT(date, ' ', time)) 
BETWEEN '$time_start_unix' AND '$time_end_unix

You will have 0 in your result set.

Upvotes: 1

Sergey Onishchenko
Sergey Onishchenko

Reputation: 7881

In MySQL comparing zero to a string 0 = "any string" is always true! so we need to CAST it.

WHERE CAST(dir AS CHAR) <> ''

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

Assuming that dir is a numeric, non-nullable, column, then the only values you should ever see are numbers. Therefore the WHERE condition:

WHERE dir <> ''

makes no sense at a high level, because you are trying to compare a number against an empty string. At the database level it appears that MySQL is returning false for x <> '' whenever x is zero, and true otherwise. Hence, your 2 records with dir having a zero value are being filtered out. There are no empty records with regard to the value of dir. However, since you set a default value to 0.0, if you see a zero value, and nothing in your business logic would update with this value, then the following query might be what you want:

SELECT *
FROM wr
WHERE dir <> 0.0 AND
      UNIX_TIMESTAMP(CONCAT(date, ' ', time))
      BETWEEN '$time_start_unix' AND '$time_end_unix

I have created a Fiddle demonstrating this this here:

SQLFiddle

Upvotes: 1

Related Questions