CakePhp, how MySQL "Like" works as case insensitive search

Question

In CakePhp, how MySQL Like works as case insensitive search. I have tried with following code but problem is also coming here.

If i am searching "Motel Park" then result is fine.

But when searching "motel park" no result is found.

In database Collation is latin1_swedish_ci

$where_condition = array('Ad.completed' => 1,
                         'Ad.address LIKE '=>'%'.$_REQUEST['address'].'%',
                         'Ad.status' =>$ad_status
                        );
 $result =  $this->Ad->find('all',array('conditions'=>$where_condition));

You shouldn’t be using the `$_REQUEST` superglobal. CakePHP has `$this->request->data` for POST data, and `$this->request->query` for query string parameters. — Martin Bean, May 01 '14 at 08:33

score 2 · Answer 1 · answered May 01 '14 at 09:08

You can convert both the value to lowercase to use LIKE statement

$where_condition = array(
    'Ad.completed' => 1,
   'LOWER(Ad.address) LIKE '=>'%'.strtolower($this->request->data['address']).'%',
    'Ad.status' =>$ad_status
  );
$result =  $this->Ad->find('all',array('conditions'=>$where_condition));

score 1 · Accepted Answer · edited May 23 '17 at 11:50

try using "COLLATE utf_general_ci" collation. refer this link.

but there is an easy solution , by converting all string in to lowercase. then check.

$request_address=strtolower($_REQUEST['address']);
$where_condition = "Ad.completed =  1 AND
                    LOWER(Ad.address) LIKE '%".$request_address."%' AND
                    Ad.status ='".$ad_status."'";  

$result =  $this->Ad->find('all',array('conditions'=>$where_condition));

CakePhp, how MySQL "Like" works as case insensitive search

2 Answers2