Clever Geek Handbook

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.

+3


source to share


4 answers


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


source


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
+1


source


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


source


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


source






More articles:


All Articles