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
Boolean search with mysql FullText search
Moderator: General Moderators
-
GrayGhost1
- Forum Newbie
- Posts: 3
- Joined: Sat Mar 08, 2003 11:47 am
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
Add a little error handling to your mysql_query() to see what MySQL says about the problem:
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:
Mac
Code: Select all
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');Code: Select all
@mysql_connect($host, $user, $pass) or die(mysql_error());
@mysql_select_db($database) or die(mysql_error());-
GrayGhost1
- Forum Newbie
- Posts: 3
- Joined: Sat Mar 08, 2003 11:47 am
Boolean fulltext search
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
<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
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
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
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
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
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