Inserting NULL or not null into Mysql

[snowman304]

I am having a problem with using the same INSERT query to pass NULL or not null. If the user doesn't put anything in the field, I want it to be NULL, BUT.....I also want it to work if the user does input something.

Here is what I am using:

$query = "INSERT INTO log (wTemp, wSpeed, wDirection, time)

VALUES ( '$weather_temp', '$wind_speed', '$wind_direction', '$time' )";

I have a time field. But I want it to be NULL if no time is entered. If that value is NULL, the above doesn't work unless I remove the '' around $time. But on the other side, if I remove '', then that statement doesn't work if the value isn't NULL.

What is the correct way to make this statement and have it work for NULL or not null?

Thanks,
Mike

[gergoe]

Instead of directly inserting the content of a variable to the sql command, use a function which checks the variable, and if it's empty, return 'NULL', otherwise the content of the passed variable enclosed by quotes, something like this:

function nullifyValue($value) { 
return empty($value)?'NULL':"'{$value}'"; 
} 

Then change your sql command as follows:

$query = "INSERT INTO log (wTemp, wSpeed, wDirection, time) VALUES ( '$weather_temp', '$wind_speed', '$wind_direction', ". nullifyValue($time) ." )";  

[snowman304]

Thanks gergoe,

I never thought about using a function inside of a query. Thanks again.

-Mike