CODEWITHSUNDEEP
phpcodeigniter
Sujan Shrestha
Sujan Shrestha

Reputation: 614

Codeigniter: how to get data between today and last 15 days from database

my database table looks like below

| id | user_name | address | contact | date | |----|-----------|---------|---------|----------| | 1 | john | NY | 12345 |2015-4-20 | | 2 | Mart | NY | 54345 |2015-4-05 | | 3 | Drew | US | 67340 |2015-3-14 |

my controller function is

function orders()
{

  $data['orders'] = $this->common_model->get_data_between_15days('tbl_orders',array('status'=>'1'));
  $data['title']='Orders';
  $data['main_content']='users/orders_view.php';
  $this->load->view('admin/includes/template',$data);

}

and my model function is

   public function get_data_between_15days($table, $condition)
   { 

    $result = $this->db->get_where($table, $condition);
    if($result)
      {
        return $result->result_array();
      }
   }

now i want to get the records between today and last 15 days from database.and i tried like this 

 $result = $this->db->query('SELECT * FROM '.$table.' WHERE date BETWEEN DATE_SUB(NOW(), INTERVAL 15 DAY) AND NOW(); AND '.$condition);

but its not working. i want to get all the Records between Last 15 and 30 Days too. I would appreciate for your help. thank you.

Upvotes: 6

Views: 22848

Answers (6)

Felipe Lima
Felipe Lima

Reputation: 453

Updating answer to CI 4 at 2021

$myModel = new WhateverModel();

$result = $myModel->where("date BETWEEN DATE_SUB(NOW(), INTERVAL 15 DAY) AND NOW()")->findAll();

Upvotes: 0

Mohammed Alomair
Mohammed Alomair

Reputation: 395

I think the best way You can use dateiff to get any query between any two dates by days like this

    $result  = $this->db->query("SELECT * FROM ".$table." WHERE datediff('". $your_date ."', row_date) <= 15")->get()->result();

Upvotes: 0

Saty
Saty

Reputation: 22532

Use CodeIgniter standard of query

$this->db->select('*');
$this->db->where('date BETWEEN DATE_SUB(NOW(), INTERVAL 15 DAY) AND NOW()');
$this->db->where($conditions);
$result = $this->db->get($table);

Upvotes: 10

ranakrunal9
ranakrunal9

Reputation: 13558

You can use the following query to get last 15 days data based on your localhost time zone as may be your MYSQL database time zone is different than your localhost then you will not get correct data from database.

 $result = $this->db->query('SELECT * FROM '.$table.' WHERE date >= '.date('Y-m-d', time() - (15 * 24 * 60 * 60)).' AND date <= '.date('Y-m-d').' AND '.$condition);

Upvotes: 1

Girish
Girish

Reputation: 12127

Remove ; semicolon after NOW() function, semicolon is break query so YySql understand another query after semicolon
this query would work

 $result = $this->db->query('SELECT * FROM '.$table.' 
 WHERE `date` BETWEEN DATE_SUB(NOW(), INTERVAL 15 DAY) AND NOW() AND '.$condition);

Upvotes: 0

jogesh_pi
jogesh_pi

Reputation: 9782

This is how you can achieve: 

$qs = "";
if( is_array($condition) && count($condition) > 0 ):
    foreach( $condition as $_k => $_v ) {
        $qs .= "and $_k = {$_v} ";
    }
endif;

'SELECT * FROM '.$table.'  
    WHERE `date` BETWEEN DATE_SUB(NOW(), INTERVAL 15 DAY) AND NOW() '.$qs

Upvotes: 1

Related Questions