Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

MySQL Fulltext Index Search won't work!

Hi there,

Hope you can help, I am stuck.

I've got a table called tblquestions.

In it is a table called questions, with the following fields:

questionID, int, 6
question, varchar, 255
answer, varchar, 255

The question column has a fulltext index and I am trying to query it using the following sql statement:

$strSQL = SELECT questionID, question, answer FROM tblquestions WHERE MATCH (question) AGAINST ('" . $theQuestion . "')";

For some reason it tells me I haev an error in my sql statement, but if I echo the statement and execute it against the database directly with my MySQL GUI it doesn't give me an error. I have tried it with and without funny characters like apostrophies to no avail. Even odder, is that although I don't get an error when I execute the statement with the GUI, it still finds no matches, even though I know for sure there is a match. I have an identical SQL Statement working just fine on another site I built so this driving me bananas now!

Hope you can help!

Many thanks in anticipation!

P
0
m175400
Asked:
m175400
  • 6
  • 4
1 Solution
 
ldbkuttyCommented:
There are certain constraints for fulltext search, make sure your query passes these constraints:

# Excludes words that appear in more than half the rows (meaning at least 3 rows are required)
# Excludes partial words.
# Excludes words less than 4 characters in length.
# Excludes common words (stopwords) in query.

To escape special characters, this is the syntax:

$strSQL = SELECT questionID, question, answer FROM tblquestions WHERE MATCH (question) AGAINST ('" . mysql_real_escape_string($theQuestion) . "')";

If the query passes all the constraints, post the result of echo $strSQL.
0
 
m175400Author Commented:
I'm pretty certain that it meets all the necessary criteria.

Here is the statement echoed:

SELECT questionID, question, answer FROM tblquestions WHERE MATCH (question) AGAINST ('How old')

There are 7 records in the database, values for the question column being:

How old are you?
How old is the Queen of England?
How old is my aunt?
Where do you live?
What is the point?
Where does The Pope live?
Where is my nearest pub?
0
 
ldbkuttyCommented:
Nope! Both "How" and "old" are two words and both the words are 3 letters.

Words less than 4 characters in length (3 or less) will be excluded!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
m175400Author Commented:
Oh naff! Even though they're a phrase in this context!? DOH!

Any other way to produce search results ordered by score similar to this? There is a way to override the 4 characters setting if I remember rightly?
0
 
ldbkuttyCommented:
Yes, from MySql manual: http://dev.mysql.com/doc/mysql/en/fulltext-fine-tuning.html

====

The minimum and maximum length of words to be indexed is defined by the ft_min_word_len and ft_max_word_len system variables (available as of MySQL 4.0.0). See Section 5.2.3, “Server System Variables”. The default minimum value is four characters. The default maximum depends on your version of MySQL. If you change either value, you must rebuild your FULLTEXT indexes. For example, if you want three-character words to be searchable, you can set the ft_min_word_len variable by putting the following lines in an option file:

[mysqld]
ft_min_word_len=3

Then restart the server and rebuild your FULLTEXT indexes. Also note particularly the remarks regarding myisamchk in the instructions following this list.

====
0
 
m175400Author Commented:
Hmm yeah I've read that.

Not really sure how to do that though?

If I bought some more points and made this question worth more is this something you could help me with please?

Thanks for all your help so far by the way!
0
 
ldbkuttyCommented:
Its not the points that matter here but I neither tried to reduce the minimum of length of fulltext search!

My suggestion is post a question in CS requesting to reduce the points from 110 to 20 points and post a question in MySQL TA with your remaining points. Since I am not able to help you further, I've no problem in getting this question deleted (which I think will take 4 days) or to reduce the points to 20. (which will be done by CS immediately).
0
 
ldbkuttyCommented:
I dont know if you've already done this, but you can get extra 200 points if you fill the survey in EE.
0
 
ldbkuttyCommented:
Any updates m175400 ?
0
 
m175400Author Commented:
Hi there!

Sorry, forgot all about this. Have posted a request as per your suggestion

http://www.experts-exchange.com/Community_Support/Q_21409203.html

Thanks for you help, much appreciated!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now