display a fixture list grouped by date

[ceresnook]

Hello there

I would appreciate some help with the following problem.

I have a database table with fields such as

hometeam,awayteam,date etc. Values could be

team1,team2,20-01-2011
team3,team4,20-01-2011
team5,team6,20-01-2011
team5,team2,27-01-2011
team1,team4,27-01-2011
team3,team6,27-01-2011

based on the data above I want to display them in a html table but grouped by date like this:

20-01-2011
team1 v team2
team3 v team4
team5 v team6
27-01-2011
team5 v team2
team1 v team4
team3 v team6

I could quite easily have one big list but how can I group them nicely by date and only have the date displaying once per group?

Thanks

[penders]

Since you will be stepping through all your records anyway in order to generate the HTML, you could simply "...ORDER BY date" your query and only display the date when it changes.

eg.

$prevDate = null; 
while (/* loop through query */) { 
 // If the date in the current record is different to the previous date then display it 
 if ($date != $prevDate) { 
  // Display date 
 } 
 // Display data... 
 : 
 $prevDate = $date; 
}

Although I would be grateful if anyone could enlighten us with a more elegant SQL solution... GROUP BY perhaps?!