Can't Pass NULL Date from form

[bsanderson]

I have a date field in a MySQL database which is set to accept NULLs. If I enter a record from phpMyAdmin and leave the date field blank, the record is entered properly. However, if I do the same thing from a form, I get an error that the date cannot be blank. It seems that I need to enter a NULL in the date field from the form rather that nothing. At the suggestion of someone on a newsgroup, I added the following line:

if ($date === '' or !isset($date)) $date='NULL';

This almost works, but the resultant statement contains the single quotation marks, which cause an error.

INSERT INTO jobs ( JobNumber, DateRcvd, DateEff ) VALUES ( '9999', 'NULL', 'NULL')

Does not work, but

INSERT INTO jobs ( JobNumber, DateRcvd, DateEff ) VALUES ( '9999', NULL, NULL)

does.

This is my actual query:

$query = "INSERT INTO jobs ( JobNumber, DateRcvd, DateEff, ) VALUES ( '$JobNumber','$DateRcvd','$DateEff',)";

If I remove the single quotes from the date values, the nulls are entered correctly but that only works if the field is null. If a date is entered, it doesn't work.

Ahy help will be greatly appreciated.

[yonta]

Well, maybe you could vary your query so

if (DateRcvd == "")
query = ...
elseif(DateEff == "")
query=..
else
query =..

Probably not the best approach but the only one i could think of now reading your post.

Sofia

[fenway]

The easiest solution is simply to write your query as follows:

[code]$query = "INSERT INTO jobs ( JobNumber, DateRcvd, DateEff, ) VALUES ( '$JobNumber',$DateRcvd,$DateEff)";[/code]

And have PHP add the single quotes if necessary:

[code]$DateRcvd = ($DateRcvd === '' or !isset($DateRcvd)) ? "NULL" : "'$DateRcvd'";[/code]

Do the same for $DateEff, if necessary.

Make sense?

[bsanderson]

That did it. Thanks