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
LVL 1
m175400Asked:
Who is Participating?
 
ldbkuttyConnect With a Mentor Commented:
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:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.