CakePHP find a query using% like%

Question

I have the following search query for my CakePHP application:

$this->paginate = array( 'limit'=>5, 'order'=>'Note.datetime DESC', 'conditions' => array( 'Note.status'=>1, 'OR' => array( 'Note.title LIKE' => '%'. $q . '%', 'Note.content LIKE' => '%'. $q . '%' ) ) ); 

Which takes a parameter named $q to make both a request for both the header and the content.

So, for example, if I have the following:

Title: Lorem ipsum Content: Lorem ipsum dolare

Search for 'lorem'

This will be good. But if I search for 'lorem dolare' , he will not find it.

How to do it?

Note. I do not want to use any plugins, etc.

EDIT: If I use FULLTEXT, will this work?

 $this->paginate = array( 'limit'=>5, 'order'=>'Note.datetime DESC', 'conditions' => array( 'Note.status'=>1, 'OR' => array( 'MATCH(Note.title) AGAINST('.$q.' IN BOOLEAN MODE)', 'MATCH(Note.content) AGAINST('.$q.' IN BOOLEAN MODE)' ) ) ); 

EDIT 2:

Getting this error while trying above:

  Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dolor IN BOOLEAN MODE)) OR (MATCH(`Note`.`content`) AGAINST(lorem dolor IN BOOLE' at line 1 SQL Query: SELECT `Note`.`id`, `Note`.`title`, `Note`.`excerpt`, `Note`.`content`, `Note`.`datetime`, `Note`.`user_id`, `Note`.`slug`, `Note`.`status`, `Note`.`topic_id`, `User`.`id`, `User`.`email`, `User`.`firstname`, `User`.`lastname`, `User`.`password`, `User`.`status`, `Topic`.`id`, `Topic`.`title`, `Topic`.`slug` FROM `db52704_driz2013`.`notes` AS `Note` LEFT JOIN `db52704_driz2013`.`users` AS `User` ON (`Note`.`user_id` = `User`.`id`) LEFT JOIN `db52704_driz2013`.`topics` AS `Topic` ON (`Note`.`topic_id` = `Topic`.`id`) WHERE `Note`.`status` = 1 AND ((MATCH(`Note`.`title`) AGAINST(lorem dolor IN BOOLEAN MODE)) OR (MATCH(`Note`.`content`) AGAINST(lorem dolor IN BOOLEAN MODE))) ORDER BY `Note`.`datetime` DESC LIMIT 5 

Answer 1

try it

 $this->paginate = array( 'limit'=>5, 'order'=>'Note.datetime DESC', 'conditions' => array( 'Note.status'=>1, 'OR' => array( "MATCH(Note.title) AGAINST('".$q."' IN BOOLEAN MODE)", "MATCH(Note.content) AGAINST('".$q."' IN BOOLEAN MODE)" ) ) 

);

In other words, enclose your search criteria with quotation marks

EDIT ndm clause makes sense

 $this->paginate = array( 'limit'=>5, 'order'=>'Note.datetime DESC', 'conditions' => array( 'Note.status'=>1, 'OR' => array( 'MATCH(Note.title) AGAINST(? IN BOOLEAN MODE)' => $q, 'MATCH(Note.content) AGAINST(? IN BOOLEAN MODE)' => $q ) ) 

);

Answer 2

You can also try the following:

 $this->paginate = array( 'limit'=>5, 'order'=>'Note.datetime DESC', 'conditions' => array( 'Note.status'=>1, 'OR' => array( 'Note.title LIKE' => "%$q%", 'Note.content LIKE' => "%$q%" ) ) ); 

Answer 3

LIKE will not help you here. Your title is Lorem ipsum and your content is Lorem ipsum dolare . You are looking for a lorem dolare that will not be found with the LIKE operator, since it is not a substring inside any of these columns. You will need to learn FULLTEXT, or you will need to use something like Sphinx . Take a look at this answer here if you are trying to figure out which solution to implement.

Answer 4

 Please Try this: $this->paginate = array( 'limit'=>5, 'order'=>'Note.datetime DESC', 'conditions' => array( 'Note.status'=>1, 'OR' => array( 'Note.title LIKE' => "%".$q."%", 'Note.content LIKE' => "%".$q."%" ) ) );