Detecting field containing NULL in mySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
gmckay
Forum Newbie
Posts: 11
Joined: Mon Apr 28, 2008 5:13 pm
Location: Melbourne, Australia

Detecting field containing NULL in mySQL

Post by gmckay »

I have a table with a field defined as "`offer_limit_remaining` INT( 5 ) NULL".
I have rows where mySQL shows that the field contains NULL, however mysql_fetch_assoc() returns an array with the field containing "string: "0".

Am I doing something wrong? How can I retrieve the field and detect that it contains NULL?

php 5.2.9
mySQL Server version: 5.1.33-community
MySQL client version: 5.0.51a
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Detecting field containing NULL in mySQL

Post by Eran »

If you want NULL to be the default value, you need to declare it as such:

Code: Select all

`offer_limit_remaining` INT( 5 ) NULL DEFAULT NULL
gmckay
Forum Newbie
Posts: 11
Joined: Mon Apr 28, 2008 5:13 pm
Location: Melbourne, Australia

Re: Detecting field containing NULL in mySQL

Post by gmckay »

When I browse the table using phpMyAdmin is shows the value as NULL.

When I run SQL "WHERE `offer_limit_remaining` is NULL" mySQL returns all the rows.

So I think the field is NULL despite not defaulting to NULL.
gmckay
Forum Newbie
Posts: 11
Joined: Mon Apr 28, 2008 5:13 pm
Location: Melbourne, Australia

Re: Detecting field containing NULL in mySQL

Post by gmckay »

I STILL have rows where mySQL shows that the field contains NULL, however mysql_fetch_assoc() returns an array with the field containing "string: "0".

How can I retrieve the field and detect that it contains NULL?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Detecting field containing NULL in mySQL

Post by AbraCadaver »

MySQL NULL is not equal to PHP null. The MySQL functions will return an empty value for null values depending upon the type of the field (text = '', int =0, etc...). IS NULL will return fields with NULL. To check the field in PHP you'd use if empty().
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply