PHP Mysql blank date field value submission

Question

On my form, I have a date field that is not a required field. My problem is if users don't fill in this field and they submit the form, on mysql db, instead of leaving the field blank, it will instead save 1969-12-31 and any succeeding form that has been submitted on the same day will have ascending dates from the 1969-12-31 if this field is once again left blank. What do I change so that mysql will simply leave that field blank. Thanks for reading.

Here are the codes:

<input id="nfud" name="nfud" type="date" class="input-small"/>

Mysql submit query

$nfud = date('Y-m-d', strtotime($_POST["nfud".$n.""]));
$query="insert into number_info (phone_number, data_plan, phone_used, fud, dealID) 
values ('".$nphone_number."','".$ndata_plan."','".$nphone_used."','".$nfud."','$dealID')";
        mysql_query($query) or die(mysql_error());

is date field set to not null??

geekman
– geekman

2012-12-08 09:02:11 +00:00
Commented Dec 8, 2012 at 9:02 — geekman
– geekman, Commented Dec 8, 2012 at 9:02

koopajah · Accepted Answer · 2012-12-08 10:22:49Z

2

Just leave the field empty if you did not post a date from your form:

if(isset($_POST["nfud$n"]) {
    $nfud = date('Y-m-d', strtotime($_POST["nfud$n"]));
} else {
    $nfud = "";
}
$query="insert into number_info (phone_number, data_plan, phone_used, fud, dealID) 
values ('".$nphone_number."','".$ndata_plan."','".$nphone_used."', '".$nfud."','$dealID')";
        mysql_query($query) or die(mysql_error());

EDIT : Try this to let MySQL put a default value for your column

if(isset($_POST["nfud$n"]) {
    $nfud = date('Y-m-d', strtotime($_POST["nfud$n"]));
    $query="insert into number_info (phone_number, data_plan, phone_used, fud, dealID) 
            values ('".$nphone_number."','".$ndata_plan."','".$nphone_used."','".$nfud."','$dealID')";

} else {
    $query="insert into number_info (phone_number, data_plan, phone_used, dealID) 
            values ('".$nphone_number."','".$ndata_plan."','".$nphone_used."','$dealID')";
}

mysql_query($query) or die(mysql_error());

edited Dec 8, 2012 at 10:22

answered Dec 8, 2012 at 9:03

koopajah

26k9 gold badges87 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Renee Cribe Over a year ago

Thank you for the reply. I tried this, the only thing that change is now the date does not ascend but stuck on 1970-01-01. I even tried doing $nfud = "0000-00-00"; but it is still saving 1970-01-01. I also set default on the mysql date field as 0000-00-00

koopajah Over a year ago

Try my edit but I'm not sure it works, is you field of type DATE or DATETIME? For DATETIME you cannot store NULL values

Renee Cribe Over a year ago

Nope, it didn't work. It is actually giving me a syntax error. The sql field is DATE.

koopajah Over a year ago

Updated to insert the date only if there is a date, else let MySQL put a default value. Set DEFAULT NULL to your date column

Renee Cribe Over a year ago

Still didn't work. I've been reading around and it seems to have something to do with using strtotime

Justin McDonald · Accepted Answer · 2012-12-08 09:02:01Z

0

in mysql, give the date field a default value of 0.

answered Dec 8, 2012 at 9:02

Justin McDonald

2,16616 silver badges19 bronze badges

1 Comment

Renee Cribe Over a year ago

Thanks for the reply. I set the mysql date field to default 0000-00-00 but still when the form is submitted 1970-01-01 is saved instead

Valerie Parham-Thompson · Accepted Answer · 2012-12-08 15:05:21Z

0

This can be controlled on the mysql side with default and other settings.

Can you run the following in mysql and share the results?

SHOW CREATE TABLE number_info;

answered Dec 8, 2012 at 15:05

Valerie Parham-Thompson

1,5861 gold badge11 silver badges21 bronze badges

2 Comments

Renee Cribe Over a year ago

I ran it but I am not sure what results I should be showing. MySql is only saying CREATE TABLE `number_info` ( `numberID` int(11) ...

Valerie Parham-Thompson Over a year ago

Yes, that is what it should show. Can you show the full output?

Collectives™ on Stack Overflow

PHP Mysql blank date field value submission

3 Answers 3

5 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related