Page 1 of 1

MATCH() AGAINST() error [SOLVED]

Posted: Fri Jun 23, 2006 1:36 pm
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?

Posted: Fri Jun 23, 2006 1:43 pm
by Robert Plank
The MATCH needs to go inside a WHERE clause.

Posted: Fri Jun 23, 2006 1:51 pm
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?

Posted: Fri Jun 23, 2006 1:52 pm
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.

Posted: Fri Jun 23, 2006 1:55 pm
by andym01480
Sorry you have got me there. How do I do that?

Posted: Fri Jun 23, 2006 2:24 pm
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.

Posted: Fri Jun 23, 2006 5:42 pm
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!