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?

Answer 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)";

Answer 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.