Fulltext query problem due to escaping

[eluser]Skinnpenal[/eluser]
Hi!

I'm having a hard time with my fulltext search after upgrading from 1.5.4 to 1.6.1.

My question is; how do you do fulltext searches with codeigniter active record?



This is what I've tried:

Code:

$where = "MATCH (title) AGAINST ('$search_term')";

$ci->db->select('id, '.$where.' AS score');
$ci->db->where($where);
$ci->db->where("id IN ($pages_as_string)");

But with the escaping issue, this is the query produced:

Code:

SELECT `id`, `MATCH` (`title`) `AGAINST` ('my-search-term') AS score
FROM (`my-table`) WHERE `MATCH` (title) AGAINST ('my-search-term') AND `id` IN (1,2,3)

- Where you see that the MATCH, title, AGAINST and id in the end has been escaped, which natually causes the query to fail.

And so, I add the optional parameter to turn off escaping (, false).

Code:

$ci->db->select('id, '.$where.' AS score', false);
$ci->db->where($where, false);
$ci->db->where("id IN ($pages_as_string)", false);

But then, for some strange reason, CI adds zero's to the SQL, and it ends up like this:

Code:

SELECT id, MATCH (title) AGAINST ('my-search-term') AS score
FROM (`my-table`) WHERE MATCH (title) AGAINST ('my-search-term') 0 AND id IN (1,2,3) 0

Anyone with tips on how I can fix this?

[eluser]Chris Newton[/eluser]
WHERE doesn't accept the FALSE parameter, unlike the SELECT statement. It automatically escapes data. CI's active record isn't perfect for everything, and my guess is that you might have more luck just writing your own queries in this instance.

You could use the LIKE method.

Code:

$this->db->like('title', '%'.$search_term.'%');

// Produces: WHERE title LIKE '%my_search_term%'

[eluser]xwero[/eluser]
[removed]