SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Making Products have a Display Order or Sort Order?

    Hello everyone,

    I have put together a product catalog but i'm having trouble with the customization of the display order of the products.

    I want the administrator to be able to change the order in which the products are displayed on the page. I have a display_order field in my database table for products but it won't let me auto_increment since I already have a primary key.

    Has anyone dealt with this issue, are there any recommendations how to make the order of display customizable?

    Should I create a new table in the database just for display order of the products. Also the products are in different categories, so there may be repeats of display_order number in the table because the order is for different categories.

    Any help is much appreciated to say the least!!!!

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    Table : Products
    ----------------
    ProductID
    ProductName
    ...
    
    Table : Categories
    -----------------
    CategoryID
    CategoryName
    
    Table : ProductCategory
    -----------------------
    ProductID
    CategoryID
    DisplayOrder
    No duplicate values. Display order is set per category. You need to manage the values yourself - auto_increment isn't appropriate for this.

  3. #3
    SitePoint Evangelist priti's Avatar
    Join Date
    Aug 2006
    Location
    India
    Posts
    488
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Parlay View Post
    Hello everyone,

    I have put together a product catalog but i'm having trouble with the customization of the display order of the products.

    I want the administrator to be able to change the order in which the products are displayed on the page. I have a display_order field in my database table for products but it won't let me auto_increment since I already have a primary key.

    Has anyone dealt with this issue, are there any recommendations how to make the order of display customizable?

    Should I create a new table in the database just for display order of the products. Also the products are in different categories, so there may be repeats of display_order number in the table because the order is for different categories.

    Any help is much appreciated to say the least!!!!
    Hello,

    Product can be displayed on some field like category wise,alphabetic order ,date of creation,date of update,or some feature which your product have you don't need to maintain a separate field to order your products.Every product is have unique id(pk) and if u are maintaing some field just to order your product then why u want them autoincrement?????

    u must be having a field name cdate(create-date: when u created that product)or udate(update date:when u tried to update your produc for detail or any other attribute) use them for ordering if u want product added latest or so

    hope it helps
    regards

  4. #4
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Quote Originally Posted by Dan Grossman View Post
    Code:
    Table : Products
    ----------------
    ProductID
    ProductName
    ...
    
    Table : Categories
    -----------------
    CategoryID
    CategoryName
    
    Table : ProductCategory
    -----------------------
    ProductID
    CategoryID
    DisplayOrder
    No duplicate values. Display order is set per category. You need to manage the values yourself - auto_increment isn't appropriate for this.
    Thanks for your help. I think I'm just gonna include the CategoryID and DisplayOrder fields in the Products Table instead of creating a new ProductCategory table. What do you think?

    The tough part comes in when I allow the catalog administrator to edit the products and be able to put them into different categories and also delete them. Both factors start to mess with the way DisplayOrder is kept track of. So I would have to write a lot of functions and SQL statements to reorganize the display order whenever a products is moved to a different category or deleted. I have been coming up with the code for this. Do you guys have any recommendations or have you seen code for this kind of thing somewhere? Thanks again.

  5. #5
    SitePoint Wizard bronze trophy devbanana's Avatar
    Join Date
    Apr 2006
    Location
    Pennsylvania
    Posts
    1,736
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with Dan. No you should not merge the tables as you mentioned; that would be very bad. Having a structure as shown in Dan's posts let's you have a many-to-many relationship between products and categories, which means categories may have many products and products may have many categories.

    To find a new display order, I'd say just do something like:

    Code:
    select max(display_order) + 1 from categories_products where category_id = %d
    Where %d is the ID of the category the product is being added to. That's untested of course but, seems like it should work.
    Laudetur Iesus Christus!
    Christ's Little Flock
    Jesus is the Good Shepherd

  6. #6
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Quote Originally Posted by devbanana View Post
    Code:
    select max(display_order) + 1 from categories_products where category_id = %d
    Where %d is the ID of the category the product is being added to. That's untested of course but, seems like it should work.
    Excellent, thank you, I will do that. But my problem arises when someone deletes a product or switches its category.

    My solution is to go through, the 'display_order' field and renumber each field using a PHP loop and SQL statements but it seems inneficient. If there are 20 records, it will take 20 SQL statements (yes they will be short statements) but is that the best way to do it? Is there a SQL function that reorders the numbers in the display_order, so that if the product in the #2 display_order is deleted or moved, the products in the #3,#4,#5, etc..... are all numbered down to fill in the that #2 spot gap. Any suggestions?

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    When someone deletes/moves a product, make note of its display order, then issue a single UPDATE:

    Code:
    UPDATE categories_products 
    SET display_order = display_order - 1 
    WHERE display_order > $deleted_item_order
    No loops necessary.

  8. #8
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Quote Originally Posted by Dan Grossman View Post

    No loops necessary.
    You bring honor back to the name Grossman. I thank you for this UPDATE query method, I did not know it was possible. Much appreciated my friend. I now understand why you have all those medals of honor near your name.

    One other thing, no biggie if you don't respond, you recommended putting display_order field in its own table, but I can see how it can work being part of the products table. Is this bad practice? Should I put the display_order in it's own table to follow normalization rules?

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you don't have a separate table linking products to categories, you can't have a product in multiple categories. That's a requirement of most online stores, so that was the right place to put the display order.

    If every product is in only one category, and you don't think that'll ever change, you can put the category ID and display order in the products table.

  10. #10
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    If you don't have a separate table linking products to categories, you can't have a product in multiple categories. That's a requirement of most online stores, so that was the right place to put the display order.

    If every product is in only one category, and you don't think that'll ever change, you can put the category ID and display order in the products table.
    Thanks again man, seriously... this PHP forum is unreal.


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
  •