Weighting a MySQL Query

[AcidReignX]

I'm trying to create a search engine for my site, but I can't seem to find any MYSQL options for weighting the queries. And I'm almost positive that I had found them a couple months ago. Are they only available in certain versions? Basically what I need is a clone of an engine like they have on the mysql site, though a clone of google, yahoo, msn, etc... might be easier since more people are familiar with the context. I'm tried to use Regular Expressions to get the issue resolved, but unfortunately it will return a lot of queries that I'm not interested in receiving. So my question is how do I forgo this problem?

I've probably started out the wrong way, so smack me in the face if this looks completely wrong... at any rate, this is what I have (essentially):

<code>
<?php

// [CHANGE] rain forest "tropical birds" [TO] rain|forest|"tropical|birds" [FOR REGEXP]
$words = str_replace("|", " ", trim($_GET['query']));

$query = "SELECT * FROM engine WHERE hotkeys REGEXP '(" . $words . ")' OR description REGEXP '(" . $words . ")' ORDER BY rank";
$result = @mysql_query($query);

while($fetch = @mysql_fetch_array($result, MYSQL_ASSOC))
{
// Output the line
echo "
<div class='show_query'>
<div class='query_title'>" . $fetch['title'] . "</div>
<div class='query_description'>" . $fetch['description'] . "</div>
</div>";
}

?>
</code>

I realize that double quotes in my search are completely useless as of right now, too, but I wanted to point those out. My goal is to have the output that is MOST RELATED to the user's input to be listed. The "order by" function here is pretty much useless, because the query is still going to output anything with the word "rain" in it, even if it is entirely unrelated to the rain forest. Hence the need for stuff like quotes and matching it with something like "forest". If there is some sort of command or function available for mysql that would allow such weighting, that in particular is what I'm looking for. However, if anybody here is familiar with making an engine like this, that would ideal =) because I could sure use some pointers.

Thanks in advance =)

 

[TonyGroves]

What you are looking is "full-text searching". You use a TEXT-type field, put a FULLTEXT index on it, and then use the MATCH function to return a relevance ranking for a term in a page. For example:[tt]
SELECT * FROM tbl
ORDER BY MATCH(txtfld) AGAINST("word")[/tt]

See

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

 

[AcidReignX]

Ahhh! Fantastic =D Yes, this is exactly what I was looking for. I had glanced over it in the previous versions today, but didn't catch the importance. This version's explanation, 5.1, seems to be exactly what I'm looking for. However, I'm wondering if there is any way to add your own parameter to the search from a relevent-matching field on the table? For example:

SELECT title, body, rank MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE) AS score FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE) ORDER BY (score * rank) DESC;

Would that work? In other words, would the relevency be affected by the number I specify with the "rank" field? So if I had set "rank" to 10 or 5, would that impact it? Or is there a way to, and I just messed up the command?

 

[TonyGroves]

MATCH returns a numeric value indicating the relevance, so you can use it in any situation where an expression can be used. In your example I presume you meant to put a comma after the "rank" word; if "rank" is a field in the table then the query should work.