MySQL Fulltext Search Problem

rgraves

Hi,

I have a knowledge base database with a table called 'problems' that has two fields, 'heading' and 'problem' which are set to allow fulltext searches over them.

The problem I'm having is this - say I want to search for the word 'internet'. I've noticed that the fulltext searches seem to be case-sensitive. So, if one of the fields has the word 'internet', it will be returned in the results, but if one of the fields as the word 'Internet' (notice the capital i), it will not be returned in the results.

Does anyone know how to force fulltext searches to be case insensitive?

Rob

MrPixel

Except that FULLTEXT searches aren't ever case sensitive ... perhaps 'internet' is too common in your table, ie. 50% or more of the records have that word. In which case it would be ignored. This would happen, if, for example, you had only two rows in your table, and 'Internet' was in one of them.

You can use the IN BOOLEAN MODE to get around this.

SELECT * FROM my_table
WHERE MATCH/b
AGAINST ('Internet' IN BOOLEAN MODE)

herve

May be this is too much time consuming but, something like :

$keyword="Internet";

$sql_cmd = "SELECT * FROM my_table WHERE problem LIKE '%$keyword%' ";

rgraves

Hi,

Okay, I figured it out. It was the fact that the keyword appeared in 50% of the entries. I am doing a complex search over 2 tables and then merging the results since as far as I know, FULLTEXT cannot work over multiple tables (correct me if I'm wrong). This prevented me from noticing that the keyword was in more than 50% of the results in the one table, but not in the other.

Now for problem # 2. I tried searching for the term "sunray" in my table using a fulltext search and it returned nothing. I then tried searching for the term "sunrays" and it returned several results. "sunray" (without the 's') is not found anywhere in my table but "sunrays" (with the 's') is.

Shouldn't a fulltext search still retrieve the records if it only has "sunrays" in them since "sunray" is part of the word "sunrays"?

I'd prefer to use the LIKE keyword in my searches but I like the fact that fulltext searches rank the results like a search engine should.

rgraves

Anybody?