FULLTEXT search returning nothing. CI or MySQL problem? Help?

[eluser]Hickeroar[/eluser]
I've added FULLTEXT keys on the "content" and "title" fields in my pages table (added them after the table already had some things populated into it, but I re-indexed the table since then using the OPTIMIZE query) I've got 108 rows in the table.

Here's my query:

Code:

$this->db->from("pages");
$this->db->select("title, content");
$this->db->where('MATCH(content) AGAINST ("description") >', "0", FALSE);
$this->db->limit(10);

This returns no results, every time and I KNOW that "description" is in the content field in some pages. I also know that "the" is and I tried that without success as well.

I've also tried using the where statement as a select, and it returns zero for every row.

Any insight that might help me?

[eluser]jkevinburton[/eluser]
read up on FULLTEXT searches, there is something about having to have a minimum amount of data, and it does search for small words like "the", "is", "and" etc..

I know my response is not as helpful, but WAAAAY back i tried FULLTEXT searches but couldn't because of the limitations of my data.

[eluser]Hickeroar[/eluser]
Hmmm. I THINK the row count limit is 3 rows, so I'm good there. words like "the" make sense though. Gah. This is so frusterating.

[eluser]hvalente13[/eluser]
Hi,

I solved my FULLTEXT search problem by doing this:

Code:

$this->db->from("pages");
$this->db->select("title, content, MATCH(content) AGAINST ("description") as rank ");
$this->db->where('MATCH(content) AGAINST ("description")');
$this->db->orderby('rank DESC');
$this->db->limit(10);

I just used your code for example. Mine was to complex to write here.

Try it and tell me if it works. I guess the WHERE clause in FULLTEXT search doesn't like the > = != < operators. It's just a guess. I'm here to learn as everybody. Not an expert.

Good luck

[eluser]Hickeroar[/eluser]
I'll give this a try and report back my findings.

[eluser]hvalente13[/eluser]
One more thing that I've forgot. You've have to use IN BOOLEAN MODE

Code:

$this->db->select("title, content, MATCH(content) AGAINST ("description" IN BOOLEAN MODE) as rank ");

It has to have IN BOOLEAN MODE to give you an integer or if you want to had rank by field you could do like this:

Code:

$this->db->select("title, content, ((1.5*(MATCH(content) AGAINST ("description" IN BOOLEAN MODE)))+((1*(MATCH(content) AGAINST ("other_field" IN BOOLEAN MODE))) ) as rank ");

This is useful if you have more than one search keyword.

Give it a try.

[eluser]Devon Lambert[/eluser]
Just out of curiosity, why use FULLTEXT and not LIKE?

Is there a way to perform multiple keyword searches using just LIKE?

[eluser]Hickeroar[/eluser]
[quote author="dnyce" date="1216539099"]Just out of curiosity, why use FULLTEXT and not LIKE?

Is there a way to perform multiple keyword searches using just LIKE?[/quote]

With fulltext you can have someone search for:

"amy cookies"

and it'll find "Amy is a big fan of cookies!"

Case insensitive and all...

You can't easily do something like that with "like"

[eluser]Hickeroar[/eluser]
This worked perfectly for me:

Code:

$this->db->from("pages");
$this->db->select("title, page_id, url, (MATCH(title) AGAINST(".$this->db->escape($strSearchTerm).") + MATCH(content) AGAINST(".$this->db->escape($strSearchTerm).")) as rank");
$this->db->where("(MATCH(title) AGAINST(".$this->db->escape($strSearchTerm).") + MATCH(content) AGAINST(".$this->db->escape($strSearchTerm).")) >", "0");
$this->db->limit($intLimit, $intOffset);
$this->db->orderby('rank DESC');

Too bad there's no decent way to do this without having to run the match stuff twice. Any insight for my fulltext noobness might be helpful.

[eluser]hvalente13[/eluser]
Hi dnyce,

With MATCH AGAINST syntax you'll have a wider result table. But if you want to build your search upon LIKE syntax you could do it like this. Of course in this case CI's Active Record doesn't do much of anything, so I use in this example simple SQL syntax:

Code:

$sql = "SELECT *,( ";

// Here you can do a php foreach keyword
// Begin loop
$sql .= " (CASE WHEN $field LIKE '%".$keyword."%'
    THEN 1
    ELSE 0
    END
    ) +";
$sql .= "(CASE WHEN $field LIKE '%".$keyword2."%'
    THEN 1
    ELSE 0
    END
    )";
// End loop
$sql .= " ) as rank";
$sql .= " FROM table ORDER BY rank DESC";

$query = $this->db->query($sql);

Hope this helps