[SOLVED] simple date store not working?

[delphi123]

Hi there,

 

I have a web form which I'm trying to use to insert a date from a textbox.

 

I basically want the user to be able to write the date in a format (eg dd/mm/yyyy) into the textbox and it'll add the date to the database.

 

Here's a snippet of my code:

$release_date=date("Ymd",$_POST['release_date']);

mysql_query("
INSERT INTO ratings_software (release_date)
					VALUES('$release_date')")or die("You have an error because:<br />" . mysql_error());

 

My database is filling with funny values like: 1970-01-13 when I enter things?!

 

Help! 

[paul2463]

Although MySQL tries to interpret values in several formats, dates always must be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98').

courtesy of the <a href="http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html">MYSQL MANUAL</a>

[delphi123]

yeah you'll see I did Ymd in that order - so how would you normally do it?  Most webforms I see juts have a textbox and ask you to enter in a certain format (eg dd/mm/yyyy ).

 

Is there a way to convert the string in php?

[pocobueno1388]

As said above, you should always store date formats in the DB like yyyy-mm-dd.

 

So your code should look like this

 

<?php

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

mysql_query("
INSERT INTO ratings_software (release_date)
					VALUES('$release_date')")or die("You have an error because:<br />" . mysql_error());

?>

[paul2463]

the php date function requires

 

string date  ( string $format  [, int $timestamp  ] )

 

you are passing it a posted date (such as 29 september 2007) when it needs a timestamp instead

 

try

$tstamp = strtotime($_POST['release_date']);
mysql_query("
INSERT INTO ratings_software (release_date)
					VALUES('$tstamp)")or die("You have an error because:<br />" . mysql_error());

[pocobueno1388]

the php date function requires

 

string date  ( string $format  [, int $timestamp  ] )

 

you are passing it a posted date (such as 29 september 2007) when it needs a timestamp instead

 

try

$tstamp = strtotime($_POST['release_date']);
mysql_query("
INSERT INTO ratings_software (release_date)
					VALUES('$tstamp)")or die("You have an error because:<br />" . mysql_error());

 

The date needs to also be converted to the correct format, the code I supplied will do that.

[paul2463]

sorry poco I missed that bit out - damn - forgive me

should have read

$tstamp = strtotime($_POST['release_date']);
$release_date=date("Ymd",$tstamp);

mysql_query("
INSERT INTO ratings_software (release_date)
					VALUES('$release_date')")or die("You have an error because:<br />" . mysql_error());

 

i usually break mo code down like this, if an error is thrown I can easily isolate the problem, which I would have found in my case !

 

 

[Psycho]

This won't work

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

 

strtotime will interpret a value in the format of "xx/xx/xxxx" as "mm/dd/yyyy". However he is having his users enter the value in the format of "dd/mm/yyyy".

 

You could simply use an explode to create the date parts and then reassemble.

 

$dateParts = explode('/', $_POST['release_date']);
$date = "$dateParts[2]-$dateParts[1]-$dateParts[0]";

[pocobueno1388]

This won't work

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

 

I tested it, and it worked fine for me 0_o

 

EDIT: Nevermind, I was doing the wrong format >.>

[Psycho]

Doesn't work for me. Month and day get transposed:

 

<?php

$date = "1/3/2007"; // Day 1, Month 3, Year 2007

echo date("Y-m-d", strtotime($date));

//Output: 2007-01-03  // Year 2007, Month 1, Day 3

?>

[pocobueno1388]

Here is a solution

 

<?php 

$date = explode('/', $_POST['release_date']);
$release_date = $date[2].'-'.$date[1].'-'.$date[0];
$release_date = date("Y-m-d", strtotime($release_date));

mysql_query("INSERT INTO ratings_software (release_date)
             VALUES('$release_date')")or die("You have an error because:<br />" . mysql_error());

?>

 

[helraizer]

This may not fit your bill but if you want your own date format, like (only an example)

 

$release_date =  date("H:i jS F Y");

- which returns '20:01 10th December 2007'

 

You can have the date field in your database set to 'Text' thus you can have any format.

 

Sam

[delphi123]

fantastic thanks guys!

 

pocobueno1388's solution worked perfectly!

 

Helraizer - as I am planning to sort by dates then a date field will probably be the easiest for a simpleton like myself, but thanks for the tips!

[Psycho]

Hmm...

 

Wasn't Poco's solution the same as I posted in reply #7 but with an unneeded step?

 

<?php

$date = explode('/', $_POST['release_date']);

//This puts the date in the format YYYY-MM-DD
$release_date = $date[2].'-'.$date[1].'-'.$date[0];

//This takes the above value, converts it to a timestamps
//and then puts it back into the format YYYY-MM-DD ? ? ?
$release_date = date("Y-m-d", strtotime($release_date));


?>