Fulltext?

furious5

Can anyone help me, I am trying to do a keyword search of a table I have.

Basically I want to search the title, description and location fields of the job table against a string to be passed from a form. At the moment I have just hard coded the string.

When I try and run the SQL statement:

$sql = "SELECT *, MATCH (title, description, location) AGAINST ('web designer') FROM job";

I get the following error:

Can't find FULLTEXT index matching the column list

Does anyone have any ideas????

goldbug

Obvious, but did you actually set up fulltext indexes on the target column(s)?

furious5

Erm no!

I didn't realise you needed to do that. Is it simply a case of setting Fulltext up on the column and it should work??

Any links to good tuts on this subject???

goldbug

The manual's a good place to start:

Fulltext overview:
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Fulltext_Search

Creating indexes:
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#CREATE_INDEX

Keeping in mind the last part of that section:

FULLTEXT indexes can index only CHAR, VARCHAR, and TEXT columns, and only in MyISAM tables. FULLTEXT indexes are available in MySQL Version 3.23.23 and later.

furious5

Goldbug, thanks for the reply and the links.

I've now succesfully created an index and the search is working.
Unfortunately it is now returning all the records from the table I am querying.

I know there are only 4 records with 'web designer' anywhere in the record contained within the job table.

Does anyone have any ideas why it is returning all the records within the table??

goldbug

If I remember correctly, using fulltext searches is a non-absolute search. Try searching for just 'web' or 'designer', and see if those results match your original query. (It's giving any matches, and the relevance)

If you want a more absolute search, try using:

... WHERE foo LIKE '%web design%' ...

in your query, 'foo' being the name of whatever column you want to search, obviously.

FULLTEXT examples from the manual:

And here are some examples:

apple banana
find rows that contain at least one of these words.
+apple +juice
... both words.
+apple macintosh
... word apple'', but rank it higher if it also containmacintosh''.
+apple -macintosh
... word apple'' but notmacintosh''.
+apple +(>turnover <strudel)
... apple'' andturnover'', or apple'' andstrudel'' (in any order), but rank apple pie'' higher thanapple strudel''.
apple*
... apple'',apples'', applesauce'', andapplet''.
"some words"
... some words of wisdom'', but notsome noise words''.

furious5

Doesn't the examples you've listed relate to the boolean mode, or is that the boolean mode??

goldbug

Yes, but the first one applies whenever (aka, it's just like your query)

Feel free to go and read the whole manual section on Fulltext indexes linked above. It's quite informative.

furious5

Thanks Goldbug

I'm just about to start reading through the mySQL manual on fulltext, I've only quickly skimped through it to try and answer my initial problem.

I may be back!! :rolleyes:

furious5

Right back again.

I'm just trying to run a search using the boolean mode but so far have failed.

Any ideas?

Here is the SQL I am trying to run:

$sql = "SELECT * FROM job WHERE MATCH (title, description, location) AGAINST ('$keywords' IN BOOLEAN MODE)";

I am getting the following error returned, when I try to run the query: You have an error in your SQL syntax near 'BOOLEAN MODE)' at line 1

goldbug

I admit, I'm stumped. Your query looks perfectly valid to me. You've got nearly the same query as in the manual (save the ending semicolon). Maybe someone else sees something wrong... I don't right now.

goldbug

Wait a sec! What version of MySQL is your server running?

From manual:

As of Version 4.0.1, MySQL can also perform boolean full-text searches using the IN BOOLEAN MODE modifier.

If your version is < 4.0.1, that would explain the error. (not supported)

furious5

Yes I also noticed that when re-reading through the text.

My web hosts are only running version 3.23.54 - hence the error.

Thanks for your time goldbug.

goldbug

No problem.

Drakla

Wow, once again I have exactly the same problem as somebody - but I've just solved it. I was going to ask why this fulltext query wasn't working:

SELECT threadid, subject, author,
SUM(match(subject,data) AGAINST('$subject' IN BOOLEAN MODE)) as Relevance
FROM posts 
WHERE match(subject, data) AGAINST('$subject' IN BOOLEAN MODE)
GROUP BY threadid
ORDER BY Relevance DESC";

If I run it with say "Fish cake" it works fine - run it with "Play Doh" and it doesn't work at all... doesn't even FIND "Doh" on its own.

Turns out that there's a minimum word length of four letters (with the config I'm running straight out of the box). Run a query like "Web Designer" then run "Designer" and see if the result set is exactly the same - if so that's your problem.