Reputation: 397
I have this SQL query that keeps shooting me an error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR
description
IN BOOLEAN MODE) ORDER BYdatetime
ASC LIMIT 0,20' at line 4
This is the query I'm attempting to run:
$items = $itemsClass->getAll($start , $limit, " `status` = 'active' AND MATCH (".sql_quote($s).") AGAINST (`name` OR `description` IN BOOLEAN MODE) ".$whereQuery, "$order");
Why: I'm working on a search function for a website and when we search for a certain keyword (i.e. cake), it shows the relavent items. But, when we search for 'cook' which is also in the same title/ description as the cake item, it doesn't result any results and says it's empty.
I can't seem to figure out why I'm getting this error for the query. I'm just trying to find a solution to my problem. I'm using MySQL 5.1.66.
Upvotes: 1
Views: 80
Reputation: 563021
First of all, you put column names in the MATCH()
function, and patterns in the AGAINST()
function. You're doing it the wrong way around.
Second, searches across multiple columns are implicitly "OR" comparisons, in that a keyword match in any of the indexed columns counts as a match.
So you should use:
... MATCH (`name`, `description`) AGAINST (".sql_quote($s).") IN BOOLEAN MODE)"
The columns you name in the MATCH()
function must be all the columns you defined in your fulltext index, and in the same order as they appear in the index.
Only if you defined two separate fulltext indexes, one for each column, then you need to use two separate predicates.
... MATCH (`name`) AGAINST (".sql_quote($s).") IN BOOLEAN MODE)
OR
MATCH (`description`) AGAINST (".sql_quote($s).") IN BOOLEAN MODE)"
Upvotes: 1
Reputation: 385405
You wrote:
MATCH ("your string") AGAINST (`name` OR `description` IN BOOLEAN MODE)
However, AGAINST
(like other operators) is not associative in this fashion. The expression it takes is just that — an expression; the use of connectives such as OR
is not unwrapped to apply to the wider operation, but used simply to evaluate to the final expression.
So, usually, you must place the connective OR
outside the operator call:
MATCH ("your string") AGAINST `name` IN BOOLEAN MODE
OR
MATCH ("your string") AGAINST `description` IN BOOLEAN MODE
However, in this instance you also switched the operands to MATCH
.
Write:
MATCH (`name`) AGAINST "your string" IN BOOLEAN MODE
OR
MATCH (`description`) AGAINST "your string" IN BOOLEAN MODE
And it so happens that MATCH
inherently supports searching multiple columns.
So, finally:
MATCH (`name`, `description`) AGAINST "your string" IN BOOLEAN MODE
The moral of the story is that the documentation is your friend.
Upvotes: 0