Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    389
    Rep Power
    76

    Custom order list of items in multiple categories


    I am trying to find a way to create a custom order of a list of products. I was able manually order them using a column named prod_order. Then assigned each product a number. In my SQL I just order the list that is returned by order number.

    Then I realized that some products appear under multiple categories. So if a product is number "3" in the order it may not be "3" in the list of another category. How is this done?
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,115
    Rep Power
    9398
    Store the sort order in the product-category association table instead/too.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    389
    Rep Power
    76
    Ok that makes sense. I have this query that I tested in PHPMyAdmin. I can't figure how to pull 'prod_order' from the category_assoc table.

    Code:
    SELECT `pr`.`prod_description` , `pr`.`prod_id` , `pr`.`prod_name` , `pr`.`prod_mainImage` , `pr`.`prod_model`
    FROM products AS pr
    LEFT JOIN category_assoc AS assoc ON assoc.prod_id = pr.prod_id
    WHERE assoc.cat_id = "4"
    ORDER BY `prod_order`
  6. #4
  7. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,115
    Rep Power
    9398
    Code:
    ORDER BY category_assoc.prod_order
    Unless I missed the question?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    389
    Rep Power
    76
    That gives me: Unknown column 'category_assoc.prod_order' in 'order clause'

    I moved the prod_order to the category_assoc table. When I run the query I get description, prod id, name, image and model. I need to have another select statement to get and show the 'prod_order' column.

    I tried using UNION but the returned rows were not equal.
  10. #6
  11. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,115
    Rep Power
    9398
    What's the structure of your category_assoc and product tables?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    389
    Rep Power
    76
    category_assoc:
    cat_assoc_id, prod_id, cat_id, prod_order

    products:
    prod_id, prod_description, prod_name, prod_mainimage, prod_model
  14. #8
  15. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,115
    Rep Power
    9398
    Ah, didn't notice the alias.
    Code:
    ORDER BY assoc.prod_order
    but I'm pretty sure you could have realized that.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    389
    Rep Power
    76
    PHP Code:
    SELECT `pr`.`prod_description` , `pr`.`prod_id` , `pr`.`prod_name` , `pr`.`prod_mainImage` , `pr`.`prod_model`
    FROM products AS pr
    LEFT JOIN category_assoc 
    AS assoc ON assoc.prod_id pr.prod_id
    WHERE assoc
    .cat_id "4"
    ORDER BY `assoc.prod_order
    I get: #1054 - Unknown column 'assoc.prod_order' in 'order clause'

    Seems like a need to select the prod_order from the assoc table. And use a UNION?
  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by mallen
    Seems like a need to select the prod_order from the assoc table.
    No, you stumbled upon those stupid backticks, and you've proven yet again that we shouldn't use them.

    Take a close look:

    sql Code:
    `assoc.prod_order`

    This is a single identifier with a dot inside. It's not a column name with a table name in front. That would be

    sql Code:
    `assoc`.`prod_order`

    You're person No. 500,000 to make this mistake, so learn from it and erase the terrible backticks from your code.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    389
    Rep Power
    76
    Thanks that worked. But I need the query to show "prod_order" column also. It was originally in the products table and now its in category_assoc table. So I need to select it from there.
  22. #12
  23. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,115
    Rep Power
    9398
    So add it.
    Last edited by requinix; September 23rd, 2013 at 01:50 PM.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    389
    Rep Power
    76
    PHP Code:
    SELECT `pr`.`prod_description` , `pr`.`prod_id` , `pr`.`prod_name` , `pr`.`prod_mainImage` , `pr`.`prod_model`
    FROM products AS pr
    SELECT 
    `prod_order
    FROM category_assoc
    LEFT JOIN category_assoc 
    AS assoc ON assoc.prod_id pr.prod_id
    WHERE assoc
    .cat_id "4" 
    ORDER BY `assoc`.`prod_order`
    LIMIT 0 30 
    I don't understand how to combine two "SELECT" statements
  26. #14
  27. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    First of all, I just told you to remove the stupid backticks. When you make a mistake, shouldn't you learn from it and try to avoid it rather than making sure you repeat it next week?

    Secondly, I have no idea what you're doing with those "two SELECT statements". Isn't your "prod_order" supposed to be just another column in the result set? Then simply add it to the current SELECT clause.

    Comments on this post

    • paulh1983 agrees : furthermore if you do not know how to add a column to be selected, you need to go back to SQL basics.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    389
    Rep Power
    76
    Post #11:
    "It was originally in the products table and now its in category_assoc table. So I need to select it from there"
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo