MYSQL PHP Order By Date and separate results into groups per each date

Question

I have tried to search for a solution to my problem, but I'm not really sure what I am searching for, so I haven't had much luck.

I have a simple MySQL database with one table called "activities". In that table I have fields for "start_date", "activity_description", and "activity_location".

I am trying to do an MySQL query using PHP and display the results in ascending date order, but have all the activities that fall on the same date separated by a heading.

For example I am trying to achieve the following.

2012-05-03

• Take dog for walk
• Park
  
  
• Go out for dinner
• Little Italian Restaurant

2012-05-04

• Get Car Serviced
• Johns Auto and Mechanical

2012-05-05

• Do Grocery Shopping
• Fresh Supermarket
  
  
• Go See Movie
• MegaPlex Cinemas
  
  
• Meet Up With Sam
• Hole In The Wall Bar

So far I have worked out that the MQSQL query needs to be something like this:

$result = mysql_query("SELECT * FROM activities ORDER BY start_date ASC")

And then to display the results I need to do this:

while($row = mysql_fetch_array($result))
{
echo 
  '<strong>' .  
  $row['start_date'] . 
  '</strong>' .
  '<ul><li>' . 
  $row['activity_description'] .
  '</li><li>' .     
  $row['activity_location'] . 
  '</li></ul>'; 
 }              

Which gives the results like so, repeating the date for each result:

2012-05-03

• Take dog for walk
• Park

2012-05-03

• Go out for dinner
• Little Italian Restaurant

2012-05-04

• Get Car Serviced
• Johns Auto and Mechanical

2012-05-05

• Do Grocery Shopping
• Fresh Supermarket

2012-05-05

• Go See Movie
• MegaPlex Cinemas

2012-05-05

• Meet Up With Sam
• Hole In The Wall Bar

Could anyone give me some tips on how to only echo the 'start_date' once for a particular date, and then echo it again when the date is different from the previous date?

Any tips, no matter how cryptic would be greatly appreciated. Thanks.

Answer 1

Just track the date.

You define a date with something that would not appear like a date in the past or just false.

Running through the lis you only print your heading when the date has changed and save the new date. Just like this:

$currentDate = false;
while($row = mysql_fetch_array($result))
{
if ($row['start_date'] != $currentDate){
 echo
  '<strong>' .  
  $row['start_date'] . 
  '</strong>' ;
  $currentDate = $row['start_date'];
}
 echo 
  '<ul><li>' . 
  $row['activity_description'] .
  '</li><li>' .     
  $row['activity_location'] . 
  '</li></ul>'; 
 }  

Regards, STEFAN