Advanced search

[FiRe]

I am trying to create an advanced mysql search. Here is the table:

TABLE `results` (
`id` int(10) NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`tags` varchar(255) NOT NULL default '',
`contents` text NOT NULL,
PRIMARY KEY (`id`)
)

Now a user will enter into the search field a sentence, e.g. "How many cards in a pack?" and it has to search through the title, tags and contents fields for anything relevant. Currently it looks like this:

mysql_query("SELECT * FROM results WHERE title LIKE '%$q%' OR tags LIKE '%$q%' OR contents LIKE '%$q%'");

This is not very effective however, I need it to be more advanced. Can someone help me make a better query? I also want it to be able to sort by relevance (so most relevant first) and (if possible) output its releavnce like 50%, 60% etc...

Thanks :-)

[GoldFish]

All of the functionality you want can be accomplished with MySQL's Full-Text search functions [dev.mysql.com]. Be sure to have a look at the user comments and the IN BOOLEAN MODE modifier as well. Good luck!

[FiRe]

Thanks for that, here is my new code:

$query = mysql_query("SELECT short, title, contents, edited,
MATCH(title, tags, contents)
AGAINST ('$q' IN BOOLEAN MODE) AS score FROM q
WHERE MATCH(title, tags, contents)
AGAINST ('$q' IN BOOLEAN MODE) ORDER BY score DESC");

What is the maximum number it gives for a score? If I searched the exact title it gives me 4% score as opposed to something like 80/90%?

[edited by: FiRe at 4:44 pm (utc) on Oct. 6, 2006]

[GoldFish]

There's no upper limit to the maximum score, it depends on the frequency of the match. You could however make a relative scoring where the highest ranking result equals 100%.

What also might come in handy: you can make different aspects rank differently (e.g. place more weight on a matching title than on matching content). Do this by changing the SELECT part of the query. E.g.:

select short, title, contents, edited, (match(title) against($q in boolean mode)*20) + (match(tags) against($q in boolean mode)*10) + (match(contents) against ($q in boolean mode)*1) as score ...

In this example a matching title will have twice the weight compared to a matching tag and 20 times more weight compared to matching content.

[FiRe]

Ah I see, thanks for the idea :-)