Cell background colour by date

[davidcriniti]

Hi everyone,

 

In the script below, the background colour (I'm Australian, not a bad speller  ) is determined by the sex field.

 

Any tips on how I can change the array (or replace it with an if / else statement) so it's determined by the 'note_end_date' field and set out so that if the value in the note_end_date is sometime in the future, then the cell background is #FF99FF, and if that is not the case, then the cell background is #CCCCCC.

 

Any advice is much appreciated. Here's the relevant code:

 

 

$bgColors = array('F'=>'#FF99FF', 'M'=>'#CCCCCC'); while ($row = mysql_fetch_array($sql))

{   
// Print out the contents of each row into a table   
echo "<tr bgcolor=\"{$bgColors[$row['sex']]}\">\n";    


	echo "</td><td>";
echo $row_counter++; 
echo "</td>";
echo "<td>{$row['firstname']}</td>\n";    
echo "<td>{$row['lastname']}</td>\n";   
echo "<td>{$row['sex']}</td>\n";    
echo "<td>{$row['notes']}</td>\n";   
echo "<td>{$row['note_end_date']}</td>\n";   
echo "</tr>\n";
}
echo "</table>";

[WebStyles]

off the top of my head, maybe something like this: (untested)

<?php
while ($row = mysql_fetch_array($sql)){   
$bgColors = strtotime($row['note_end_date']) > time() ? '#FF99FF' : '#CCCCCC');
// Print out the contents of each row into a table   
echo "<tr bgcolor=\"{$bgColors[$row['sex']]}\">\n";    


	echo "</td><td>";
echo $row_counter++; 
echo "</td>";
echo "<td>{$row['firstname']}</td>\n";    
echo "<td>{$row['lastname']}</td>\n";   
echo "<td>{$row['sex']}</td>\n";    
echo "<td>{$row['notes']}</td>\n";   
echo "<td>{$row['note_end_date']}</td>\n";   
echo "</tr>\n";
}
echo "</table>";

?>

[davidcriniti]

No luck with that. I changed one of your lines to

 

echo "<tr bgcolor=\"{$bgColors[$row['note_end_date']]}\">\n"; 

 

...Was that a move in the right direction at least? Any other tips?

 

Thanks,

 

Dave

[WebStyles]

echo $row['note_end_date'] and post the result here.

[jcbones]

WebStyles missed changing this line:

echo "<tr bgcolor=\"{$bgColors[$row['sex']]}\">\n";
//should be
echo "<tr bgcolor=\"{$bgColors}\">\n";

 

If the column 'note_end_date' is a date, datetime, or timestamp column.

[davidcriniti]

Thanks webstyles and jcbones.

 

I've now got a solution thanks to both of you. I'll post the working code below for the benefit of anyone wanting to do this in future:

 

<?php
while ($row = mysql_fetch_array($sql)){   
$bgColors = strtotime($row['note_end_date']) > time() ? '#99FF99' : '#FF3333';
// Print out the contents of each row into a table   
echo "<tr bgcolor=\"{$bgColors}\">\n";
	echo "</td><td>";
echo $row_counter++; 
echo "</td>";
echo "<td>{$row['firstname']}</td>\n";    
echo "<td>{$row['lastname']}</td>\n";   
echo "<td>{$row['sex']}</td>\n";    
echo "<td>{$row['notes']}</td>\n";   
echo "<td>{$row['note_end_date']}</td>\n";   
echo "</tr>\n";


}
echo "</table>";
?>

 

Thanks again,

 

Dave

[davidcriniti]

Having played with this over the past couple of days I got to thinking about the possibility of having a third colour (for example, #CC6600) for the background of the cells in a situation where note_end_date is nearing, but has not yet arrived.

 

Kind of like traffic signals I guess:  Green when the end date is a way off, orange when the end date is approaching (say less than 7 days away) and red for once the end date has arrived.

 

Currently the code just has the two options (green and red):

 

$bgColors = strtotime($row['note_end_date']) > time() ? '#99FF99' : '#FF3333';

 

Can anyone advise how I'd tweak it to include the third option?

 

Yours gratefully,

 

Dave

 

 

[WebStyles]

calculate the remaining time with a simple substraction.

then use an IF statement to check if it's greater or smaller than 7 days (in seconds)

which would be 60*60*24*7 (seconds * minutes * hours * days)

 

so something like:

if(strtotime($row['note_end_date']) > time()){
  // date hasn't expired yet, so we check the remaining days
  $diff = time() - strtotime($row['note_end_date']);
  if($diff <= (60*60*24*7) ){
     $bgColors = '#CC6600';
  }else{
     $bgColors = '#99FF99';
  }
}else{
  // date has expired
  $bgColors = '#FF3333';
}

 

hope this helps

[davidcriniti]

Thanks Webstyles,

 

I managed to get it working after changing the code slightly to make it strtotime($row['note_end_date']) - time() instead of vice versa.

 

while ($row = mysql_fetch_array($sql)){ 

if(strtotime($row['note_end_date']) > time()){

  // date hasn't expired yet, so we check the remaining days

  $diff = strtotime($row['note_end_date']) - time();

  if($diff <=  (60*60*24*7) ){

    $bgColors = '#CC6600';

  }else{

    $bgColors = '#99FF99';

  }

}else{

  // date has expired

  $bgColors = '#FF3333';

}

 

 

Seems to be working like a charm now - ta muchly!

 

Dave