asked on

Insert Null Date using PHP and SprintF

I am updating a form and have an issue whereby i can't insert a NULL value into a datetime field. The datetime field allows NULL and is NULL by default.

I have tried inserting a NULL directly into the table and that works fine.

I believe that the issue is being caused because I am sending 'NULL' as opposed to NULL.. but i'm not entirely sure how i can do it using the following approach :

<?php

	$enquirysubmitted=NULL;
	$insert_sql = sprintf("INSERT INTO enquiry (enquirysubmitted) VALUES ('%s');", mysql_real_escape_string($enquirysubmitted));
	mysql_query($insert_sql)
	
?>

Open in new window

The reason why i need to be able to insert null is because the date may or may not be needed. For example, someone could enter a date and decide later that it no longer applies.

ASKER CERTIFIED SOLUTION

gr8gonzo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

I've never used or seen a DATETIME column that was null. Why not make it like this?

NOT NULL DEFAULT 0000-00-00 00:00:00

In unrelated news, this.

maddisoncr

ASKER

thank you to both of you

i will have a look at the solution now

as for leaving the date as 0000-00....

when you use that approach, i presume you just test for a 0 date when displaying the form and hide it ?

Ray Paseur

Yes, I always know that a zero date means there is no information. It makes this easy to sort.

maddisoncr

ASKER

absolutely, i will use that approach

i will also beging replacing mysql :-/

SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

maddisoncr

ASKER

Thanks for your help guys

The sprintf information was exactly what i asked for.. thank you

i have decided though to replace all mysql with PDO.. thankfully, it's done and now working !

Ray Paseur

Thanks for the points and thanks for using EE, ~Ray