MATCH() AGAINST() error [SOLVED]

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
User avatar
andym01480
Forum Contributor
Posts: 390
Joined: Wed Apr 19, 2006 5:01 pm

MATCH() AGAINST() error [SOLVED]

Post by andym01480 »

This code

Code: Select all

$itemquery="SELECT * FROM cart MATCH(title,description) AGAINST('$keywords')";
$search=mysql_query($itemquery) or die(mysql_error());
is producing this error

Code: Select all

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 'MATCH(title,description) AGAINST('blah')' at line 1
when $keyword="blah" or anything actually.
I've made title and description fulltext in phpmyadmin, there are currently 4 rows in cart, one of which has the word blah in the description field!

I'm using PHP 5.1.1 and MySQL 5.0.18

What have I done wrong?
Last edited by andym01480 on Fri Jun 23, 2006 5:46 pm, edited 1 time in total.
Robert Plank
Forum Contributor
Posts: 110
Joined: Sun Dec 26, 2004 9:04 pm
Contact:

Post by Robert Plank »

The MATCH needs to go inside a WHERE clause.
User avatar
andym01480
Forum Contributor
Posts: 390
Joined: Wed Apr 19, 2006 5:01 pm

Post by andym01480 »

Cheers for the fast reply. The search now works but returns...

Code: Select all

Can't find FULLTEXT index matching the column list.
When I took a look at phpmyadmin teh fulltext button is highlighted for both the columns. Is there something I am missing? How can I check that a fulltext index has been done?
Robert Plank
Forum Contributor
Posts: 110
Joined: Sun Dec 26, 2004 9:04 pm
Contact:

Post by Robert Plank »

You need a composite index for title and description. Not separate ones. Delete the fulltext index for description. Edit the fulltext index for title, and add description as another field in that index.
User avatar
andym01480
Forum Contributor
Posts: 390
Joined: Wed Apr 19, 2006 5:01 pm

Post by andym01480 »

Sorry you have got me there. How do I do that?
Robert Plank
Forum Contributor
Posts: 110
Joined: Sun Dec 26, 2004 9:04 pm
Contact:

Post by Robert Plank »

In phpMyAdmin:

View the structure of the table.
Hit the red X next to the description fulltext index to delete it.
Hit the pencil next to the title fulltext index to edit it.

Ok, now you are editing it and you see columns for field and size.
Add to index 1 column(s), click Go.

You should see a 2nd field listed at the bottom, it says "--Ignore--" ... change this to the description field.
Hit save.

Then try your script again.
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post by printf »

If your title and desription already have a index drop them first, then add full text...

Code: Select all

Query one...

$sql = 'ALTER TABLE `cart` DROP INDEX `title`';

Query two...

$sql = 'ALTER TABLE `cart` DROP INDEX `description`';

Query three...

$sql = 'ALTER TABLE `cart` ADD FULLTEXT(`title`,`description`)';

pif!
Post Reply