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
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
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 records between today and last 15 days from the 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 doesn't work. I want to receive all entries between 15 and 30 days. I would appreciate your help. thank.
Use CodeIgniter Standard Request Code
$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);
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
You can use the following query to get the latest 15 days data based on your localhost timezone, as your MYSQL database timezone is different from your localhost and you will not get the correct data from the 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);
Remove ;
semicolon after function NOW()
, semicolon is break query, so YySql understands another query after semicolon
this query will work
$result = $this->db->query('SELECT * FROM '.$table.'
WHERE `date` BETWEEN DATE_SUB(NOW(), INTERVAL 15 DAY) AND NOW() AND '.$condition);