Date problem

[jeff5656]

I have a form with a date.

When I put it into the datatbase I use this:

 

$dob = date("Y-m-d", strtotime($_POST['dob']));

 

However, if the user leaves that blank, it is stored as 0000-00-00 instead of just remaining blank.

I tried changing from NOT NULL to NULL in the table, but that had no effect.

Other than changing the date filed to VARCHAR (not ideal obviously), how do you keep that field blank if it is truly blank?

 

[premiso]

$dob = (isset($_POST['dob']) && !empty($_POST['dob'])) ? date("Y-m-d", strtotime($_POST['dob'])) : NULL;

 

Try that out and see if it works for you. (The ? : is called that Ternary operator if you want more information on how it works).

 

[jeff5656]

Nope.  it still is storing it as 0000-00-00

[premiso]

Nope.  it still is storing it as 0000-00-00

 

Interesting, let me ask you this...why does it matter if it shows as "blank" or not?

 

I do believe the only other way to prevent this (which I do not know if it will work) is if you omit the date field from the insert query. However, you should check your table structure and make sure a default value has not been set for the datefield, as that will always put that value in when nothing else is present.

 

I will do some more digging to see if I can find anything difinitive, but I am pretty sure that is how MySQL is setup.

 

EDIT:

Well my digging yielded some interesting results:

If you are not using the NO_ZERO_DATE SQL mode, MySQL also allows you to store '0000-00-00' as a “dummy date.” This is in some cases more convenient than using NULL values.

 

Found at http://dev.mysql.com/doc/refman/5.0/en/using-date.html  might be some good reading as it does seem you can configure your MySQL to do nulls instead of the 0's.

[jeff5656]

I read that but they didn't explain what do do if you do NOT want a dummy date.

If I go into phpmyadmin and there is a patient smith with dob as "0000-00-00" and I delete that and leave it blank, when I go back to Smith the dob field is back to 0000-00-00!

 

There was even a thread from someone with the same problem here:

 

http://www.phpfreaks.com/forums/index.php?topic=141792

 

and it was never solved and that was 2007.  But what do you guys do if a user leaves a field blank?  The database does not accept blank so it stores it as 0000-00-00!

 

Also, I changed the dob field to NULL instead of NOT NULL and it had no effect.

 

[jeff5656]

Ok this is weird.  When I add a new DATE field in phpmyadmin nd I browse the records, they all have 0000-00-00 as the value!

Does anyone have any odea what is goin on here? If I add a new DATE field with NULL then ALL the records have "Null" as the value.

[rajivgonsalves]

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

 

says

 

Illegal DATETIME, DATE, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00 00:00:00' or '0000-00-00').

 

so logically speaking its zero in date format

[j0n]

Ok this is weird.  When I add a new DATE field in phpmyadmin nd I browse the records, they all have 0000-00-00 as the value!

Does anyone have any odea what is goin on here? If I add a new DATE field with NULL then ALL the records have "Null" as the value.

 

Check there isn't a DEFAULT flag on that field.  This query will set the default back to 'NULL' again (obviously, put in your table name and column name).

 

ALTER TABLE your_table MODIFY date_column DATE DEFAULT NULL;

[Trizen]

hey i am not that good at php but i had a similar problem and i just tried changing the structure of how the date was recorded and it started working.

 

$date = date ("l, F jS, Y");

[jeff5656]

I tried changing null and not null.  In both cases a blank is not allowed.  If not null, the computer adds 0000-00-00 if the form was blank.  If it's null it puts in "null".

 

So basically what I'm asking is, how do I stop the database from putting in a value (000-000-00) when the POST value was BLANK?  I am gathering from lack of responses that this is not possible?  Does that mean you guys don't have any blank DATE fields??

[jeff5656]

In other words, if there is a field called firstname and the user leaves it blank, php does not take it upon itself to insert the value "None", so why does it insert "0000-000-00" when a user leaves a date field blank??

[Trizen]

oh ok you want it to have a default value if the date is not entered so that it will not be zeros.

is that what your asking or you want it to just leave the field entirely blank

[rajivgonsalves]

actually I think "0000-00-00" is null of a sort but not to sure its similar to integer value of 0 and as the manual says illegal dates will be converted to that, so I guess null is a illegal date. and this is inserted by mysql and not by php

[Trizen]

i would either make the $dob required in validation or i would do like an elseif

to include todays date so that it isnt an illegal date and go back and change it as nessessary.

 

up to you

[DavidAM]

Just a couple of things I've noticed that may have some bearing on this:

[*]I have trouble with mysqladmin assigning a DEFAULT clause when creating a table with NOT NULL columns.  Or removing the default from a column so that there is NO default (seems mysql wants a default if the column is not null, but it should NOT)

[*]I have trouble changing a value in the mysqlbrowser to NULL.

[*]When you are adding $dob to the query string, how are you doing it?

 

I'm at work and don't have access to an environment to check things on, so this is all from memory.

 

// Example 1
$dob = (empty($_POST['dob']) ? 'NULL' : $_POST['dob']);
$sql = "UPDATE myTable SET myDOB = '" . $dob . "' WHERE myID = " . $id . "'";

// Example 2
$dob = (empty($_POST['dob']) ? NULL : date($_POST['dob']));
$sql = "UPDATE myTable SET myDOB = '" . $dob . "' WHERE myID = " . $id . "'";

// Example 3
$sql = "UPDATE myTable SET myDOB = " .  
  (empty($_POST['dob']) ? 'NULL' : "'" . date($_POST['dob']) . "'") .
  . " WHERE myID = " . $id . "'";

Ex 1-- is going to put the string 'NULL' into a date field, which is illegal and results in a "zero date". 

Ex 2-- is going to put an empty string in there, also illegal

Ex 3--should put a NULL in the field since the resulting query will not have quotes around the word NULL.