CODEWITHSUNDEEP
phpmysqlselectenums
Pushpendra
Pushpendra

Reputation: 4382

selecting rows using the enum field in mysql

I am facing an abnormal issue with the enum field(UserStatus) of my table(userinfo). The table structure is as follows:

Field       Type        Default
------------------------------------------
Id          int(11)       (NULL)
FirstName   varchar(50)   (NULL)
LastName    varchar(50)   (NULL)
....        ...           ...
....        ...           ...   
UserStatus  enum('0','1') (NULL)

Here I have rows with the UserStatus values as NULL, 0 and 1. But When I am trying to select rows on the basis of the UserStatus field like:

SELECT 
    * 
FROM 
    userinfo 
WHERE 
    UserStatus<>'1'

Its not working and resulting in an empty resultset.

Thanks in advance.

Upvotes: 2

Views: 461

Answers (2)

agim
agim

Reputation: 1841

It would be a better structure, if you would give a default value and make the field NOT NULL but in this case you could use 

 SELECT * 
 FROM userinfo 
 WHERE ifnull(UserStatus, '0') <> '1'

Upvotes: 0

cdhowie
cdhowie

Reputation: 169103

Remember that NULL <> '1' is itself NULL and therefore your condition won't match rows whose UserStatus is NULL. Use this expression instead:

UserStatus IS NULL OR UserStatus <> '1'

Upvotes: 3

Related Questions