7

I want to retrieve data between two dates in mysql.

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

but i cant get date these in single variable;

how i am get as below mentioned:

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

my code in 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');
        }

some times it return results properly, and some times it not return result, but i had an data in mysql between mentioned two dates.

what mistake i make on this code please help me

Improve this question
1

4 Answers 4

Reset to default
6

you can use

$this->db->where("YOUR COLUMN BETWEEN DATE_FORMAT(COLUMN1,'%d/%m/%Y') AND DATE_FORMAT(COLUMN2,'%d/%m/%Y'"));
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4

This should do it:

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

1 Comment

In where condition it cant get given date if taken default date as 1970-01-01
4

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));
Improve this answer

1 Comment

Charlesliam ! i cant understand properly pls give another example
1 
public function get_between_dates($date_from, $date_to)
{
    $this->db->where('order_date >=', $date_from);
    $this->db->where('order_date <=', $date_to);
    return $this->db->get()->result();
}

This is how you can get data between two dates. Name your form inputs as date_from and date_to respectively.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.