Page 1 of 1

order of relevance?

Posted: Mon Aug 01, 2005 10:24 pm
by harrisonad
Hi, I am doing a search script for people names (lname,fname). I would search the database with this built query

Code: Select all

$toSearch = 'Harrison Dano';
$conditions = array();
$aray = explode(' ',$toSearch); 
foreach($aray as $keyword){
    $conditions[] = "lname like '$keyword%' OR fname LIKE '$keyword%'";
} 
$query = "SELECT  lname,fname FROM directory WHERE ".implode(' OR ',$conditions);
/* the query must be ...
    SELECT lname,fname FROM directory 
    WHERE lname LIKE 'Harrison%' OR fname LIKE 'Harrison%' OR
    lname LIKE 'Dano%' OR fname LIKE 'Dano%'
*/
When it is executed it will give the list of names that matches the begining of either lastname or firstname.

Code: Select all

George Harrison
Harrison Ford
Emanuel Danobelle
Harrison Dano <-- The exact match is sadly not the first item
Now I want to sort the result so that the closest match is at the begining of the list. Do I have to add ORDER BY statement? If yes, how?

Thank you in advance.

Posted: Mon Aug 01, 2005 10:28 pm
by feyd
yes, you need an order by, typically.. but you have to figure out a definition of "closest match" that the query can understand.. otherwise, you need to shift the sorting to php after retrieving all matches..

Posted: Mon Aug 01, 2005 10:31 pm
by harrisonad
You mean like putting the results in an array and sorting it from there?

Posted: Mon Aug 01, 2005 10:47 pm
by feyd
yes........

Posted: Tue Aug 02, 2005 5:34 pm
by timvw
You can also perform a fulltext search and order on the score..