Can't find FULLTEXT index matching the column list

sherdog

well my problem is starting to piss me off, first of all there is my table structure

CREATE TABLE customer (
cid int(11) NOT NULL auto_increment,
company_name varchar(255) NOT NULL default 'n/a',
company_title varchar(255) NOT NULL default 'n/a',
fname varchar(255) NOT NULL default '',
lname varchar(255) NOT NULL default '',
add1 varchar(255) NOT NULL default '',
add2 varchar(255) NOT NULL default '',
city varchar(255) NOT NULL default '',
state varchar(255) NOT NULL default '',
zip varchar(10) NOT NULL default '',
country varchar(255) NOT NULL default '',
phone varchar(255) NOT NULL default '',
fax varchar(255) NOT NULL default '',
email varchar(255) NOT NULL default '',
b_email varchar(255) NOT NULL default '',
status varchar(10) NOT NULL default '',
PRIMARY KEY (cid),
UNIQUE KEY cid (cid),
FULLTEXT (fname, lname, b_email)
)TYPE=MyISAM;

as you can see there is FULLTEXT on fname, lname, and b_email.

now here is my query

just simply looking for a match via fname

$query = "SELECT * FROM customer WHERE MATCH (fname) against ('$fname')";

and im getting the error

Can't find FULLTEXT index matching the column list

what is wrong?
this should be simple and easy and i dont understand what is going on!

any help and i will call you god =)

steadyguy

I think your problem is that you defined three FULLTEXT columns, and are querying only one - you need to have all three in the query:
SELECT *
FROM customer
WHERE MATCH (fname, lname, b_email)
AGAINST('$fname')";

sherdog

yup! thanks man, "god" =)

Maarten_Malaise

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

I seemed to have a problem with my fulltext search. It took me some time to solve this. And I like to show you what was wrong, in case anyone else is getting a headache.

It's important to add a fulltext WITH AN INDEX or create them as a set in stead of creating them one by one. It was confusing at first because of the phpMyAdmin interface I was using. To create a correct FULLTEXT, you need to run a command similar to this one:

create fulltext index fulltext_index on news (title,subtitle,newstext);

I hope this will help some people having the same problem.

Maarten Malaise
http://www.politics.be