MySQL FullText search ??

Question

I am working on search functionality in my website. I want to provide a best search mechanism to users. I am using PHP/MYSQL.

Let's say user searched for "sport".

I have 30 records having word "sport" and 5 records having word "sports" but when I am searching for sport then following query only returns the result 30 who have the word "sport". But actually I think the best way to provide good search result is to display all the 30+5 records, all the records having sport or sports.

SELECT DISTINCT p.id, p.title, p.descr, p.tags FROM pdata p WHERE MATCH (p.title, p.tags, p.descr) AGAINST ('sport')

Please tell me some articles or some tips & tricks using which I'll be able to provide a good search functionality.

Answer 1

...MATCH (p.title, p.tags, p.descr) AGAINST ('sport*' IN BOOLEAN MODE)

May do the trick.

Edit, the MySQL documentation is an excellent resource for these kind of problems! :)

Answer 2

Just use the % wildcard on your search strings. This will allow any words formed by the search string to be matched. Sadly you will need to be in Boolean mode for this wildcard to work. Also all search strings must be greater then 3 characters in order to show up using full text search. this can be changed in the mysql system settings, but the default is 3.

Answer 3

In your case I would make the following query:

SELECT p.id, p.title, p.descr, p.tags FROM pdata p WHERE (p.title LIKE '%SPORT%' OR p.tags LIKE '%SPORT%' OR p.descr LIKE'%SPORT%')

This query would find any articles which cover sportmagazines , summer-sports ect ect.