order of relevance?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
harrisonad
Forum Contributor
Posts: 288
Joined: Fri Oct 15, 2004 4:58 am
Location: Philippines
Contact:

order of relevance?

Post 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.
Last edited by harrisonad on Mon Aug 01, 2005 10:33 pm, edited 3 times in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
harrisonad
Forum Contributor
Posts: 288
Joined: Fri Oct 15, 2004 4:58 am
Location: Philippines
Contact:

Post by harrisonad »

You mean like putting the results in an array and sorting it from there?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

yes........
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

You can also perform a fulltext search and order on the score..
Post Reply