Date sorting and formatting with MySQL

Best way to do this?

[naaathan]

I'm working with a form that lets an Admin choose a day, month, and year for an event. These events are publicly displayed on their website, ordered by the soonest upcoming date.

In order to sort in this manner, what's the most effective way to do this? Accepting form data from the user in one format, converting it using PHP to store as a different format (to sort correctly), then converting it again for display seems like a lot of work.

I want to publicly display the date as "Monday, September 24, 2007" but accept the Day, Month and Year from the Admin dropdown.

Any suggestions?

[phranque]

you should store your date in a canonical format such as date or datetime.
the database can do some of the conversion for you on input and you can specify conversion the exact format you require when you select from that column.

[ericjust]

What about using strtotime() and date()?

// storing the date

$month = $_POST['month']; // (i.e. '09')
$day = $_POST['day']; // (i.e. '24')
$year = $_POST['year']; // (i.e. '2007')

$phpdate = strtotime($year.'-'.$month.'-'.$day);

// sorting is easy now
ORDER BY `event_date` DESC

// displaying the date
$phpdate = $row['event_date'];
$display_date = date('l, F jS, Y',$phpdate);
echo $display_date; // output: Monday, September 24th, 2007

[naaathan]

Thanks for the replies guys!

This is what I did to solve this problem, based on your suggestions:

Getting the data ready for the database:

$event_month = ($_POST['event_month']);
$event_day = ($_POST['event_day']);
$event_year = ($_POST['event_year']);
$event_date = $event_year.'/'.$event_month.'/'.$event_day; // stored as date

Pulling it back out:
SELECT * FROM events ORDER BY event_date ASC

Formatting it:

print date('l, F jS, Y', strtotime($row['event_date']))';