How do save my date in DATE record ??

habbardone

Hi - a basic question...

I have my date entered in this format:

$Db_pub_date = "03/07/2012";

and I want to update my database with

$sql = "UPDATE `mypages` SET pub_date   = '$Db_pub_date' WHERE page_id = '$Db_page_id' ";

That "pub_date" is DATE record.

At the moment when I do the above, I just get "0000-00-00" stored in
the databases table.

So what do I need to do to $Db_pub_date to get it in the correct format ?

I tried this:

$Db_pub_date = 	date_create_from_format('m/d/Y',$Db_pub_date);

but I got a fatal error. :
Catchable fatal error: Object of class DateTime could not be converted to string in /ho ...

Can anyone help me on this?

Thanks.

bradgrafelman

(Note I'm assuming MySQL is your DBMS since you didn't specify otherwise.)

habbardone;10998493 wrote:
Hi - a basic question...

I have my date entered in this format:
$Db_pub_date = "03/07/2012";

That's not a format, that's an arbitrary string of characters that could reference July 3rd, March 7th, or perhaps some other date.

habbardone;10998493 wrote:
I want to update my database with
$sql = "UPDATE `mypages` SET pub_date   = '$Db_pub_date' WHERE page_id = '$Db_page_id' ";
That "pub_date" is DATE record.

At the moment when I do the above, I just get "0000-00-00" stored in
the databases table.

Which should make perfect sense, since you didn't first convert the data to the 'YYYY-MM-DD' format. Rather than making blind/arbitrary guesses as to what you meant, MySQL instead stores a default value of '0000-00-00' or rejects the query and throws an error message (depending upon configuration).

habbardone;10998493 wrote:
So what do I need to do to $Db_pub_date to get it in the correct format ?

You need to do anything that will take the data in whatever format you claim it is in now and converts it to 'YYYY-MM-DD' as expected by the DBMS.

One of the many (infinite?) ways to do this might be to let MySQL take care of the conversion for you by using its STR_TO_DATE() function.

habbardone

Sorry I forgot to mention that my date was generated by the date() function

$Db_pub_date  = date("m/d/Y");

I just wrote it like this: $Db_pub_date = "03/07/2012"; to show the
format it is in.

You need to do anything that will take the data in whatever format you claim it is in now and converts it to 'YYYY-MM-DD' as expected by the DBMS.

YES - that is what I am asking !!!

And that is why I tried:

$Db_pub_date =  date_create_from_format('m/d/Y',$Db_pub_date);

Does anyone know why my date_create_from_format() is not working ?

I could use 3 sets of substr() to cut the date up and reassemble it.
or maybe STR_TO_DATE() in the SQL query would be more efficient.

It looked to me like the date_create_from_format() was a php way of
doing the STR_TO_DATE() SQL query.

So would this be the correct way to use STR_TO_DATE() ?

$sql = "UPDATE `mypages` SET pub_date   = STR_TO_DATE($Db_pub_date, '%m/%d/%Y') WHERE page_id = '$Db_page_id' ";

Thanks.

bradgrafelman

Two questions:

If you're generating the date yourself, why not generate the date in the correct format to start with, rather than trying to convert it later?
MySQL is perfectly capable of inserting the current date without you explicitly specifying it - that's what the CURDATE() function is for.

habbardone

1) If you're generating the date yourself, why not generate the date in the correct format to start with, rather than trying to convert it later?

Because that date is placed on a form that can be altered by the user.
It shows todays date in the format that everyone is used to.

So that is the format I want it to be shown in.

2) MySQL is perfectly capable of inserting the current date without you explicitly specifying it - that's what the CURDATE() function is for.

True, but - see above. The date is the publication date - can be changed so the posted item is published later rather than "today".

Does anyone know why my date_create_from_format() is not working ?

$Db_pub_date =  date_create_from_format('m/d/Y',$Db_pub_date);

I could use 3 sets of substr() to cut the date up and reassemble it.
or maybe STR_TO_DATE() in the SQL query would be more efficient.

It looked to me like the date_create_from_format() was a php way of
doing the STR_TO_DATE() SQL query.

So would this be the correct way to use STR_TO_DATE() ?

$sql = "UPDATE `mypages` SET pub_date   = STR_TO_DATE($Db_pub_date, '%m/%d/%Y') WHERE page_id = '$Db_page_id' ";

If anyone knows how this should be properly coded, would most appreciate the help.

Thanks.

bradgrafelman

habbardone;10998547 wrote:
Does anyone know why my date_create_from_format() is not working ?
$Db_pub_date =  date_create_from_format('m/d/Y',$Db_pub_date); 

That does work just fine... but what doesn't work is trying to treat the [man]DateTime[/man] [man]object[/man] returned by that function as if it were a [man]string[/man]. Instead, you'd probably want to call one of that object's methods that returns a string, such as DateTime::format()

habbardone;10998547 wrote:
I could use 3 sets of substr() to cut the date up and reassemble it.
or maybe STR_TO_DATE() in the SQL query would be more efficient.

I'd go with the latter.

EDIT: Actually... now that you mention this is user-supplied data, I might be more inclined to go with the former, although I'd probably use a single call to [man]explode/man instead. The advantage here is that you can then use something like PHP's [man]checkdate/man function to ensure that you were given a valid date before you try to use it in a SQL query.

habbardone;10998547 wrote:
It looked to me like the date_create_from_format() was a php way of
doing the STR_TO_DATE() SQL query.

Sort of... it allows you to parse a date in an arbitrary format and receive a [man]DateTime[/man] object in return, instantiated with the proper values to represent the given date.

habbardone;10998547 wrote:
So would this be the correct way to use STR_TO_DATE() ?
$sql = "UPDATE `mypages` SET pub_date   = STR_TO_DATE($Db_pub_date, '%m/%d/%Y') WHERE page_id = '$Db_page_id' "; 

Close, but note that you don't have any quotes around the date value (which is nothing more than a string) so you're going to end up with a query containing something like:

STR_TO_DATE(03/08/2012, '%m/%d/%Y')

which is approximately equivalent to:

STR_TO_DATE(0.00018638171, '%m/%d/%Y')