Jump to content

[SOLVED] Full Text Search Error : Can't find FULLTEXT index matching the column list.

suttercain

By suttercain
in MySQL Help

Recommended Posts

suttercain Advanced Member

suttercain

Posted
Posted

Hi everyone,

 

I have done full text searches in the past but it's been a while. I created three columns. id, title and body. The table is MyISAM and the title and body columns have been set to 'FULL TEXT' to allow the search to work.

 

Here is the basic code I am using to get this going:

<body>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<input type="text" name="search" id="search" /><input type="submit" name="submit" />
</form>

<?php
if (isset($_POST['search'])) {
include('connection.php');
$search = mysql_real_escape_string(trim($_POST['search']));

$sql = "SELECT id, title, 
         MATCH (title, body) 
	 AGAINST ('".$search."') 
	 AS score 
	 FROM faq 
	 WHERE MATCH (title, body) 
	 AGAINST ('".$search."')";

  $res = mysql_query($sql) or die(mysql_error());
  unset($_POST['search']);
}//end if
?>
</body>

 

When I run this code I get the following MySQL error code:

Can't find FULLTEXT index matching the column list.

 

Any suggestions? Thanks.

suttercain Advanced Member

suttercain

Posted
  • Author
Posted

Thanks Fenway. That solved that issue  ;D

 

But now when I run the following code I don't get any results:

<?php
$sql = "SELECT body, title FROM faq1 WHERE MATCH (body, title) AGAINST ('vehicles')";
?>

 

But this code does work, but is not a full-text search:

<?php
$sql = "SELECT * FROM faq1 WHERE title LIKE '%Vehicles%'";
?>

 

What am I doing wrong in the first query statement? Thanks again!

fenway Advanced Member

fenway

Posted
Posted

If you read the refman page on FULLTEXT, you'll see that it doesn't always return "everything" you would expect.  If you don't have a large record set, of if your query matches too many records, you'll get unexpected output.

suttercain Advanced Member

suttercain

Posted
  • Author
Posted

Thanks Fenway,

 

I have 6 records I am trying to query at the moment with more to be added once I validate this works. I am concerned about the "too many" statement you quoted. That's scary because people may not find what they want. "doesn't always return "everything" you would expect" makes me wonder if I should use this form of search because that seems like a big pit fall.

 

Thanks again.

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.