passing a null value to mysql database

Question

A user fills out a form and if they choose to not fill out a field that is not required php does this:

      if($_SESSION['numofchildren']=="")
         $_SESSION['numofchildren']=null;

But when I use the session variable in a mysql query, the result is not null, but is 0. The column is a tinyint(4) that allows NULL.

Why am I getting a 0 instead of NULL?

Solution 1

Probably because PHP doesn't convert 'null' into 'NULL'. You are probably just inserting an empty value.

INSERT INTO TABLE (`Field`) ('')

You probably have the default for the column set to '0', and that means that it will insert a 0 unless you specify a number or NULL

INSERT INTO TABLE ('Field') (NULL)

To fix this, check for Null Values before you do the query.

foreach($values as $key => $value)
{
     if($value == null)
     {
         $values[$key] = "NULL";
     }
}

I have a feeling that prepared statements will have the foresight to do this automagically. But, if you are doing inline statements, you need to add a few more things.

MySQL values must have quotes around them, but Nulls don't. Therefore, you are going to need to quote everything else using this

foreach($values as $key => $value)
{
     if($value == null)
     {
         $values[$key] = "NULL";
     }
     else
     {
         // Real Escape for Good Measure
         $values[$key] = "'" . mysql_real_escape_string($value) . "'";
     }
}

Then, when you create the statement, make sure to not put quotes around any values

$SQL = "INSERT INTO TABLE (Field) VALUES(".$values['field'].")";

turns into

$SQL = "INSERT INTO TABLE (Field) VALUES("Test Value")";

or

$SQL = "INSERT INTO TABLE (Field) VALUES(NULL)";

Solution 2

Have a look at the table definition for whichever table you're inserting into. The 'default' value for that field is probably set to zero.

The version of MySql you are using is quite important in determining precisely how MySql treats Data Type Default Values.

The above link says:

For numeric types, the default is 0, with the exception that for integer or floating-point types declared with the AUTO_INCREMENT attribute, the default is the next value in the sequence.

Solution 3

I had the same problem some minutes ago, but then I figured it out. In my case I was making the query with the NULL variables between quotes like these ", '. Let me explain myself... This is what you want to do:

INSERT INTO `tbl_name` (`col1`, `col2`) VALUES (NULL,"some_value");

So if you want to use a NULL variable it should be "NULL", like this:

$var1="NULL"; $var2="some_value";

Now, if you want to use $var2, you will type '$var2' in the query, but you shouldn't do the same for $var1:

INSERT INTO `tbl_name` (`col1`, `col2`) VALUES ($var1,'$var2');

If you put $var1 between quotes, you'll get a 0 instead NULL.

Solution 4

For me it didn't work to put NULL var in database, I used var char(2).

So I just made 2 queries. This way it will work 100%. For your example it would be:

if($_SESSION['numofchildren']=="")
{
$updatequery="
            UPDATE table 
            SET table1='$value', table2='$value2', numofchilrdrentable=(NULL)
            ";
}
else
{
$updatequery="
            UPDATE table 
            SET table1='$value', table2='$value2', numofchilrdrentable='$_SESSION[numofchildren]'
            ";

}
$updatequeryresult=mysql_query($updatequery) or die("query fout " . mysql_error() );    

edit: var char -> var char(2)

Solution 5

You all where probably right, but all I had to do is put quotes around the null.

  if($_SESSION['numofchildren']=="")
    $_SESSION['numofchildren']='NULL';

View more solutions

Comments

[karim79]

@Ian McCullough please see my edit. I think your column is set to not nullable and MySql is applying a default value. Read that link.