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 :


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

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.
Who is Participating?
gr8gonzoConnect With a Mentor ConsultantCommented:
With sprintf, you're defining a template first, and your template has quotes around your value, so the value will ALWAYS have quotes around it:

...VALUES ('%s');

If you want to be able to insert a NULL, you need to change your template so it doesn't have quotes:

...VALUES (%s);

and then you need to update your sprintf so that you add quotes when the value isn't null:

$insert_sql = sprintf("INSERT INTO enquiry (enquirysubmitted) VALUES (%s);", ($enquirysubmitted === null ? "NULL" : "'".mysql_real_escape_string($enquirysubmitted)."'"));
Ray PaseurCommented:
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.
maddisoncrAuthor Commented:
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 ?
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Ray PaseurCommented:
Yes, I always know that a zero date means there is no information.  It makes this easy to sort.
maddisoncrAuthor Commented:
absolutely, i will use that approach

i will also beging replacing mysql :-/
Ray PaseurConnect With a Mentor Commented:
If you choose MySQLi as the MySQL replacement and you use the object-oriented notation you will find the replacement goes smoothly.  You can connect to the MySQL extension and the MySQLi extension at the same time and convert the queries one at a time from the obsolete extension to the MySQLi extension.  If you choose the procedural MySQLi it may be more work since the function calls now put the arguments in a different order than MySQL.  If you choose PDO you have more work to do.  Potentially a lot more work because all of the query strings are different.  It's quite an exercise!
maddisoncrAuthor Commented:
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 PaseurCommented:
Thanks for the points and thanks for using EE, ~Ray
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.