Boolean search with mysql FullText search

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
GrayGhost1
Forum Newbie
Posts: 3
Joined: Sat Mar 08, 2003 11:47 am

Boolean search with mysql FullText search

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
GrayGhost1
Forum Newbie
Posts: 3
Joined: Sat Mar 08, 2003 11:47 am

Boolean fulltext search

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
GrayGhost1
Forum Newbie
Posts: 3
Joined: Sat Mar 08, 2003 11:47 am

Post 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
Post Reply