CODEWITHSUNDEEP
phpsqlcodeigniteractiverecord
Thomas John
Thomas John

Reputation: 1899

Getting data posted in between two dates

How can I retrieve data from the database by querying records between two dates using CodeIgniter's activerecord?

Upvotes: 48

Views: 191826

Answers (12)

Tedros Kahsu
Tedros Kahsu

Reputation: 1

$start_date = $this->input->post('startDate');
$end_date  = $this->input->post('endDate');
    
$data['total_retailer'] = $this->company_model->where(
                    array(
                        'DATE(company_created_date) >=' => $start_date,
                        'DATE(company_created_date) <=' => $end_date,
                    )
                )

Upvotes: 0

MdFarzan
MdFarzan

Reputation: 390

If you are using DATETIME datatype then you can use:

$this->db->where('order_date BETWEEN "'. date('Y-m-d H:i:s', strtotime($start_date.' 00:00:00')). '" and "'. date('Y-m-d H:i:s', strtotime($end_date.' 23:59:59')).'"');

Upvotes: 0

Vipin Choubey
Vipin Choubey

Reputation: 345

Just simply write BETWEEN '{$startDate}' AND '{$endDate}' in where condition as

->where("date BETWEEN '{$startDate}' AND '{$endDate}'")

Upvotes: 2

abubakkar tahir
abubakkar tahir

Reputation: 847

if your date filed is timestamp into database then this is the easy way to get record

$this->db->where('DATE(RecordDate) >=', date('Y-m-d',strtotime($startDate)));
$this->db->where('DATE(RecordDate) <=', date('Y-m-d',strtotime($endDate)));

Upvotes: 6

jonmary karanzi
jonmary karanzi

Reputation: 51

This worked for me:

$this->db->where('RecordDate >=', '2018-08-17 00:00:00');
$this->db->where('RecordDate <=', '2018-10-04 05:32:56');

Upvotes: 5

drosanda
drosanda

Reputation: 599

if you want to force using BETWEEN keyword on Codeigniter query helper. You can use where without escape false like this code. Works well on CI version 3.1.5. Hope its help someone.

if(!empty($tglmin) && !empty($tglmax)){
        $this->db->group_start();
        $this->db->where('DATE(create_date) BETWEEN "'.$tglmin.'" AND "'.$tglmax.'"', '',false);
        $this->db->group_end();
    }

Upvotes: 1

DRO
DRO

Reputation: 31

If you want to compare SQL dates, you can try this:

$this->db->select();
$this->db->from('table_name');
$this->db->where(' date_columnname >= date("'.$from.'")');
$this->db->where( 'date_columnname <= date("'.$to.'")');

That worked for me (PHP and MySQL).

Upvotes: 3

Madhu
Madhu

Reputation: 2803

This worked great for me

$this->db->where('sell_date BETWEEN "'. date('Y-m-d', strtotime($start_date)). '" and "'. date('Y-m-d', strtotime($end_date)).'"');

Upvotes: 6

user28864
user28864

Reputation: 3453

$query = $this->db
              ->get_where('orders',array('order_date <='=>$first_date,'order_date >='=>$second_date))
              ->result_array();

Upvotes: 1

Chakradhar
Chakradhar

Reputation: 121

Try This:

$this->db->where('sell_date BETWEEN "'. date('Y-m-d', strtotime($start_date)). '" and "'. date('Y-m-d', strtotime($end_date)).'"');

Hope this will work

Upvotes: 12

Pratik Butani
Pratik Butani

Reputation: 62411

May this helpful to you.... With Join of Three Tables

public function get_details_beetween_dates()
    {
        $from = $this->input->post('fromdate');
        $to = $this->input->post('todate');

        $this->db->select('users.first_name, users.last_name, users.email, groups.name as designation, dailyinfo.amount as Total_Fine, dailyinfo.date as Date_of_Fine, dailyinfo.desc as Description')
                    ->from('users')
                    ->where('dailyinfo.date >= ',$from)
                    ->where('dailyinfo.date <= ',$to)
                    ->join('users_groups','users.id = users_groups.user_id')
                    ->join('dailyinfo','users.id = dailyinfo.userid')
                    ->join('groups','groups.id = users_groups.group_id');

        /*
        $this->db->select('date, amount, desc')
                 ->from('dailyinfo')
                 ->where('dailyinfo.date >= ',$from)
                 ->where('dailyinfo.date <= ',$to);
        */

        $q = $this->db->get();

        $array['userDetails'] = $q->result();
        return $array;
    }

Upvotes: 1

Teej
Teej

Reputation: 12873

This looks like what you need:

$this->db->where('order_date >=', $first_date);
$this->db->where('order_date <=', $second_date);
return $this->db->get('orders');

Upvotes: 127

Related Questions