Re: [PHP] scoring/sorting db search results based on score

2003-07-21 Thread Marek Kilimajer
I use mysql, so this was for mysql - LIKE is case insesitive and returns 0 for false and 1 for true. Dean E. Weimer wrote: Try SELECT , (((keywords LIKE '%$search%') * 5) + ((title LIKE '%$search%') * 3) + (description LIKE '%$search%')) score FROM . ORDER BY score DESC PostgreSQL

Re: [PHP] scoring/sorting db search results based on score

2003-07-21 Thread Marek Kilimajer
I don't know how postgres server would optimize the query, but in teory there should be only a slight overhead as the (I)LIKE results should be cached. How much takes to order by depends on the number of rows returned. Dave [Hawk-Systems] wrote: Appreciate the responses... We are talking a db

Re: [PHP] scoring/sorting db search results based on score

2003-07-17 Thread Marek Kilimajer
Try SELECT , (((keywords LIKE '%$search%') * 5) + ((title LIKE '%$search%') * 3) + (description LIKE '%$search%')) 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

Re: [PHP] scoring/sorting db search results based on score

2003-07-17 Thread Dean E. Weimer
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

RE: [PHP] scoring/sorting db search results based on score

2003-07-17 Thread Dave [Hawk-Systems]
Appreciate the responses... 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