CakePHP find query using %like%

Question

I have the following find query for my CakePHP app:

$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 called $q to do like query on both title and content.

So for example if I have the following:

Title: Lorem ipsum Content: Lorem ipsum dolare

And search for 'lorem'

It would find it fine. But if I search for 'lorem dolare' it won't find it.

How do I do that?

Note: I don't want to use any plugins etc.

EDIT: If I use FULLTEXT would 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 trying the 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

I was thinking of just what Wayne posted below `FULLTEXT` search. Have a look at this Q&A on SO http://stackoverflow.com/q/17294924/1415724 may help. — Funk Forty Niner, Nov 28 '13 at 20:55

score 8 · Accepted Answer · edited Aug 02 '18 at 13:35

8

Try this

$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 the search criteria with quotes

EDIT ndm's suggestion 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
        )
    )
);

edited Aug 02 '18 at 13:35

Blue

22,608
7
62
92

answered Nov 28 '13 at 21:20

Guillermo Mansilla

3,779
2
29
34

2

It's also a perfectly nice SQL injection vulnerability! ;) Please use the proper [**array syntax**](http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#complex-find-conditions) instead: `"MATCH(Post.title) AGAINST(? IN BOOLEAN MODE)" => $q` @Cameron – ndm Nov 28 '13 at 23:17
@ndm Can you post EXACTLY what you think the whole thing should look like? Thanks – Cameron Nov 28 '13 at 23:28

monsur.hoq · Answer 2 · 2014-10-16T06:38:37.893

7

You can also 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%"
        )
    )
);

edited Oct 16 '14 at 06:38

answered Oct 16 '14 at 06:31

monsur.hoq

1,135
16
25

score 1 · Answer 3 · edited May 23 '17 at 11:46

1

LIKE won't help you here. Your title is Lorem ipsum and your content is Lorem ipsum dolare. You're searching for lorem dolare, which won't be found via the LIKE operator as it is not a substring inside either of those columns. You'll need to look into using FULLTEXT or you'll need to use something like Sphinx. Take a look at this answer here if you're trying to figure out what solution to implement.

edited May 23 '17 at 11:46

Community

1
1

answered Nov 28 '13 at 20:50

Wayne Whitty

19,513
7
44
66

What would you suggest then? – Cameron Nov 28 '13 at 20:51
Could you share an example? I found this: http://stackoverflow.com/questions/10827444/cakephp-fulltext-search-mysql-with-rating any good for what I want? – Cameron Nov 28 '13 at 20:54
You'll find plenty of examples floating around. I must note that you'll need to use MyISAM if you want to implement FULLTEXT, and that has a few drawbacks. Have a look here before you make a choice: http://stackoverflow.com/a/1381860/491494 – Wayne Whitty Nov 28 '13 at 20:57
@Cameron You'll have to test. Although in order for FULLTEXT to work, you'll need to change the table from INNODB to MyISAM. – Wayne Whitty Nov 28 '13 at 21:11
If your search conditions are somewhat complex then I recommend to use our [plugin](https://github.com/cakedc/search) – Guillermo Mansilla Nov 28 '13 at 21:11
I've tried the code and got a SQL error. Any ideas what the problem is? I've changed the table to MyISAM. – Cameron Nov 28 '13 at 21:16
1

You're missing single quotes around your $q variable. 'MATCH(Note.title) AGAINST('.$q.' IN BOOLEAN MODE)' should be 'MATCH(Note.title) AGAINST(\''.$q.'\' IN BOOLEAN MODE)' for example. – Wayne Whitty Nov 28 '13 at 21:20

score 0 · Answer 4 · answered Jun 05 '15 at 09:43

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."%"
        )
    )
);

CakePHP find query using %like%

4 Answers4