Checking for an empty field with MySQL

Question

I've wrote a query to check for users with certain criteria, one being they have an email address.

Our site will allow a user to have or not have an email address.

$aUsers=$this->readToArray('
 SELECT `userID` 
 FROM `users` 
 WHERE `userID` 
 IN(SELECT `userID`
         FROM `users_indvSettings`
  WHERE `indvSettingID`=5 AND `optionID`='.$time.')
  AND `email`!=""
 ');

Is this the best way to check for an empty field in SQL? I've just tried "IS NOT NULL" and this still returned a users record without them having an email address.

The query above works but out of curiosity I wondered if I'm doing it the correct way.

Answer 1

Yes, what you are doing is correct. You are checking to make sure the email field is not an empty string. NULL means the data is missing. An empty string "" is a blank string with the length of 0.

You can add the null check also

AND (email != "" OR email IS NOT NULL)