Daniel Omara Daniel Omara - 5 months ago 23
MySQL Question

How to get number of rows using due date/ expiry date in codeigniter?

Table Name:

directives


dir_id | directive | due_date |
-----------------------------------
1 | some text | 2017-03-30 02:00:00
2 | some text | 2016-04-30 02:00:00
3 | some text | 2017-04-30 02:00:00
4 | some text | 2016-03-30 02:00:00
5 | some text | 2015-04-30 02:00:00
6 | some text | 2016-04-30 02:00:00


using three conditions.

if
there are
60 days
left to due date show
green
elseif
there are
5 days
left show
yellow
else if
there are
zero(0) days or minus days
passed due date show
red
.

Also if possible get number of rows respectively
*You can use different query for row count*
e.g rows with
60 days left = 5, data with 5 days left = 4, data with 0 days or minus = 4


'Lovely day to all'

Thank you

** Final Edit "Working Code for row count"**



//For Zero days
$this->db->where('due_date < now()');
$query0 = $this->db->count_all_results('directives');

//For five days left
$this->db->where('due_date between now() - interval 6 day and now() + interval 5 day');
$query5 = $this->db->count_all_results('directives');

//For more than 5+ days left
$this->db->where('due_date > now() + interval 6 day');
$query6 = $this->db->count_all_results('directives');


In case I did not follow the standard procedure please let me know.

Thank you

Answer

/UNTESTED/

select case
    when due_date between now() - interval 5 day and sysdate()
       then "red"
    when due_date between now() - interval 6 day and now() - interval 59 day
       then "Yellow"
when due_date > now() + interval 60 day
       then "Green"
end as age,
sum(case when due_date between now - interval 5 day and sysdate() then 1 else 0 end) red_count 
 ///// sum others to get counts
from table 
Comments