CODEWITHSUNDEEP
phpmysql
andy
andy

Reputation: 459

Separating Results Within a Number of Different Date Ranges

I need to have a table that splits up dates into:

1. If the date is within the next 30 days - Red
2. If the date is over 30 days but under 60 - Orange
3. If the date is over 60 days - Green

I cannot seem to get this right. Here is my select statement that returns the results:

$result = mysqli_query($con,"SELECT a.ID, a.TITLE, b.VALUE_ID, 
b.UF_CRM_1410772281, b.UF_CRM_1410772330 FROM b_crm_company a 
INNER JOIN b_uts_crm_company b ON a.ID = b.VALUE_ID 
WHERE UF_CRM_1410772281 = 1 GROUP BY a.TITLE ORDER BY UF_CRM_1410772330 ASC");

Here is the while loop that displays the results in a table. You will see that I have tried to seperate the dates between the results specified above (please note that the table begins before the select statement):

<table width="475">
<tr><th width="275"><b>Company</b></th><th width="200"><b>Renewal Date</b></th></tr>


while($row = mysqli_fetch_array($result))
{
$companyName = $row['TITLE'];
$supportRenewal = date('d/m/Y',strtotime($row['UF_CRM_1410772330']));
$id = $row['ID'];


if(strtotime($row['UF_CRM_1410772330']) < strtotime('+30 day')) {

print "<tr><td><strong style='color:#ff0000;'>$companyName</strong></td><td>
<strong style='color:#ff0000;'>$supportRenewal</span></td></tr>";

}

if ((strtotime($rows['UF_CRM_1410772330']) > strtotime('+30 day')) 
and (strtotime($rows['UF_CRM_1410772330']) < strtotime('+60 day'))) {

print "<tr><td><strong style='color:#ff8400;'>$companyName</strong></td>
<td><strong style='color:#ff8400;'>$supportRenewal</span></td></tr>";

}

if(strtotime($row['UF_CRM_1410772330']) > strtotime('+90 day')) {

print "<tr><td><strong style='color:#ff0000;'>$companyName</strong></td><td>
<strong style='color:#ff0000;'>$supportRenewal</span></td></tr>";

}

}

?>

</table>

This doesn't seem to work when I thought it would, could somebody point out where I am going wrong

Upvotes: 0

Views: 39

Answers (2)

dadarya
dadarya

Reputation: 313

you should do like this

<?php 
$array = array('2014-11-15','2014-12-08','2015-01-10','2015-7-05');
foreach($array as $date) {
    $now = strtotime(date('Y-m-d'));
    $newDate = strtotime($date);
    if($newDate > $now) {
        $dateDiff = ($newDate - $now)/(24*60*60);
        echo $dateDiff; 
        if($dateDiff > 1 && $dateDiff <= 30) {
            echo "Red =======<br>";
        } else if($dateDiff > 30 && $dateDiff < 60) {
            echo "Orange =======<br>";
        } else {
            echo "Green =======<br>";
        }
    }
} 
?>

implement this in your code.

<table width="475">
<tr><th width="275"><b>Company</b></th><th width="200"><b>Renewal Date</b></th></tr>


while($row = mysqli_fetch_array($result))
{
$companyName = $row['TITLE'];
$id = $row['ID'];
$now = strtotime(date('Y-m-d'));
$supportRenewal = strtotime($row['UF_CRM_1410772330']);
$dateDiff = ($supportRenewal - $now)/(24*60*60);
if($dateDiff <= 30) {

print "<tr><td><strong style='color:#ff0000;'>$companyName</strong></td><td>
<strong style='color:#ff0000;'>date('Y-m-d',$supportRenewal)</span></td></tr>";

}

if($dateDiff > 30 && $dateDiff < 60) {

print "<tr><td><strong style='color:#ff8400;'>$companyName</strong></td>
<td><strong style='color:#ff8400;'>date('Y-m-d',$supportRenewal)</span></td></tr>";

}

if($dateDiff > 60) {

print "<tr><td><strong style='color:#ff0000;'>$companyName</strong></td><td>
<strong style='color:#ff0000;'>date('Y-m-d',$supportRenewal)</span></td></tr>";

}

}

?>

</table>

Upvotes: 1

Peter
Peter

Reputation: 9123

I would try using PHP's DateTime class. $now = new DateTime();

$future = new DateTime($row['UF_CRM_1410772330']);

$interval = $now->diff($future);
echo $interval->format('%a');

// Now you can simply check the differences by using if statements

Upvotes: 0

Related Questions