MySQL Full text search problems (solved)

[srg_13]

Hi,

I am having a problem with doing a MySQL Full text search. The query that I am currently using is this:

Code: Select all

$query = "SELECT id, title, description, category, date, time, 
               MATCH(title,description, body) 
               AGAINST ('$query' IN BOOLEAN MODE) AS score FROM table 
               WHERE MATCH(title, description, body) 
               AGAINST ('$query' IN BOOLEAN MODE) ORDER BY score DESC";

That doesn't work. It just returns an error...

I am using MySQL 3.23.57, and acording to the MySQL docs, that version does support full text search.

What's wrong? The error that I am getting is as follows:

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in F:\www\include\mysql.php on line 39

Thanks,

-Stephen

[Kemp]

That error means you didn't get any results back, so the numrows function is passed some equivalent of null instead of a resource handle. Often this means your query was trash and the sql server had no idea what to do with it, I believe zero results from a working query still gives you a valid handle to play with. Can't help you much with the sql itself, but for error messages I can be slightly helpful at least

[Warrior]

Try using mysql_error(); on failure of the query to get the last error which may show some syntax problems.

Code: Select all

$query = @mysql_query("your query");

if (!$query)
mysql_error();
else
// stuff.

It should help point you in the right direction.

[srg_13]

Thanks for that. I solved it. What I had to do to make mysql_error work was:

Code: Select all

$result = mysql_query($query) or die(mysql_error());

It was the boolean mode thing. I will have a look in the MySQL manual again to see how to do this.

-Stephen

[Kon-Tiki]

What exactly're you trying to do?

[srg_13]

Well, I was trying to do a full text search of a MySQL table.

Here's the working query:

Code: Select all

$db_query = "SELECT id, title, description, catagory, date, time, author,comments,
      MATCH(title,body,description) 
      AGAINST ('$query') AS score
      FROM table 
      WHERE MATCH(title,body,description) 
      AGAINST ('$query') ORDER BY score DESC";

-Stephen

[Kon-Tiki]

What exactly's in $query, that it can match three fields?

[srg_13]

search.php:

Code: Select all

<h2>Search Articles</h2>
<form action="results.php" method="post">
Enter a term to search for:
<input type="text" name="query" /><br />
<input type="submit" value="Search"/>
</form>

results.php:

Code: Select all

<?php 
if(isset($_POST['query'])
{
   $query = $_POST['query'];
}
else
{
   echo 'Please enter a term to search for';
   die();
}

$db_query = "SELECT id, title, description, catagory, date, time, author,comments,
      MATCH(title,body,description)
      AGAINST ('$query') AS score
      FROM table
      WHERE MATCH(title,body,description)
      AGAINST ('$query') ORDER BY score DESC";

then after that it does all the mysql connection stuff, and sends the query, and then lists the results of the search.

Code: Select all

?>

-Stephen