Date sorting and formatting with MySQL

Forum Moderators: open

Message Too Old, No Replies

Date sorting and formatting with MySQL

Best way to do this?

naaathan

4:52 pm on Sep 24, 2007 (gmt 0)

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

10:38 pm on Sep 24, 2007 (gmt 0)

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

11:02 pm on Sep 24, 2007 (gmt 0)

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

5:21 pm on Sep 25, 2007 (gmt 0)

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']))';