CODEWITHSUNDEEP
phpmysql
Phil
Phil

Reputation: 767

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:

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

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.

Upvotes: 1

Views: 967

Answers (3)

Simon Elliston Ball
Simon Elliston Ball

Reputation: 4455

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']);
}

Upvotes: 2

Shad
Shad

Reputation: 15451

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)

Upvotes: 2

brian_d
brian_d

Reputation: 11385

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.

Upvotes: 1

Related Questions