problems with mysql fulltext search

LSArcher

hello,

i am working on a Search Script over my mySQL Database, which works fine as long as i am just searching over 1 Field, but when i start to insert more the Query doesn't work anymore.

Researching FAQ and Boards didn't help, so i have to ask here

This query works fine:
$result = mysql_query("select * from products where match(ArtNumber) against ('$searchword1 $searchword2 $searchword3')",$db) or die ("error");

This one doesn't:
$result = mysql_query("select * from products where match(ArtNumber,Description) against ('$searchword1 $searchword2 $searchword3')",$db) or die ("error");

ArtNumber and Description (and other fields) are defined as Fulltext Fields via phpMyAdmin, and the mySQL Version is 3.23.49

Anyone a Clue why this doesn't work out the way it should?

Could there probably be a Provider Restriction?

Thanks for reading and Greetings
LSArcher

jasonn

I am finding the exact same problem. Does anybody have the answer?

jason.

bombytza

If I'm not mistaking, when you want to do a mysql fulltext serach on more than one field, you must have defined the index on all columns you want together, like this

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   [B]FULLTEXT (title,body)[/B]
    -> );

It doesn't work if you've created a separated fulltext index for each column and you try to search on both columns in the same time.
You can find this in mysql manual, in the fulltext index chapter.

Hope this helps