FULLTEXT search

mizzy posted this at 14:04 — 2nd September 2003.

Joined: Jun 2001

Hi I am trying to use the fulltext feature of MySQL. I am running this querry to try and get a listing of product names and shor description.

"SELECT * FROM product WHERE MATCH (name,description) AGAINST ('+$search')";

I however keep getting an error which I need help with... For any search querry, it says

"Can't find FULLTEXT index matching the column list"

my database structure is as follows...

CREATE TABLE product (
ID tinyint(9) NOT NULL auto_increment,
cat_ID tinyint(9) default NULL,
name varchar(100) NOT NULL default '',
description text,
top tinyint(4) NOT NULL default '0',
PRIMARY KEY (ID),
FULLTEXT KEY description (description),
FULLTEXT KEY name (name)
) TYPE=MyISAM;

What am I doing wrong?
Thanks
Mizzy

NB: I am running MySQL 3.23.56

ShaneS posted this at 18:56 — 2nd September 2003.

They have: 93 posts

Joined: Jun 2003

Well I see a few things here...

Not sure where you got the code, but I belive that is 4.0.X not 3.X code.

First off the create, I dont remeber seeing a "KEY" ever used, it is called an index used the INDEX namehere..

Quote: FULLTEXT

[index_name] (index_col_name,...)

Next we have your creation, you used a single column at a time. If you want them to be search together as you did in your query you must combined them like SO....

Quote: FULLTEXT INDEX namdesc (name,description)

Now for you search. MySQL 3.23.56 does not support boolean mode, only 4.0 and higher, so your search criteria can not have the "+" operators.

Now onto the problem...

The field list must be the FULLTEXT index name, if none is given you specify the fields in the proper order.

Quote: All parameters to the MATCH() function must be columns from the same table that is part of the same FULLTEXT index, unless the MATCH() is IN BOOLEAN MODE.

The MATCH() column list must exactly match the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE.

So you had 2 indexes requireing they each be matched seperatly. The documents are a little tough to follow, but because you gave indexes you Match would have have been Match(name) Against() AND Match(description) Against().

For this reason I suggest making index have a unique name not the same or too similar to a table's column name.

If you use my above Create fix, you can keep your existing query less the boolean operator. That will be the only way to search both field together as one FULLTEXT index.

Here is an example to help better uderstand how it works:
CREATE TABLE blank(foo tinytext, boo tinytext, zoo tinytext, doo tinytext, FULLTEXT INDEX fb(foo,boo), FULLTEXT INDEX zd(zoo,doo));

You select for fb can be MATCH(fb) or MATCH(foo,boo), one used the index, the other the columns that make the fulltext. Same applies for zd.

Hope that helps

[Design Alpha] -Web Services : Design,Hosting,Advertising,Software
Ask about custom pricing on hosting!!
Site Assets: [UltraGaming.com] [Blades of Warcraft]