Sorting by column weight

[Chran]

Hi everybody,

I am working on a search function for an e-commerce site and I just cant figure this one out. It is probably not that hard but I am stuck.

The search is searching three columns in a table (category, title and description) for matches in each column. Each column should now weigh differently (1. category, 2. title and 3. description) and I want the results to be ordered with hits in category on top, then title hits and last description hits. Then I want to group the hits into categories and present them category by category.

Is there a way to accomplish this in one single query?

This is what I got so far:

SELECT category FROM 
        (SELECT category, title, descr, count(*) AS hits FROM csvdata_searchtest WHERE title LIKE '%$searchword%' || descr LIKE '%$searchword%' GROUP BY category ORDER BY hits DESC LIMIT 0,50)
    csvdata_searchtest WHERE category LIKE '%$searchword%' || title LIKE '%$searchword%' || descr LIKE '%$searchword%'

Right now I am counting the hits in each category to sort the relevance and works fine but should come secondary after the column weight.

Any ideas?