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.