NULL fields in database

[snehula]

Hi,

I have some fields in my db tables defaulting to NULL, and I'm wondering what's the difference between setting the defualt to null or setting it to nothing (empty string). I have a tested the NULL value (let's say the $fieldvalue is the field that has the value NULL and I pulled it out of the db with mysql_fetch_assoc) with the following:

if($fieldvalue == NULL)

as well as with

if(is_null($fieldvalue))

and

if($fieldvalue == "")

and finally

if(empty($fieldvalue))

and it's always the third or fourth if that executes. So the NULL field behaves like an empty string which wasn't my intention and I don't get it.. Is there any other way of testing for null that i'm not aware of?

[penders]

NULL is not the same as an empty string (in your database field). If you assign an empty string to your database field (which defaults to NULL) it will contain an empty string, not NULL.

If your database field contains NULL, then all 4 of your tests should evaluate to TRUE. NULL evaluates to an empty string in a string context, so ($fieldvalue == "") is TRUE. However ($fieldvalue === "") is FALSE when $fieldvalue is NULL (since an empty string is not identical to NULL).

If your database field contains an empty string, then all but the is_null() tests above should evaluate to TRUE. As mentioned above, NULL evaluates to an empty string, so your first test passes.

Can you post your code, as you appear to be getting different results to this?

[rocknbil]

You're also adding a layer of complication by using PHP's interpretation of null after your query. Whenever you can, use the mysql null/not null operators. This resolves searches in selects before it get interpreted by your programming layer.

select * from table where field is not null;
or
select * from table where field is null;

not

select * from table where field <> null;
or
select * from table where field = null;

To test for NULL, you cannot use the arithmetic comparison operators such as =, <, or <>.

Working with null values [dev.mysql.com]

Problems with null [dev.mysql.com]