Page 1 of 1
MATCH AGAINST: what's wrong with this FULL TEXT?
Posted: Tue Apr 17, 2012 5:37 am
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?
Re: MATCH AGAINST: what's wrong with this FULL TEXT?
Posted: Tue Apr 17, 2012 6:22 am
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
Re: MATCH AGAINST: what's wrong with this FULL TEXT?
Posted: Tue Apr 17, 2012 6:28 am
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.