Jump to content


Photo

Can't Pass NULL Date from form


  • Please log in to reply
3 replies to this topic

#1 bsanderson

bsanderson
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 03 March 2006 - 01:39 PM

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.

#2 yonta

yonta
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 03 March 2006 - 04:54 PM

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


do it, do it right, do it right now

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 March 2006 - 07:34 PM

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

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

And have PHP add the single quotes if necessary:

$DateRcvd = ($DateRcvd === '' or !isset($DateRcvd)) ? "NULL" : "'$DateRcvd'";

Do the same for $DateEff, if necessary.

Make sense?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 bsanderson

bsanderson
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 06 March 2006 - 01:51 PM

That did it. Thanks




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users