CodeIgniter 3 Query ESCAPE '!' Ignoring the WHERE statement?

Question

I am building a site search with select boxes to get certain categories & regions. If selected am I including it in the query, but for some reason, using LIKE is it ignoring my WHERE section of the query!?!? Why is that or what am I doing wrong?

When I echo the query built by Codeigniter I get the fololowing: (Note the ESCAPE '!')

Query echo:

      SELECT SQL_CALC_FOUND_ROWS null as rows, ads.id AS id, location, provLabel, text, 
  adcat.id AS catid, ads.subcatid AS subcatid, ads.province R_rand, r_option, addate, 
  adcat.name AS catname, adsubcat.name AS subname, f_value, adtitle, ads.area, regionLabel, 
  adlink 
  FROM `ads` 
  JOIN `search_town` ON `search_town`.`townId`=`ads`.`townId` 
  JOIN `search_region` ON `search_region`.`regionId`=`ads`.`area` 
  JOIN `search_prov` ON `search_prov`.`provId`=`ads`.`province` 
  JOIN `adcat` ON `adcat`.`id`=`ads`.`catid` 
  JOIN `adsubcat` ON `adsubcat`.`id`=`ads`.`subcatid` 
  LEFT JOIN `adfields` ON `adfields`.`ad_id`=`ads`.`id` 
  WHERE `ads`.`catid` != 8 AND `ads`.`adactive` = 1 AND `scam` =0 AND `ads`.`province` = '1' 
  AND `ads`.`catid` = '3' AND `text` LIKE '%Nissan%' ESCAPE '!' 
  OR `f_value` LIKE '%Nissan%' ESCAPE '!' 
  GROUP BY `ads`.`id` 
  ORDER BY `addate` DESC 
  LIMIT 10

Here is the actual query in the controller:

        public function get_search($fsearch, $fcategory, $fprovince, $farea, $limit, $start)
    { 
      if($fcategory >=1){
      $incl_cat=" AND ads.catid='$fcategory'"; 
      }else{
      $incl_cat='';
      }
      if($fprovince>=1){
      $incl_prov=" AND ads.province='$fprovince'";
      }else{
      $incl_prov='';
      }
      if($farea >= 1){
      $incl_area=" AND ads.area='$farea'";
      }else{
      $incl_area='';
      }                            

       $this->db->select('SQL_CALC_FOUND_ROWS null as rows, ads.id AS id, location, provLabel, text, adcat.id AS catid, ads.subcatid AS subcatid,ads.province  
      R_rand, r_option, addate, adcat.name AS catname, adsubcat.name AS subname, f_value, adtitle, ads.area, regionLabel,
      adlink', FALSE);
       $this->db->from('ads');
       $this->db->join('search_town', 'search_town.townId=ads.townId');
       $this->db->join('search_region', 'search_region.regionId=ads.area');
       $this->db->join('search_prov', 'search_prov.provId=ads.province');
       $this->db->join('adcat', 'adcat.id=ads.catid');
       $this->db->join('adsubcat', 'adsubcat.id=ads.subcatid');
       $this->db->join('adfields', 'adfields.ad_id=ads.id', 'left');
       $where = "ads.catid!=8 AND ads.adactive=1 AND scam=0 $incl_prov $incl_cat $incl_area";
       $this->db->where($where);
       $this->db->like('text', $fsearch);
       $this->db->or_like('f_value', $fsearch);
       $this->db->group_by("ads.id");
       $this->db->order_by('addate', 'DESC');
       $this->db->limit($limit, $start);
       $query = $this->db->get();
       $return = $query->result_array();
       echo $this->db->last_query();
       $total_results=$this->db->query('SELECT FOUND_ROWS() count;')->row()->count;
       $this->session->set_userdata('tot_search', $total_results);
       return $return;

    }

Answer 1

Your WHERE condition is

a AND b AND c AND d AND ... AND x OR y

If y is true the whole WHERE condition is true.

Perhaps you mean:

a AND b AND c AND d AND ... AND (x OR y)

The ESCAPE character defaults to \, but it looks like codeigniter has changed this to ! for you (presumably because \ is already an escape in PHP, so sometimes you need multiples and this can be confusing).

Currently this is irrelevant to your query. This would only be used if you need to match a % or an _ with !% or !_ (default \% or \_).