I am trying to group a list of event dates by their ending date. So if I have 15 events that end on the same date, I want all of those tr bgcolors to be red, then the next group, lets say 8 events all end on the same date, but different ending date than the first group, I want all those tr bgcolors to be black.

I want it to just keep alternating tr bgcolors (red, black, red, black)by blocks of ending dates. Please note that each block of ending dates has more than one event in it.

My current code, without the above piece in it:

$query = "select * from tbl_tourny WHERE archived != 'yes' ORDER BY sdate ASC";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
$id = $row['id'];
$name = $row['name'];
$games = $row['games'];
$location = $row['location'];
$state = $row['state'];
$cost = $row['cost'];
$sdate = $row['sdate'];
$timestamp = strtotime($sdate);
$newtime = date('m/d/Y' , $timestamp);
$edate = $row['edate'];
$timestamp3 = strtotime($edate);
$newtime3 = date('m/d/Y' , $timestamp3);
$age = $row['age'];
$age2 = $row['age2'];
$full = $row['full'];
$showsch = $row['showsch'];

echo "<tr><td><div align=center>$newtime</div></td><td><div align=center>$newtime3</div></td><td><div align=center>$location, $state</div></td><td><div align=center>$name</div></td><td><div align=center>$age</div></td><td><div align=center>$$cost</div></td><td><div align=center>$games</div></td></tr>";

}
Member Avatar for diafol

Your query is set up to sort by startdate (*I think*), therefore you won't get 'blocks' of the same end dates together. If you want to sort by end date, that more straightforward:

$r = mysql_query("SELECT * FROM table ORDER BY enddate");
if(mysql_num_rows($r)>0){
...start table...
   $i = 0;$colour='red';
   $q = while($d = mysql_fetch_array($r)){
      if($i == 0)$stickydate = $d['enddate']; 
      if($d['enddate'] != $stickydate){
          $stickydate = $d['enddate'];
          $colour=('red') ? 'black' : 'red';
      }
      echo "<tr style=\"background-color:$colour;text-align:center;\"><td>{$d['newtime']}</td><td>{$d['$newtime3']}</td><td>{$d['$location']}, {$d['$state']}</td><td>{$d['$name']}</td><td>{$d['$age']}</td><td>{$d['$cost']}</td><td>{$d['$games']}</td></tr>";
      $i = $i + 1;
   }
...finish table...
}

BTW: you don't need all those divs - so I took them out. I haven't tested this so don't know if it works, but it's not far off.

Thanks for the help, after making some minor adjustments to get everything working, this is what I came up with:

$r = mysql_query("SELECT * FROM tbl_tourny WHERE archived != 'yes' ORDER BY edate ASC");

   $i = 0;
   
   $colour = '#791517';
   
   while($d = mysql_fetch_array($r)){
   
	  if($i == 0)$stickydate = $d['edate']; 
      
	  
	  if($d['edate'] != $stickydate){
          
		  $stickydate = $d['edate'];
          
		  $colour=('#791517') ? '#999999' : '#791517';
      }
      
	  echo "<tr style=\"background-color:$colour;text-align:center;\"><td>$newtime</td><td>$newtime3</td><td>".stripslashes($d['location']).", ".$d['state']."</td><td>".stripslashes($d['name'])."</td><td>".$d['age']."</td><td>$".$d['cost']."</td><td>".$d['games']."</td><td>--</td></tr>";
      
	  $i = $i + 1;
   }

The only problem I am having is, it put the first block red, but after that block they were all black, it didnt rotate colors. Any suqqestions?

Your query is set up to sort by startdate (*I think*), therefore you won't get 'blocks' of the same end dates together. If you want to sort by end date, that more straightforward:

$r = mysql_query("SELECT * FROM table ORDER BY enddate");
if(mysql_num_rows($r)>0){
...start table...
   $i = 0;$colour='red';
   $q = while($d = mysql_fetch_array($r)){
      if($i == 0)$stickydate = $d['enddate']; 
      if($d['enddate'] != $stickydate){
          $stickydate = $d['enddate'];
          $colour=('red') ? 'black' : 'red';
      }
      echo "<tr style=\"background-color:$colour;text-align:center;\"><td>{$d['newtime']}</td><td>{$d['$newtime3']}</td><td>{$d['$location']}, {$d['$state']}</td><td>{$d['$name']}</td><td>{$d['$age']}</td><td>{$d['$cost']}</td><td>{$d['$games']}</td></tr>";
      $i = $i + 1;
   }
...finish table...
}

BTW: you don't need all those divs - so I took them out. I haven't tested this so don't know if it works, but it's not far off.

Member Avatar for diafol

change the ternery operator to:

if($colour='red'){
   $colour = 'black';
}else{
   $colour = 'red';
}

see if that works.

If not check by adding this to the end of your 'td' tags:

<td>$stickydate</td><td>$colour</td>

in the loop echo

Where would you place that? Would it replace a current code block in the script you wrote?

here you need to use the modulus operator so it would be like

if($i%2==0){
  $color=black;   
else{
 $color=red;
}
Member Avatar for diafol
$r = mysql_query("SELECT * FROM table ORDER BY enddate");
if(mysql_num_rows($r)>0){
...start table...
   $i = 0;$colour='red';
   $q = while($d = mysql_fetch_array($r)){
      if($i == 0)$stickydate = $d['enddate']; 
      if($d['enddate'] != $stickydate){
          $stickydate = $d['enddate'];
          if($colour='red'){
              $colour = 'black';
          }else{
              $colour = 'red';
          }
      }
      echo "<tr style=\"background-color:$colour;text-align:center;\"><td>{$d['newtime']}</td><td>{$d['$newtime3']}</td><td>{$d['$location']}, {$d['$state']}</td><td>{$d['$name']}</td><td>{$d['$age']}</td><td>{$d['$cost']}</td><td>{$d['$games']}</td></tr><td>$stickydate</td><td>$colour</td>";
      $i = $i + 1;
   }
...finish table...
}

That's what I meant - don't know if it'll work though.

Wr.t:

if($i%2==0){
  $color=black;   
else{
 $color=red;
}

That will just zebra-stripe the table, regardless of the 'enddate' block.

I thought that's what someone requested on an earlier post.

This is what I have now, but it still only colors the first block red, the rest of the blocks (about 7 of them) stay black.

$r = mysql_query("SELECT * FROM tbl_tourny WHERE archived != 'yes' ORDER BY edate ASC");

   $i = 0;
   
   $colour = '#791517';
   
   while($d = mysql_fetch_array($r)){
   
    $sdate = $d['sdate'];
	$timestamp = strtotime($sdate);
	$newtime = date('m/d/Y' , $timestamp);
	
	$edate = $d['edate'];
	$timestamp3 = strtotime($edate);
	$newtime3 = date('m/d/Y' , $timestamp3);
      
	  if($i == 0)$stickydate = $d['edate']; 
      
	  
	  if($d['edate'] != $stickydate){
          
		  $stickydate = $d['edate'];
          
			  if($colour='#791517'){
			  
			  $colour = '#999999';
			  
			  } else { 
			  
			  $colour = '#791517';
			  
			  }
       }
	  
      
	  echo "<tr style=\"background-color:$colour;text-align:center;\"><td>$newtime</td><td>$newtime3</td><td>".stripslashes($d['location']).", ".$d['state']."</td><td>".stripslashes($d['name'])."</td><td>".$d['age']." ".$d['age2']."</td><td>$".$d['cost']."</td><td>".$d['games']."</td><td>--</td></tr>";
      
	  $i = $i + 1;
   }
$r = mysql_query("SELECT * FROM table ORDER BY enddate");
if(mysql_num_rows($r)>0){
...start table...
   $i = 0;$colour='red';
   $q = while($d = mysql_fetch_array($r)){
      if($i == 0)$stickydate = $d['enddate']; 
      if($d['enddate'] != $stickydate){
          $stickydate = $d['enddate'];
          if($colour='red'){
              $colour = 'black';
          }else{
              $colour = 'red';
          }
      }
      echo "<tr style=\"background-color:$colour;text-align:center;\"><td>{$d['newtime']}</td><td>{$d['$newtime3']}</td><td>{$d['$location']}, {$d['$state']}</td><td>{$d['$name']}</td><td>{$d['$age']}</td><td>{$d['$cost']}</td><td>{$d['$games']}</td></tr><td>$stickydate</td><td>$colour</td>";
      $i = $i + 1;
   }
...finish table...
}

That's what I meant - don't know if it'll work though.

Wr.t:

That will just zebra-stripe the table, regardless of the 'enddate' block.

$preDate=;
$colour='red';
$r = mysql_query("SELECT * FROM table ORDER BY enddate");
if(mysql_num_rows($r)>0){
...start table...
$q = while($d = mysql_fetch_array($r)){
 if($preDate != $d['enddate']){
 $colour=toggle($colour);
 }

echo "<tr style=\"background-color:$colour;text-align:center;\"><td>{$d['newtime']}</td><td>{$d['$newtime3']}</td><td>{$d['$location']}, {$d['$state']}</td><td>{$d['$name']}</td><td>{$d['$age']}</td><td>{$d['$cost']}</td><td>{$d['$games']}</td></tr><td>$stickydate</td><td>$colour</td>";
$preDate=$d['endate'];
}
}

function toggle($co){
  if($co=='red'){
  $co='black';
  }else{
  $co="red";
  }
return $co
}

this will check each previous date and once it sees a change then it will toggle the color

That worked great, thanks very much!!!!!!!

$preDate=;
$colour='red';
$r = mysql_query("SELECT * FROM table ORDER BY enddate");
if(mysql_num_rows($r)>0){
...start table...
$q = while($d = mysql_fetch_array($r)){
 if($preDate != $d['enddate']){
 $colour=toggle($colour);
 }

echo "<tr style=\"background-color:$colour;text-align:center;\"><td>{$d['newtime']}</td><td>{$d['$newtime3']}</td><td>{$d['$location']}, {$d['$state']}</td><td>{$d['$name']}</td><td>{$d['$age']}</td><td>{$d['$cost']}</td><td>{$d['$games']}</td></tr><td>$stickydate</td><td>$colour</td>";
$preDate=$d['endate'];
}
}

function toggle($co){
  if($co=='red'){
  $co='black';
  }else{
  $co="red";
  }
return $co
}

this will check each previous date and once it sees a change then it will toggle the color

your welcome!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.