Searching by name and date between codeigniter 4

Hello everyone I’m creating report through the fetched data, I created search based on category, amount and date between. But the problem is that, If I entered category and trigger the search button I get the data, And If I set the dates between to range it aslo returning the data. But if I set both category and dates between it only see the category and ignores the where condition between date range. Help please.

//html form

   <form method="post" action="">
      <input type="text" name="search" />
      <input type="date" name="startDate" />
      <input type="date" name="endDate" />
   </form>

//model method

    public function show($data = array())
    {
        $search    = $data['search'];
        $startDate = $data['startDate'];
        $endDate   = $data['endDate'];

        $query = $this->table($this->table)->select("*", false);
                     !(empty($endDate))? $query->like('category', $search): null;
                     !(empty($startDate))? $query->where('date >=', $startDate): null;
                     !(empty($endDate))? $query->where('date <=', $endDate): null;
                     $query->where(['provId' => $this->accessId]);
                     $query->groupBy('date')
                           ->orderBy('date', 'DESC');
         return $query;
    }

Answer

Looking at the documentation – https://codeigniter.com/user_guide/database/query_builder.html#looking-for-specific-data

It appears that you would have to do the following of setting the entire WHERE array at one time. I’ve used the code above to attempt to set that up properly.

$query = $this->table($this->table)->select("*", false);

$whereArray = [];
if($endDate) {
    $whereArray['category'] = $search;
   $whereArray['date <='] = $endDate;
}
if($startDate) {
    $whereArray['date >='] = $startDate;
}

$whereArray['provId'] = $this->accessId;
$query->where($whereArray);
$query->groupBy('date')->orderBy('date', 'DESC');
return $query;

Edit: Fixed some syntax issues because I was moving a little too fast.