Page 1 of 1

Boolean search with mysql FullText search

Posted: Sat Mar 08, 2003 11:47 am
by GrayGhost1
I am adding Fulltext search support to a small site and I've run into some trouble. I'm getting an in-"valid MySQL result" error. The offending code snippit is below. Essentially, I have a PHP generated HTML page that presents (among other things) a checkbox for boolean search. When the checkbox is unchecked, everything works. However, when the checkbox is selected, the following message is displayed (based on the boolean search sql statement).

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/...../public_html/.../index.php on line 376

here is the code. I've tried to search without tracking the score ($sql = "select * From news WHERE MATCH (text) AGAINST ( '$searchString' IN BOOLEAN MODE)";) and it still fails.

<snip>
if($booleanSearch)
$sql = "select *, MATCH(text) AGAINST ('$searchString' IN BOOLEAN MODE) AS score From news WHERE MATCH (text) AGAINST ('$searchString' IN BOOLEAN MODE)";
else
$sql = "select *, MATCH (text) AGAINST ('$searchString') AS score From news WHERE MATCH (text) AGAINST ('$searchString')";

<snip>

-Thanks
Mark

Posted: Mon Mar 10, 2003 2:17 am
by twigletmac
Add a little error handling to your mysql_query() to see what MySQL says about the problem:

Code: Select all

$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
You may want to add similar or die statements to your mysql_connect() and mysql_select_db() calls as well just in case they are failing:

Code: Select all

@mysql_connect($host, $user, $pass) or die(mysql_error());
@mysql_select_db($database) or die(mysql_error());
Mac

Boolean fulltext search

Posted: Sat Mar 15, 2003 11:57 am
by GrayGhost1
ok. I added "or die(mysql_error().'<p>'.$sql.'</p>');" and this is the error message.

<snip>
You have an error in your SQL syntax near 'BOOLEAN MODE) AS score From news WHERE MATCH (text) AGAINST ("Php news" IN ' at line 1

select *, MATCH(text) AGAINST ("Php news" IN BOOLEAN MODE) AS score From news WHERE MATCH (text) AGAINST ("Php news" IN BOOLEAN MODE)
<snip>

BTW, I don't believe there's a problem with the connection. (mysql_connect() and mysql_select_db() ) works 100% of the time for the non-boolean searches. Basically, the ONLY difference between the non-boolean code that works and the boolean search code that fails is the select query. See the if statement in my initial post.

Any suggestions? Thanks....
Mark

Posted: Sun Mar 16, 2003 5:24 am
by twigletmac
The error is telling you that there is something wrong with the syntax and if you check the documentation:
http://www.mysql.com/doc/en/String_comp ... ml#IDX1218

It appears that IN BOOLEAN MODE is only available in MySQL version 4.01 or above - if you are using version 3 then that would explain why it is failing.

BTW, I only mentioned debugging your connection and database selection just in case, it's generally a good idea to check out all eventualities.

Mac

Posted: Mon Mar 17, 2003 11:13 pm
by GrayGhost1
Thanks Mac. That must be my problem. I read that section but some how didn't realize that I was running 3.23.54 (well I did but didn't make the connection).

I made sure to check that full text search was supported (version 3.23.23 and above) and sorta let my guard down on the boolean full text search requirement.

Thanks
-Mark