CodeIgniter select request date difference

Question

How to implement the following query in PHP CodeIgniter?

What I am doing is choosing idfrom approvalwhere level = 5and datetime needs to be compared with the current datetime.

If the number of days exceeds 30 days and below 61 days, I need to receive id.

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

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

Not executed.

Answer 1

Thank you for helping the guys help this person :)

            $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 incorrect.

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 of the falsefunction whereis here so that CI does not treat your date calculation as a field name and therefore does not add the environment `` ''

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

Answer 3

.

function where_between($field, $min, $max){
   $CI = get_instance();
   return $CI->db->where("$field BETWEEN $min AND $max");
}

Assistant to load your saved name from / application / helpers /

$this->load->helper('yoursaved_helper');
//
$this->db->where_between('date_and_time', 30, 61);

OR you can use Directly

$this->db->where("date_and_time BETWEEN 30 AND 61");

Answer 4

There is an error in the request where the condition is, check the condition inside one quotation mark and a comma after the condition and value, for the link and in this line $this->db->where('level=>', "5");you have a character >after the equal sign, it should be like >=or simple =, as shown below

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