Best way to display a list of events by month

Question

I have a mySQL table of events and want to display them in a list using PHP. Pretty simple, except I only want to display the month name once, for example:

May 2010
12th, 7 pm. Event 1 - event one description
15th, 5:30 pm. Event 2 - event two description

June 2010
9th, 7 pm. Event 1 - event one description
11th, 5:30 pm. Event 2 - event two description

I'm very new at SQL and PHP. I can think of one way to do it. It would involve selecting all records grouped by date, then looping through all records, comparing the month/year value each time and the first time the month/year doesn't match, print a new header row to my HTML table. I can't believe it's the best or most efficient way to go. Any ideas for a newbie? I'm sure my way would work, but it's inelegant at best, and processor-intensive at worst.

Answer 1

Here's what I'd do (pretty much what you've outlined):

1. Grab all of them from MySQL. Have MySQL sort them for you (sorting dates in a select statement in MySQL is trivial).
2. In your PHP script, loop over your dates and each time through, pull the month from the date with a regex or a PHP time/date function. If the month is different from the last month (have a variable outside your loop named something like $currentMonth), print it as a heading, print the date, then set the $currentMonth variable.

Answer 2

This would be my quick and dirty approach.

Let's say that you keep event time in UNIX timestamp format in your database. (If not, simple strtotime($datetime) will give you that format).

When you select list of events from database (and most likely order them by date/time, ascending), loop through the list.

$events = array();

foreach ($rows as $row)
{
    $month_year = strtotime(date("Y-m-01", $row['unix_timestamp']));
    $events[$month_year][] = $row;
}

Then, when you want to print out the list:

foreach ($events as $month_year => $month_events)
{
    echo date("F Y", $month_year);
    foreach ($month_events as $event)
    {
        printf("%s. %s", date("jS, g:i a", $event['unix_timestamp']), $event['description']);
    }
}

You could add some more logic in there to use "jS g a" date format if minutes == 0.

Hope it helps.