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

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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