Re: scoring/sorting db search results based on score

> Try > SELECT ...., (((keywords LIKE '%$search%') * 5) + ((title LIKE > '%$search%') * 3) + (description LIKE '%$search%')) score FROM ..... > ORDER BY score DESC PostgreSQL cannot type cast the Boolean type so you have to use a case statement, also changing like to ilike will get results regardless of case. SELECT ...., ((CASE WHEN (keywords ILIKE '%$search%') THEN 5 ELSE 0 END) + (CASE WHEN (title ILIKE '%$search%') THEN 3 ELSE 0 END) + (CASE WHEN (description ILIKE '%$search%') THEN 1 ELSE 0 END)) AS score FROM .... ORDER BY score DESC > > Dave [Hawk-Systems] wrote: >> looking for code snippets or links to examples of the following; >> >> - Have a database with multiple fields that will be searched against >> (happens to >> be PostgreSQL in this instance, but we can migrate any MySQL based >> examples/code) >> - We wish to score search results - ie: a match in "keywords" is worth 5 >> points, >> "title" worth 3, and "description" worth 1, perhaps even so far as >> multiple >> matches producing multiples of the point value(though that can be a >> later >> consideration) >> - Once we get the results, we would want to display in the order of the >> scoring, >> most points first etc... >> >> Obviously there are convoluted ways to accomplish, but I am looking to >> maximize >> the database performance, limit the number of recursive searches, and >> use the >> database/PHP each handle their portion of the search/score/ranking based >> on >> their strengths and use of system resources. >> >> appreciate any feedback >> >> Dave >> >> >> > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Thanks, Dean E. Weimer http://www.dwiemer.org/ dweimer@dweimer.org