How do I loop through results and display day of the week once at every change in day using php and mysql?

with my current query and loop:

$sched = mysql_query("SELECT * 
FROM  `shows` 
ORDER BY  `shows`.`show_time` ASC")
or die(mysql_error());  


echo "<ul>";

while($row = mysql_fetch_array($sched)){
echo "<li><a href=\"#$row[id]\">";
echo $row['title'];
echo "</li>";
}
echo "</ul>";

This works great for displaying my results like this:

• Name of show 1
• Name of show 2
• Name of show 3

However, I want to add an item to the list at the beginning of every change in day so it would display as follows:

• Monday
开发者_StackOverflow社区
• Name of show 1
• Name of show 2
• Tuesday
• Name of show 3
• Wednesday
• Name of show 4

I can't quite wrap my brain around the loop needed to do this. It might be helpful to know that the field 'show_time' is a datetime type, so it has the information for both time and day of week.

Thanks.

Simple tweak:

echo "<ul>";
$curDay='';
while($row = mysql_fetch_array($sched)){
   $d=date('l',strtotime($row['show_time']));
   if($d!=$curDay){
     echo '<li>'.$d.'</li>';
   }
   $curDay=$d;
   echo '<li><a href="#',$row['id'],'">',$row['title'],"</li>";
}
echo "</ul>";

Initialize $curDay, and then each time through the loop, check to see if the particular day is different than the last time through the loop (or different from the initial value)

The best way to do this is to keep a flag in your loop, and compare to the previous value.

Eg.

$previousDay = '';
while($row = mysql_fetch_assoc()) {
  if ($previousDay != date('l', $row['show_time'])) {
     echo '<h2>' . $date('l', $row['show_time']) . '</h2>';
  }
  ...
  $previousDay = date('l', $row['show_time']);
}

Adjust your query to sort by show_time first.

"SELECT * FROM  `shows` ORDER BY `show_time`, `shows` ASC"

Then keep track of the current day as Shad suggests, parsing show_time to determine the day.