How to order mysql search results by relevence of word priority

Question

Possible Duplicate:
PHP MySQL Search And Order By Relevancy

Hi,

I have a table with several columns containing fields like name, address, company etc. Lets say someone search for "microsoft john". I want the results containing "microsoft" should appear first, then results containing john. vice versa if query is "john microsoft"

My php code is:

$searchitems=explode(" ", $trimmed);
//print_r($searchitems);
$so = $_GET['so']=='2'?"2":"1";

$clause = $so=='2'?"AND":"OR";
include("dbconnect.php");
// Build SQL Query

$query = "select FirstName,LastName,course,Department,batch,City,companyjob,companylocation,
    companyposition,coursename,institutename,coursename2,institutename2,coursename3,
    institutename3 from alumni WHERE ";
for($i=0;$i<count($searchitems);$i++)
{
    $queryappend .= "(FirstName LIKE '".$searchitems[$i]."%' OR LastName LIKE '".$searchitems[$i]."%'
    OR City LIKE '".$searchitems[$i]."%' OR CountryorRegion LIKE '".$searchitems[$i]."%'
    OR companyjob LIKE '".$searchitems[$i]."%' OR companylocation LIKE '".$searchitems[$i]."%'
    OR coursename LIKE '".$searchitems[$i]."%' OR institutename LIKE '".$searchitems[$i]."%'
    OR coursename2 LIKE '".$searchitems[$i]."%' OR institutename2 LIKE '".$searchitems[$i]."%')";
    if($i<count($searchitems)-1) $queryappend .= $clause;

}
$query .=$queryappend;

The problem is MYSQL is ordering the results by id... This makes it funny, because some higher valued results may be stuck deep in the stack. btw, phpmyadmin search has the same flaw.

Please suggest.

Answer 1

As an example:

SELECT
  FirstName,
  LastName,
  IF (FirstName LIKE '%Microsoft%' || LastName LIKE '%Microsoft%', 1, 0) AS One,
  IF (FirstName LIKE '%John%' || LastName LIKE '%John%', 1, 0) AS Two
FROM alumni
ORDER BY One DESC, Two DESC

In your code, this will make the query pretty complicated. The advantage is, that items with both search term appear before items that match only a single search term.

An alternative is sorting the records into buckets while retrieving them using PHP. Assuming you have the search terms in an array $search (ordered by descending priority):

while ($record = mysql_fetch_array($result))
{
  $total = join(' ', $record);
  $found = false;
  foreach ($search as $term)
  {
    if (strpos($total, $term) !== false)
    {
      $buckets[$term][] = $record;
      $found = true;
      break;
    }
  }
  if (!$found)
  {
    $results[] = $record;
  }
}
foreach (array_reverse($search) as $term)
{
  if (isset($buckets[$term]))
  {
    $result = array_merge($buckets[$term], $result);
  }
}

Now you have the results in array $results. Note that this demonstrates the algorithm, it it not tuned for performance.

Answer 2

I would think the simplest way to solve it would be sorting the results by the levenstein distance.

Something like....

$queryappend="ORDER BY
   length(firstname) - levenshtein(FirstName, '".$searchitems[$i]."') +
   length(lastname)  - levenstein(LastName, '".$searchitems[$i]."')   +
   length(City)      - levenstein(City, '".$searchitems[$i]."')       +
   ...

Although it might be a good idea to use a schema MORE SUITED to this kind of searching