BN83 BN83 - 1 month ago 8
MySQL Question

Applying different class to different dated results

I have a table with the following data:

______________________
| Name | Date |
----------------------
| Bob | 2016-09-16 |
| Ben | 2016-10-03 |
| Sam | 2016-10-03 |


Using the following:

SELECT
case
when datediff( CURDATE(), `the_date`) = 3 then '3 Days'
when datediff( CURDATE(), `the_date`) between 4 and 6 then '4-6 Days'
when datediff( CURDATE(), `the_date`) > 6 then '7 or more days'
end as days,
sum( case
when datediff( CURDATE(), `the_date`) <= 3 then 1
when datediff( CURDATE(), `the_date`) between 4 and 6 then 1
when datediff( CURDATE(), `the_date`) > 6 then 1
else 0
end ) as tot
FROM my_table
GROUP BY
case
when datediff( CURDATE(), `the_date`) <= 3 then '3 Days'
when datediff( CURDATE(), `the_date`) between 4 and 6 then '4-6 Days'
when datediff( CURDATE(),`the_date`) > 6 then '7 or more days'
end ;


and then using

echo '<p>'.$row['tot'].'</p>';


I'm getting the following results sorting my rows in to whether rows are less than 3 days old, 4 to 6 days old, or over 7 days old.

1
2


Ideally I'd like to apply a class to the p tags such as class="urgent" for the ones that are older than 7 days, orange for 4 to 6 days and then green for up to 3 days. I'd then like to wrap the number with unique text such as "You urgently need to reply to result application"

Answer

I'd test the data result for the value you're looking for, and conditionally assign a class name depending on the value.

if ($row['days'] == '7 or more days') {
    $css_class = 'urgent';
} else {
    $css_class = 'normal';
}

printf("<p class='%s'>%s</p>", $css_class, $row['tot']);

I would not recommend hardcoding your css class name in your query. Don't mix your database queries with your presentation layer, that will just create confusion and inappropriate coupling in your code.

Comments