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

If you want to find all records that are not NULL, and either empty or have any number of spaces, this will work:

LIKE '%\ '

Make sure that there's a space after the backslash. More info here: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

Answer 2

check this code for the problem:

$sql = "SELECT * FROM tablename WHERE condition";

$res = mysql_query($sql);

while ($row = mysql_fetch_assoc($res)) {

    foreach($row as $key => $field) {  

        echo "<br>";

        if(empty($row[$key])){

            echo $key." : empty field :"."<br>"; 

        }else{

        echo $key." =" . $field."<br>";     

        }
    }
}

Answer 3

There's a difference between an empty string (email != "") and NULL. NULL is null and an Empty string is something.

Answer 4

An empty field can be either an empty string or a NULL.

To handle both, use:

email > ''

which can benefit from the range access if you have lots of empty email record (both types) in your table.

Answer 5

You could use

IFNULL(email, '') > ''

Answer 6

This will work but there is still the possibility of a null record being returned. Though you may be setting the email address to a string of length zero when you insert the record, you may still want to handle the case of a NULL email address getting into the system somehow.

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