How do I insert a NULL into mySQL using PHP?

[busboy]

I have a simple form that my users can fill out. None of the fields are required. When they click the submit button the data goes into mySQL database. This part works fine. However, if the user leaves some of the fields blank, then that is exactly what gets put into the database, a blank. I want to be able to retain the NULL value if there is no data being submitted for a given field.

How can I structure this using PHP, nothing I've tried has worked.

Thanks.

[St.]

show for us, what did you try doing?

[busboy]

if ($address =="") {$address = "NULL";}

and

if ($address =="NULL") {$address = "NULL";}

[St.]

and what are you doing with $address? show question?

and for what are you need concretly NULL?

[Chris97b]

Well, I'm also not sure why exactly the value must be null, but something you need to understand about mySQL is that "blank" as you called it IS null.

first off, to use NULL, make sure you are doing it without quotes:
NULL: emtpy, nothingness etc.
"NULL": a 4 letter string, containing the letters N, U, L and L

incedentally, "" is synonymous to NULL, so your statement if($address=="") {$address=NULL} is effectively saying, "If $address is NULL, set $address to NULL"

here's a quick test prog. I bashed out:

<?php
 mysql_connect('localhost','*****','*****');
 $NullTest=NULL;
 $NullTest2="NULL";
 mysql_query("use test");
 mysql_query("insert into testing (test1) values ('$NullTest')");
 mysql_query("insert into testing (test1) values ('$NullTest2')");
?>

If you run this code, it produces the Table as follows:

+-------+
| test1 |
+-------+
| |
| NULL |
+-------+
(You will have to imagine that misplaced | on the first result line is the right border, as the forum will not allow me to use the [pre] tag

here's where is gets strange. See that blank in the first row? That is NULL

Where is says "NULL" in the second? that's a string, containing the word NULL (NOT the value NULL)

hope this helps

[St.]

i see. ok.

if you'll use NULL, there is a problem.
You should write NULL without quotes

mysql_query("insert into testing (test1) values ( NULL )");

by the way, you can set NULL by default in field properties ( in sql table ).
and if you have empty variable, just skip it.