codeigniter php search date wise not showing all results-CodePudding

i have a codeigniter website where i have simple search box, which is like below:

<form method="post" action="<?php echo base_url()?>homecontroller/search">
  <label>Month: </label>

  <select  id="basicSelect" name="month">
    <option value="01">January</option>
    <option value="02">February</option>
    <option value="03">March</option>
    <option value="04">April</option>
    <option value="05">May</option>
    <option value="06">June</option>
    <option value="07">July</option>
    <option value="08">August</option>
    <option value="09">September</option>
    <option value="10">October</option>
    <option value="11">November</option>
    <option value="12">December</option>


  </select>

  <label>Year: </label>

  <select  id="basicSelect" name="year">
    <option value="2022">2022</option>
    <option value="2021">2021</option>
    <option value="2020">2020</option>
    <option value="2019">2019</option>
    <option value="2018">2018</option>
    <option value="2017">2017</option>
    <option value="2016">2016</option>
    <option value="2015">2015</option>
    <option value="2014">2014</option>
    <option value="2013">2013</option>
    <option value="2012">2012</option>


  </select>

  <input style="margin-top:24%" type="submit"  name="billing" value="SEARCH">

</form>

my controller looks like:

  $month=$this->input->post('month');
    $year=$this->input->post('year');

    $stotal=$year.'-'.$month;
   
    $data['search'] = $this->user->searchbilling($stotal);

and i did the following code in model to fetch results:

public function searchbilling($stotal) { 
$this->db->select('*, SUM(quantity) as quantity'); 
$this->db->from('delivered'); 
$this->db->like('date', $stotal); 
$this->db->group_by('YEAR(date), MONTH(date), customer'); 
$query = $this->db->get(); 
$result = $query->result(); 
return $result; }

however this is noy showing all the results from that particular month, can anyone please tell me whats wrong in here, thanks in advance

CodePudding user response：

You can try like this where("DATE_FORMAT(date,'%Y-%m') =", $stotal)

public function searchbilling($stotal) { 
  $this->db->select('*, SUM(quantity) as quantity'); 
  $this->db->from('delivered'); 
  $this->db->where("DATE_FORMAT(date,'%Y-%m') =", $stotal); 
  $this->db->group_by('YEAR(date), MONTH(date), customer'); 
  $query = $this->db->get(); 
  $result = $query->result(); 
  return $result; 
}

CodePudding user response：

You can do like this where("DATE_FORMAT(date,'FORMAT YOU NEED') =", $stotal)

public function searchbilling($stotal) { 
  $this->db->select('*, SUM(quantity) as quantity'); 
  $this->db->from('delivered'); 
  $this->db->where("DATE_FORMAT(date,'FORMAT YOU NEED') =", $stotal); 
  $this->db->group_by('YEAR(date), MONTH(date), customer'); 
  $query = $this->db->get(); 
  $result = $query->result(); 
  return $result; 
}