468,290 Members | 2,104 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,290 developers. It's quick & easy.

mysql and null values

Hello, quick question about MySQL storing NULL values...

Say I have a textbox called $_POST["text"] and a variable $var.

if(empty($_POST["text"]))
$var = NULL;
else
$var = $_POST["text"];

Disregarding filtering/formatting the data, upon inserting $var into
MySQL, I thought it *should* result in a NULL entry in the database (the
field is setup to accept NULL values). I am sure $var = NULL before
inserting into the table because is_null() returns TRUE.

The field is of type CHAR and the entry gets added simply as ''
(obviously without the quotes). If I run a select statement to find all
entries where field = NULL, I get 0, but where field = '' returns the entry.

I don't understand why it's doing this. The only way I can get it to say
NULL in the record is by actually setting $var = 'NULL', but that to the
best of my knowledge is just setting $var to the CHAR value 'NULL', not
actually NULL.

Thanks in advance for all your help.

Marcus
Jul 17 '05 #1
1 2737
lig
I beleive the main problem is that MySQL has a special syntax when
setting something to NULL. Simply assigning it will not work. I would
suggest you look here -
http://dev.mysql.com/doc/mysql/en/pr...with-null.html . Maybe in
the PHP you should set it to the string 'NULL' and then in the SQL make
sure it is not quoted.

quote from the manual:
* If you want to search for column values that are NULL, you cannot use
an
* expr = NULL test. The following statement returns no rows, because
* expr = NULL is never true for any expression:
*
* mysql> SELECT * FROM my_table WHERE phone = NULL;
*
* To look for NULL values, you must use the IS NULL test. The following

* statements show how to find the NULL phone number and the empty phone
number:
*
* mysql> SELECT * FROM my_table WHERE phone IS NULL;
* mysql> SELECT * FROM my_table WHERE phone = '';

Marcus wrote:
Hello, quick question about MySQL storing NULL values...

Say I have a textbox called $_POST["text"] and a variable $var.

if(empty($_POST["text"]))
$var = NULL;
else
$var = $_POST["text"];

Disregarding filtering/formatting the data, upon inserting $var into
MySQL, I thought it *should* result in a NULL entry in the database (the field is setup to a ccept NULL values). I am sure $var = NULL before
inserting into the table because is_null() returns TRUE.

The field is of type CHAR and the entry gets added simply as ''
(obviously without the quotes). If I run a select statement to find all entries where field = NULL, I get 0, but where field = '' returns the entry.
I don't understand why it's doing this. The only way I can get it to say NULL in the record is by actually setting $var = 'NULL', but that to the best of my knowledge is just setting $var to the CHAR value 'NULL', not actually NULL.

Thanks in advance for all your help.

Marcus


Jul 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Asfand Yar Qazi | last post: by
reply views Thread by Mike Chirico | last post: by
10 posts views Thread by Python_it | last post: by
Atli
6 posts views Thread by Atli | last post: by
reply views Thread by NPC403 | last post: by
2 posts views Thread by MrBee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.