Codeigniter: getting data between two dates in mysql using php

Question

I want to get data between two dates in mysql.

from date: 01/04/2015 to date: 01/05/2015

but I cannot get the date in one variable;

as i get as below:

 $fdate=01; $fmonth=04; $tdate=01; $tmonth=05; $year=2015; 

my code in the model:

 function date_range($fdate,$fmonth,$tdate,$tmonth,$year) { $this->db->select('*'); $this->db->where('DAY(order_date) >=',$fdate); $this->db->where('MONTH(order_date) >=',$fmonth); $this->db->where('YEAR(order_date) >=',$year); $this->db->where('DAY(order_date) <=',$tdate); $this->db->where('MONTH(order_date) <=',$tmonth); $this->db->where('YEAR(order_date) <=',$year); return $this->db->get('orders'); } 

several times it returns the result correctly, and sometimes it does not return the result, but I had data in mysql between the specified two dates.

what mistake will I make on this code please help me

Answer 1

you can use

 $this->db->where("YOUR COLUMN BETWEEN DATE_FORMAT(COLUMN1,'%d/%m/%Y') AND DATE_FORMAT(COLUMN2,'%d/%m/%Y'")); 

Answer 2

This should do it:

 function date_range($fdate,$fmonth,$tdate,$tmonth,$year) { $this->db->select('*'); $this->db->where('order_date <=',date('Ym-d',mktime(0,0,0,$fmonth,$fdate,$year))) $this->db->where('order_date >=',date('Ym-d',mktime(0,0,0,$tmonth,$tdate,$year))) return $this->db->get('orders'); } 

Answer 3

You can use CodeIgniter:

 $query = $this->db->query("YOUR QUERY"); 

where your "Your QUERY" will contain mysql DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')

For example, using query binding

 sql = "SELECT * FROM some_table WHERE order_date between DATE_FORMAT(? ? ?,'%d %m %Y') AND DATE_FORMAT(? ? ?,'%d %m %Y')"; $this->db->query($sql, array($fdate, $fmonth, year, $tdate, $tmonth, year));