CodeIgniter select query date difference

Question

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.

Answer 1

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();

Answer 2

Your syntax is not correct.

Try

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

The third parameter false of the function where is here to prevent CI to consider your date calculation as a field name and in consequence not adding surroundings "`"

See CI's documentation : http://www.codeigniter.com/user_guide/database/query_builder.html?#CI_DB_query_builder::where