Page 1 of 1
Detecting field containing NULL in mySQL
Posted: Sat Dec 19, 2009 2:13 pm
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
Re: Detecting field containing NULL in mySQL
Posted: Sat Dec 19, 2009 2:35 pm
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
Re: Detecting field containing NULL in mySQL
Posted: Sun Dec 20, 2009 12:02 am
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.
Re: Detecting field containing NULL in mySQL
Posted: Mon Dec 21, 2009 2:56 pm
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?
Re: Detecting field containing NULL in mySQL
Posted: Mon Dec 21, 2009 3:02 pm
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().