only the first two values is considering from the date while fetching the result

Question

Here i want to get the result based on the dates that i would like to give but returning back the entire results the databse looks like this

the databse looks like this

customer_account_id     customer_id     date           code     
    11                    55         01-01-2017     ALI HAJI        
    12                    55         02-01-2017     ALI HAJI                
    13                    55         03-01-2017     ALI HAJI                
    125                   65         01-02-2017     SHARFU              
    126                   55         02-02-2017     ALI HAJI        
    127                   55         03-02-2017     ALI HAJI            
    128                   55         31-01-2017     ALI HAJI

controller looks like this

 public function report()
{
    $data['result']= $this->Account_model->get_report($_POST['from_date'],$to1['to_date'])->result();
    $this->load->view('report_payment_details',$data);

}

my model looks like this

public function get_report($from1,$to1)
{

$from=date('d-m-Y',strtotime($from1));
var_dump($from);
$to=date('d-m-Y',strtotime($to1));
var_dump($to);  
$this->db->order_by('customer_account_id','desc');
$this->db->where('date >=', $from);
$this->db->where('date <=', $to);       
return $this->db->get('customer_accounts');

}

Here if i choose the dates between 01-01-2017 and 03-01-2017 then also the entire results will be displayed.The problem is only the first two values is considering from the date for example if i consider 01-01-2017 here only 01 is considering and remaining not.please help me to solve this

Asmin Jismun · Answer 1 · 2017-04-13T08:03:29.513

2

If you want to stick with the VARCHAR maybe you can try using STR_TO_DATE

$from=date('d-m-Y',strtotime($from1));
var_dump($from);
$to=date('d-m-Y',strtotime($to1));
var_dump($to);  
$this->db->order_by('customer_account_id','desc');
$this->db->where("STR_TO_DATE(`date`,'%d-%m-%Y') >=", "STR_TO_DATE('".$from."','%d-%m-%Y')");
$this->db->where("STR_TO_DATE(`date`,'%d-%m-%Y') <=", "STR_TO_DATE('".$to."','%d-%m-%Y')"); 
return $this->db->get('customer_accounts');

edited Apr 13 '17 at 08:03

answered Apr 12 '17 at 09:02

Asmin Jismun

52
3

the date is stored as varchar in database.is this will be a problem of getting result – user_777 Apr 13 '17 at 05:08
yes, this will caused problem when need to filter the date. Please use DATETIME instead – Asmin Jismun Apr 13 '17 at 05:23
i want the result from varchar itself can we convert it and use – user_777 Apr 13 '17 at 05:26
its not converting VARCHAR to STR_TO_DATE – user_777 Apr 13 '17 at 06:24
still its not getting – user_777 Apr 13 '17 at 07:32
i have updated the answer,i hope it will work. you also can refer to this http://stackoverflow.com/questions/12776075/searching-data-between-dates-stored-in-varchar-in-mysql – Asmin Jismun Apr 13 '17 at 08:09

only the first two values is considering from the date while fetching the result

1 Answers1