Grouping data together by date.g. fixture lists


I have a website which has a list of football fixtures - At the moment i pull them from a database and display them on the website as below

team a v team b 07/04/2010 15:00:00
team c v team d 07/04/2010 15:00:00
team e v team f 08/04/2010 17:15:00

As you can see they are ordered by date / time - What im trying to do though is display them as below:

team a v team b 15:00:00
team c v team d 15:00:00

team e v team f 17:15:00

Ive been racking my brains for a couple of days of the best way to do this - when i pull them from the database they are ordered but how do i put the header for each date. Ive thought of looping through the array and creating a new array for each day:

array( ‘07/04/2010’ => array of all matches
‘08/04/2010’ => those matches

but this seems long winded - ive thought using the group by in the mysql but then it occurred ill only get the same result. Ive thought of writing an sql query for each day and getting the results there but that seems to be a lot of processing.

Im not looking for the code to be written for me but just any explanation of how the top sports site do it - If anyone could help before i go bald id be grateful


you’re right, GROUP BY in mysql would be wrong, and running separate queries for each date would be horribly inefficient

$saveDate = '';
while ($row = mysql_fetch_assoc($result)) {
  if ($row['date'] != $saveDate) {
    // print header
    // then save the date in $saveDate
    $saveDate = $row['date'];
  // print row data

I like the nested array method personally.

Thanks everyone - This is more than i was looking for - I went with guido2004 idea as it meant that i needed to do nothing more with the query - I liked the nested array idea as well but after thinking about it that seemed to involve me having to loop through my sql results and as guido’s way offered the same result without this i went with that. It may also be worth mentioning if anyone comes across this with a similar problem that i use smarty and with guido’s way i was able to work the code into the template.

Ill be finishing it off this morning and will post a link if anyone is interested.

Thanks again everyone for responding.

Just in case anyone wanted to see it working - heres the link - still loads more to do but at least it works as i wanted now - thanks everyone