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();

Answer 1

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

3. where() takes 3 arguments, example:

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

4. 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 .'")')