PHP / MySQL storing and searching dates

Like many people, I am totally confused by the many date functions in PHP and MySQL. What I need is a to be able to store a date in MySQL and be able to view it on the screen in a human readable format, search on it by month, year, or combination of both using a standard web form, or sort it on months or years.

Example search would be all the records for febuary for the past 5 years.

I have a javascript calendar that inputs the month in the form as 02-12-2011.

Wh开发者_JAVA技巧at is the best format to use for this. What should the field be in MySQL.

Thanks

Please make use of the DateTime object.

Store the dates in mysql as a DATE format.

When writing the data

$date = new DateTime($_POST['date']);

or

$date = DateTime::createFromFormat('d-m-Y', $_POST['date']);

$query = sprintf("INSERT INTO `data` SET `date` = '%s'", $date->format('Y-m-d'))

When reading the data out create a DateTime object.

$date = new DateTime($row['date']);

Then you can print it in whatever format you want, e.g. You javascript's format:

echo $date->format('d-m-Y');

See

http://www.php.net/manual/en/class.datetime.php

and for date formats:

http://www.php.net/manual/en/function.date.php

As far as searches go, you can use mysql Date functions on the fields.

For all records in February for the last 5 years.

SELECT * FROM `data` WHERE MONTH(`date`) = 2 AND YEAR(`date`) >= YEAR(NOW()) - 5

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

The column type in MySQL should be date.

it's a date, so store it as a DATE column. You can either use UNIX_TIMESTAMP() in your SQL query or strtotime in PHP, to convert this back to a value that can be passed in to the php date() function, to output whatever format date you'd like.