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?

select distinct
p.products_image,
m.manufacturers_name,
m.manufacturers_id,
p.products_id,
pd.products_name,
p.products_price,
p.products_tax_class_id,
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
from
products p left join manufacturers m using(manufacturers_id)
left join
specials s on p.products_id = s.products_id,
products_description pd,
categories c,
products_to_categories p2c
where
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%') )
order by
pd.products_name