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
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%') )
order by
pd.products_name
union
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_description like '%test%'
order by
pd.products_name
Split into two queries, first removes the description search, and the second only looks at the description. The union will take care of placing the description search at the end.This will slow your result significantly though. Perhaps allow the user to select whether they want to search the description before hand?
EDIT: Also, you should define your relationships during the joins, not in the where criteria.
a UNION query can have only one ORDER BY clause, and it applies to the entire result set
trying to put separate ORDER BY clauses into each subselect might eventually work in mysql, because heck, mysql doesn’t observe sql standards in many areas, and this might be one of them
but you sabotage these efforts by using UNION instead of UNION ALL
UNION implicitly sorts the entire result set, in order to search for duplicate rows, which it removes
a better strategy is to include an additional “sort key” column, and give it a value of 1 for the first subselect, and 2 for the second (or any other values, like ‘humpty’ and ‘dumpty’), and make that the first column in the single ORDER BY clause