phpmysqlcodeigniterdatetime

CodeIgniter select query date difference

How can I implement the below query in PHP CodeIgniter?

What I am doing is selecting id from approval, where level = 5 and datetime must be compared with current datetime.

If a number of days goes above 30 days and below 61 days I need to fetch ID.

SELECT ID FROM APPROVAL WHERE Level = 5 AND DateTime >= CURDATE() + INTERVAL 30 DAY AND DateTime < CURDATE() + INTERVAL 61 DAY;

I have tried,

$this->db->select('id');
$this->db->from('approval');
$this->db->where('level'=>5);
$this->db->where('date_and_time'>= CURDATE() + INTERVAL 30 DAY);
$this->db->where('date_and_time'< CURDATE() + INTERVAL 61 DAY);
$res=$this->db->get()->result_array();

It's not executing.

Solution

  • Thank you for ur help guys this one is working :) 

                $this->db->select('id');
            $this->db->from('approval');
            $this->db->where("level = '5'");
            $this->db->where("DATEDIFF(NOW(), date_and_time) BETWEEN 30 AND 60");         

            $pending=$this->db->get()->result_array();