CODEWITHSUNDEEP
phpsqlmysqlnull
user275074
user275074

Reputation:

Checking for an empty field with MySQL

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.

Upvotes: 109

Views: 200494

Answers (8)

christine
christine

Reputation: 51

WHERE TRIM(COALESCE(MyCol, '')) = ''

Query will return rows where MyCol is null or is any length of whitespace

See TRIM COALESCE and IS NULL for more info.

Also Working with null values from the MySQL docs

Upvotes: 2

Mathieu de Lorimier
Mathieu de Lorimier

Reputation: 1015

You could use

IFNULL(email, '') > ''

Upvotes: 14

Yada
Yada

Reputation: 31225

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)

Upvotes: 44

rahulcs754
rahulcs754

Reputation: 39

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>";     

        }
    }
}

Upvotes: -3

Scott Hildebrand
Scott Hildebrand

Reputation: 53

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

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425291

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.

Upvotes: 286

pdavis
pdavis

Reputation: 3211

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
     ');

Upvotes: 2

Leslie
Leslie

Reputation: 3644

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

Upvotes: 2

Related Questions