full text search in codeigniter

Question

I have a query to search keywords using like, but I also want to search full text so I changed it to full text search query, but it doesn't work.

The old query that's working fine:

$data = $this->db
    ->select('content.title,content.id,content.category,content.body,path')
    ->from('content')   
    ->join('categories','content.category = categories.id')             
    ->like('content.title', $searchterm)
    ->like('content.body', $searchterm)
    ->order_by("content.id","DESC")
    ->get();

and this is my new query for full text search:

$data = $this->db
    ->select('content.title,content.id,content.category,content.body,path')
    ->from('content')   
    ->join('categories','content.category = categories.id')                 
    ->where('MATCH (content.body, content.title) AGAINST ("'. $searchterm .'")')
    ->order_by("content.id","DESC")
    ->get();

score 5 · Accepted Answer · answered Apr 21 '14 at 11:34

if you are using mysql version 5.5 or lower, make sure all the tables involved have the engine MyISAM.
make sure the your column has the FULLTEXT INDEX.

where() takes 3 arguments, example:

$this->db->where('MATCH (field) AGAINST ("value")', NULL, FALSE);
$this->db->get('table');

more than one columns in match() triggers Error Number: 1191, so separate them:

->where('MATCH (content.title) AGAINST ("'. $searchterm .'")')
->where('MATCH (content.body) AGAINST ("'. $searchterm .'")')

score 1 · Answer 2 · answered Apr 21 '14 at 09:18

1

Try by changing the where clause in your query, see if it helps:

->where('MATCH (content.body, content.title) AGAINST ("'. $searchterm .'")', NULL, false)

This sets the value to NULL and tells CI not to escape the string.

answered Apr 21 '14 at 09:18

Nil'z

7,487
1
18
28

score 0 · Answer 3 · answered Apr 21 '14 at 09:37

0

you did not use the correct syntax of mathch .... against try this:

    ->where MATCH (content.body, content.title) AGAINST ("'. $searchterm .'") > 0

answered Apr 21 '14 at 09:37

ali panahi

78
1
2
8

score 0 · Answer 4 · answered Mar 28 '20 at 21:37

if you have multiple column you can use them in single line like this:

->where("MATCH(title, model) AGAINST('$text')", null, false);

or just one column:

->where("MATCH(title) AGAINST('$text')", null, false);

don't forget to escape your inputs. because we disabled escaping with that false over there. use this for escaping:

$text = $this->db->escape_str($text);

full text search in codeigniter

4 Answers4