SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Order By Help

    Hi Guys

    I want to order by a Featured List of product ids - which is passed from PHP. So firstly it orders by the featured list, then it falls back and orders by price. I have tried the following, but it seems to pull the featured ones last, not first.
    Code MySQL:
    ...ORDER BY ( CASE p2.product_id WHEN 4 THEN 0 WHEN 33 THEN 1 WHEN 22 THEN 2 WHEN 20 THEN 3 WHEN 16 THEN 4 WHEN 27 THEN 5 END ) ASC, pa1.price ASC

    So ideally the order would be:
    1) product_id = 4 (if it is found by the query)
    2) product_id = 33 (if it is found by the query)
    3) product_id = 22 (if it is found by the query)
    4) product_id = 20 (if it is found by the query)
    4) product_id = 16 (if it is found by the query)
    5) product_id = 27 (if it is found by the query)
    6) Everything else that is found by the query, in price ASC order.

    Hope that makes sense.

    Thanks

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Add a ELSE 9999 to the CASE:
    Code:
    ORDER BY 
        (CASE p2.product_id 
           WHEN 4 THEN 0 
           WHEN 33 THEN 1 
           WHEN 22 THEN 2 
           WHEN 20 THEN 3 
           WHEN 16 THEN 4 
           WHEN 27 THEN 5 
           ELSE 9999
        END ) ASC
      , pa1.price ASC

  3. #3
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I also tried the following with no love:

    ORDER BY FIELD(p2.product_id,4,33,22,20,16,27) ASC, pa1.price ASC

  4. #4
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Add a ELSE 9999 to the CASE:
    Code:
    ORDER BY 
        (CASE p2.product_id 
           WHEN 4 THEN 0 
           WHEN 33 THEN 1 
           WHEN 22 THEN 2 
           WHEN 20 THEN 3 
           WHEN 16 THEN 4 
           WHEN 27 THEN 5 
           ELSE 9999
        END ) ASC
      , pa1.price ASC
    Thanks much from Australia!


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
  •