SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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?

    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

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    64 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by whitemank View Post
    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
    Code:
    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.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •