MATCH AGAINST: what's wrong with this FULL TEXT?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

MATCH AGAINST: what's wrong with this FULL TEXT?

Post by simonmlewis »

Code: Select all

<?php
$q=$_GET["q"];
include "dbconn.php";
$result = mysql_query ("SELECT * FROM faq WHERE MATCH(question,answer) AGAINST ('$q')")or die(mysql_error());
$num_rows = mysql_num_rows($result); 
if ($num_rows == 1) { echo "<div class='sectionhead'>Does this answer your question?</div>";}
if ($num_rows > 1) { echo "<div class='sectionhead'>Do these answer your question?</div>";}
while ($row = mysql_fetch_object($result)){
echo "<i>$row->question</i><br/>$row->answer<hr noshade size='1' />";
} mysql_free_result($result);
mysql_close($sqlconn);
?>
This works thru Ajax. When "battery" is entered, which is in both these fields, I get this error immediately:
[text]Can't find FULLTEXT index matching the column list[/text]
Why?
Attachments
This is the MySQL Local Host screen
This is the MySQL Local Host screen
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: MATCH AGAINST: what's wrong with this FULL TEXT?

Post by mikosiko »

Can't find FULLTEXT index matching the column list
because you don't have a FULLTEXT index covering both columns (question, answer)... you only have individual indexes on each column... from the manual:
The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE. Boolean-mode searches can be done on nonindexed columns, although they are likely to be slow.
therefore you have 2 options:
1) create the fulltext index covering both columns or
2) add the modifier "IN BOOLEAN MODE" to your search string
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: MATCH AGAINST: what's wrong with this FULL TEXT?

Post by simonmlewis »

Brilliant.
BOOLEAN MODE seems to do the trick. It's not a vast database so I'm not concerned about Speed.
I didnt' know about the FULLTEXT covering b oth columns as one. I never normally use MATCH.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply