have a table
title, description, keywords
which I am searching (from PHP) using a keyword
What I want to do is sort the results based on the number of hits nd scoring
based on where the hit is. For example, a hit in keywords is worth 5, title is
worth 3, description is worth 1.\
I currently have the following working select;
SELECT *, (
(CASE WHEN (keywords ~* '.*keywordSearch.*') THEN 5 ELSE 0 END) +
(CASE WHEN (title ~* '.*keywordSearch.*') THEN 3 ELSE 0 END) +
(CASE WHEN (description ~* '.*keywordSearch.*') THEN 1 ELSE 0 END)
) AS score FROM catalog_table WHERE
keywords ~* '.*keywordSearch.*' or
title ~* '.*keywordSearch.*' or
description ~* '.*keywordSearch.*'
AND status='D' ORDER BY score DESC
which works great, but the maximum hit is 5 even if a particular item has
multiple hits in several different fields (ie: hit in keyword, title, and
description results in score of 5 instead of score of 8)
1) Any idea on how to rework the query to total the score for all field hits (as
in teh score of 8 we should se from above)?
2) What about multiple hits, for example, two keyword hits, two title hits, and
two description hits totaling a score of 16?
Thanks
Dave