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- gt 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

User · Answer

An empty field can be either an empty string or a NULL   To handle both  use   email  gt       which can benefit from the range access if you have lots of empty email record  both types  in your table

User · Answer

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

User · Answer

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

User · Answer

There s a difference between an empty string  email        and NULL   NULL is null and an Empty string is something

User · Answer

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   gt   field               echo   lt br gt             if empty  row  key                  echo  key     empty field      lt br gt               else           echo  key         field   lt br gt

User · Answer

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- gt 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

User · Answer

You could use  IFNULL email       gt

[php] Checking for an empty field with MySQL

Examples related to php

Examples related to sql

Examples related to mysql

Examples related to null