Comparing and Adding Dates

[perrij3]

I am new to PHP and the only work I have done so far with PHP & MySQL is to create a program to keep track of maintenance slips at work so please forgive me if my coding isn't the best.

 

Recently the GM wanted a field added for a target date for a project to be done. I have that field added, but though it would make life much easier if I could color code the output. What I mean by that is, say a project is passed due, I want the font for the project to be red. If it is within 2 weeks of the project target date, I want it to be green. If the project doesn't have a target date or isn't close to it, then I want the font to be black.  Not all projects have target dates.

 

Here is what I have tried so far. I am just not sure if the best way to go about this is trying to add days to the current date to get to the 2 weeks out using PHP or if I should do the date addition using MySQL and maybe a join statement.  Any help or suggestions would be greatly appreciated.

 

Thanks

 

$query = "SELECT * FROM `workorder` WHERE `Completed`='0' ORDER BY `Location` ASC, `Date_Filed` ASC";
$result = mysql_query($query)
	or die (mysql_error());


while($row = mysql_fetch_array($result)) // Loop thru recordset and get values
{
	$todays_date = date("Y-m-d");
	$add_date = 14; //Number of days I want to add

	$today = strtotime($todays_date);  //Getting Today's Date
	$compare_date = strtotime($row["Date_Completed"]);  //Getting date to compare from database
	$future_date = strtotime("$today + $add_date days");  //Setting date to 14 days into the future

   if ($compare_date == '')  //Setting text to black if there is no target date
		{ $bg ="table_text_bg1"; }
		elseif              //Setting text to black if the date is more then 2 weeks out
		($compare_date <= (strtotime("$today + $add_date days"))) 
		{ $bg ="table_text_bg1"; }
		elseif           //Setting text to green if the date is 2 weeks or less away
	($compare_date <= (strtotime("$today + $add_date days"))) 
		{ $bg = "table_text_bg2"; } 
	else  //Setting text to red if the target date is past due
		{ $bg = "table_text_bg3"; } 	

        //Display output

 echo "<tr class=\""; echo $bg; echo "\">";
 echo "<td width=\"70\" align=\"center\">"; echo $row["Location"]; echo "</td>";
 echo "<td width=\"50\">"; echo $row["WorkOrder_ID"]; echo "</td>";
 echo "<td width=\"80\">"; echo $row["By"]; echo "</td>";
	 echo "<td width=\"40\">"; echo $row["Date_Filed"]; echo "</td>";
	 echo "<td width=\"260\">"; echo $row["Comments"]; echo "</td>";
 echo "<td width=\"260\">"; echo $row["Solution"]; echo "</td>";
 echo "<td width=\"40\">"; echo $row["Target_Date"]; echo "</td></tr>";
 }

[gizmola]

This is the 2nd time today I've posted this link, but I think you'll find it very relevant:

 

http://www.gizmola.com/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html