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

Posted: 17 July 2008 10:16 AM

[ Ignore ]

Summer Student

Total Posts: 20

Joined 06-19-2007

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:

$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?

Profile

[CoDeR]

Posted: 17 July 2008 02:20 PM

[ Ignore ] [ # 1 ]

Lab Assistant

Total Posts: 159

Joined 11-06-2006

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.

Signature

“We have just gotten a wake-up call from the Nintendo Generation.” - cerealkiller; Hackers (1995)

Profile

Hickeroar

Posted: 17 July 2008 02:21 PM

[ Ignore ] [ # 2 ]

Summer Student

Total Posts: 20

Joined 06-19-2007

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.

Profile

hvalente13

Posted: 18 July 2008 09:59 AM

[ Ignore ] [ # 3 ]

Grad Student

Total Posts: 59

Joined 10-26-2007

Hi,

I solved my FULLTEXT search problem by doing this:

$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

Signature

CodeIgniter Portugal { ci_pt }

http://www.e-coisas.com

Profile

Hickeroar

Posted: 18 July 2008 11:31 AM

[ Ignore ] [ # 4 ]

Summer Student

Total Posts: 20

Joined 06-19-2007

I’ll give this a try and report back my findings.

Profile

hvalente13

Posted: 18 July 2008 12:09 PM

[ Ignore ] [ # 5 ]

Grad Student

Total Posts: 59

Joined 10-26-2007

One more thing that I’ve forgot. You’ve have to use IN BOOLEAN MODE

$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:

$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.

Signature

CodeIgniter Portugal { ci_pt }

http://www.e-coisas.com

Profile

dnyce

Posted: 19 July 2008 09:31 PM

[ Ignore ] [ # 6 ]

Grad Student

Total Posts: 63

Joined 05-04-2008

Just out of curiosity, why use FULLTEXT and not LIKE?

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

Signature

CL Auth Integration with Vanilla
Reality Knights - Have more fun
Play over 2500+ Free Arcade Games

Profile

Hickeroar

Posted: 21 July 2008 10:29 AM

[ Ignore ] [ # 7 ]

Summer Student

Total Posts: 20

Joined 06-19-2007

dnyce - 19 July 2008 09:31 PM
Just out of curiosity, why use FULLTEXT and not LIKE?

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

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”

Profile

Hickeroar

Posted: 21 July 2008 10:39 AM

[ Ignore ] [ # 8 ]

Summer Student

Total Posts: 20

Joined 06-19-2007

This worked perfectly for me:

$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.

Profile

hvalente13

Posted: 21 July 2008 10:59 AM

[ Ignore ] [ # 9 ]

Grad Student

Total Posts: 59

Joined 10-26-2007

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:

$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

Signature

CodeIgniter Portugal { ci_pt }

http://www.e-coisas.com

Profile

dnyce

Posted: 21 July 2008 11:39 AM

[ Ignore ] [ # 10 ]

Grad Student

Total Posts: 63

Joined 05-04-2008

Thanks for that.

I did actually build out my search using CI’s AR class for now. However, I will definitely be re-visiting search at a later time to make a more solid search function within my site.

Signature

CL Auth Integration with Vanilla
Reality Knights - Have more fun
Play over 2500+ Free Arcade Games

Profile

loathsome

Posted: 21 July 2008 02:11 PM

[ Ignore ] [ # 11 ]

Grad Student

Total Posts: 77

Joined 07-10-2008

Remember to make sure you actually have a fulltext index on the tables you’re matching against

Profile

Hickeroar

Posted: 21 July 2008 02:20 PM

[ Ignore ] [ # 12 ]

Summer Student

Total Posts: 20

Joined 06-19-2007

loathsome - 21 July 2008 02:11 PM
Remember to make sure you actually have a fulltext index on the tables you’re matching against

:-D

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)

Profile

loathsome

Posted: 21 July 2008 02:29 PM

[ Ignore ] [ # 13 ]

Grad Student

Total Posts: 77

Joined 07-10-2008

now there you go.

*drinks more coffee*

Profile

Hickeroar

Posted: 21 July 2008 02:33 PM

[ Ignore ] [ # 14 ]

Summer Student

Total Posts: 20

Joined 06-19-2007

loathsome - 21 July 2008 02:29 PM
now there you go.

*drinks more coffee*

ooooh, coffee sounds great. I think I’m going to go put some on.

Profile

‹‹ Upload TIF in IE 6 Quick htaccess/redirect question ››

Username		Remember Me?
Password		forgot password?

Post Marker Legend
	New posts	Hot Topic with new posts	New Poll	Moved Topic	Sticky topic
	No new posts	Hot Topic with no new posts	Old Poll	Closed Topic	Announcements

Visitor Statistics
The most visitors ever was 719, on June 06, 2008 10:16 AM
Total Registered Members:	62653	Total Logged-in Users:	30
Total Topics:	77188	Total Anonymous Users:	0
Total Replies:	416681	Total Guests:	233
Total Posts:	493869