MYSQL Query Sorting Question
We have a site search routine on our website that searches the product name, product description, part number, and manufacturer fields for matches. It works well, but we have a number of issues where a whole product line mentions another related product in their descriptions. When someone searches for that related product, it returns all the products that mentioned that product and makes the results frustrating to wade through. We'd like to modify our query so that it returns matches in the product name and part number at the top of the result set and returns matches in the product description at the end of the result set. I've included the search query below. Does anyone know how to modify the query to accomplish this?
IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price) as final_price
products p left join manufacturers m using(manufacturers_id)
specials s on p.products_id = s.products_id,
p.products_status = '1'
and p.products_id = pd.products_id
and pd.language_id = '1'
and p.products_id = p2c.products_id
and p2c.categories_id = c.categories_id
and ((pd.products_name like '%test%' or p.products_model like '%test%' or m.manufacturers_name like '%test%' or pd.products_description like '%test%') )