CODEWITHSUNDEEP
phpmysqlsearch
Jac Engels
Jac Engels

Reputation: 83

Specify rank in ranked search query PHP, MySQL

I've got a search query witch sorts the results based on how many times the term is used in an article. For example 1 time in the title and 1 time in the content. This one will end higher up in the list than a article with te term only in it's content area. But I want a little extra, is it possible to add multipliers, for example when the term is used in the title then it counts for 2 instead of one. Look at the code below:

<?PHP

//SET THE SEARCH TERM
$term = "Search Term";

$sql = "SELECT *, MATCH(subject, message,reference,textbody) AGAINST('". $term ."') as score FROM pages WHERE MATCH (subject, message,reference,textbody) AGAINST('". $term ."') ORDER BY score DESC";
$query = mysql_query($sql);

//BUILD A LIST OF THE RESULTS
while($result = mysql_fetch_assoc($query)) {
   // your stuff for showing result
}
?>

Could you please help me out?

Upvotes: 2

Views: 1323

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562921

MySQL's MATCH function does not have a feature for weighting the fields. You would have to create a separate fulltext index on the field you want to have higher weight, and then calculate the relevance against both fulltext indexes with separate MATCH() expressions.

SELECT *, 
  MATCH(subject) AGAINST('". $term ."') * 2 AS score_s /* 2x weight */
  MATCH(subject, message, reference, textbody) AGAINST('". $term ."') AS score
FROM pages 
WHERE 
  MATCH(subject, message, reference, textbody) AGAINST('". $term ."') 
ORDER BY score_s + score DESC

MySQL's builtin fulltext search is really not very flexible. Other specialized tools are often a better choice when you have specific functional requirements:

Those other tools are frequently much faster for searching large datasets, too. See my presentation Fulltext Search Throwdown for comparisons.

Upvotes: 1

echo_Me
echo_Me

Reputation: 37243

just add +1 to it like that

$sql = "SELECT *, MATCH(subject, message,reference,textbody) AGAINST('". $term ."')+1 as score 
             FROM pages  ORDER BY score DESC";

Upvotes: 1

Related Questions