CODEWITHSUNDEEP
phpmysqlsql
user1086337
user1086337

Reputation: 397

Am I using MATCH() correctly?

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 BY datetime 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

Answers (2)

Bill Karwin
Bill Karwin

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

Lightness Races in Orbit
Lightness Races in Orbit

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

Related Questions