SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Member rodin7's Avatar
    Join Date
    Mar 2004
    Location
    ohio
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Changing order by sorting

    I have a database I need to sort but I am not real sure on how to do it.

    I have a table
    menu
    -- Columns --
    id
    name
    description
    price
    cat

    What i need to do is when building a dymanic page be able to sort this into a different order.

    so i might need
    record 5 to be #1
    record 1 to be #2
    record 2 to be #3
    record 3 to be #4
    record 4 to be #5

    I was thinking that I should make another table called menu_order
    --columns --
    order_id -- this would be the order the records go in
    menu_id -- this would be the id number from the menu table

    Do i have to make it so that it rewrites the entire menu_id column each time there is a change?

    any ideas if this is correct?

    rodin

  2. #2
    SitePoint Wizard bronze trophy JRMillion's Avatar
    Join Date
    Apr 2004
    Location
    Arlington VA
    Posts
    2,094
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well you need some value by which to sort... however you go about it.
    PHP has a function called usort() that lets you sort an array using a custom comparison you define yourself.

    Either way, you need some value (or same way) to determine how it should be sorted. you could use another table to do it, or just have a sortkey in each row and modify them when needed to be in a certain order. You should only do that if there is no other logical way to sort them.

  3. #3
    SitePoint Member rodin7's Avatar
    Join Date
    Mar 2004
    Location
    ohio
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by JRMillion
    well you need some value by which to sort... however you go about it.
    PHP has a function called usort() that lets you sort an array using a custom comparison you define yourself.

    Either way, you need some value (or same way) to determine how it should be sorted. you could use another table to do it, or just have a sortkey in each row and modify them when needed to be in a certain order. You should only do that if there is no other logical way to sort them.
    I figured that order_id would be my sort key.
    I then would use menu_id to put in the id field from the menu table?
    so then would menu_id be my primary key and i would just update the order_id when the order changes? Can I make order_id unique if it is not the primary key?

    rodin

  4. #4
    SitePoint Wizard bronze trophy JRMillion's Avatar
    Join Date
    Apr 2004
    Location
    Arlington VA
    Posts
    2,094
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the primary key can be an field that is unique, but you can have other unique fields if need be. For your specific question, you lost me a little bit, i am not sure what the goal is.

  5. #5
    SitePoint Member rodin7's Avatar
    Join Date
    Mar 2004
    Location
    ohio
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by JRMillion
    the primary key can be an field that is unique, but you can have other unique fields if need be. For your specific question, you lost me a little bit, i am not sure what the goal is.
    Sorry let me try again

    I have my menu table
    I need to be able to change the order of my menu table when the page is built by php

    so i can change
    record = order
    5 = 1
    2 = 2
    4 = 3
    1 = 4
    3 = 5

    and also be able to change this order anytime.

    I figured i would need another table to do it

    so then I have

    order_menu

    2 columns in this table

    1 - id_order -- this would be the sort key so it would be in order
    2 - id_menu -- this would be the id from the menu table to tell what record goes with what order

    does that seem right?
    and do i have to rewrite the entire id_menu column each time the order is changed?

    thanks
    rodin

  6. #6
    SitePoint Wizard bronze trophy JRMillion's Avatar
    Join Date
    Apr 2004
    Location
    Arlington VA
    Posts
    2,094
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well you can do it either way, you could just add an extra key to the main table to use for sorting and just change the values whenever you need to....
    or you could use another table to determine the ordering.
    Either way, you perform and similar function when you want to change the way it orders.

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll second JRMillion's suggestion on adding another field...just call it "SortOrder"...that's how I deal w/situations like this...esp when you never know how dynamic that list might need to be, by client specs or otherwise. When you pull the rs into your PHP you can just order it by the 'SortOrder' field and there you go (I'm not familiar w/PHP but I figure that's possible, no?).

  8. #8
    SitePoint Member rodin7's Avatar
    Join Date
    Mar 2004
    Location
    ohio
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by null
    I'll second JRMillion's suggestion on adding another field...just call it "SortOrder"...that's how I deal w/situations like this...esp when you never know how dynamic that list might need to be, by client specs or otherwise. When you pull the rs into your PHP you can just order it by the 'SortOrder' field and there you go (I'm not familiar w/PHP but I figure that's possible, no?).
    Yeah I agree and just about have that figured out but i found another issue

    I have different categories for al the records also so I need to order them based on each category also so;

    salads
    id order
    2 1
    3 2
    1 3

    main
    id order
    2 1
    1 2
    3 3

    so I don't know how this is then ordered individual tables for the categories?

  9. #9
    SitePoint Wizard bronze trophy JRMillion's Avatar
    Join Date
    Apr 2004
    Location
    Arlington VA
    Posts
    2,094
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It seems like its a restuarant menu...
    make a PHP script that does the admin using a form, that way you dont have to keep track of the whole thing in your head.
    you can have categories, and items.
    each item can have a catid to show what type of item they are, salad main course , etc....
    then each item can have a orderid that would determine how its sorted.
    You can use a php form to let an admin user move items up and down in the order listing.

  10. #10
    SitePoint Member rodin7's Avatar
    Join Date
    Mar 2004
    Location
    ohio
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by JRMillion
    It seems like its a restuarant menu...
    make a PHP script that does the admin using a form, that way you dont have to keep track of the whole thing in your head.
    you can have categories, and items.
    each item can have a catid to show what type of item they are, salad main course , etc....
    then each item can have a orderid that would determine how its sorted.
    You can use a php form to let an admin user move items up and down in the order listing.
    Exactly that is what i have
    I am building a restaurant content management system. I am hoping that this will give me a better handle on php and mysql and so far it is really working. This is the last thing I have to do before I start working on the display data for the front end.

    I was concerned about changing the order by moving up and down. Would i just make the movement up and down exchange the orderid between the 2 records just switched? Can that be done? I know how to increment up without a problem but i am not sure about saying;
    record 5 had an orderid 12
    record 27 had an orderid 13

    record 5 has an orderid 13
    record 27 has an orderid 12

    ???
    rodin

  11. #11
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, in your 'menu' table, you have a category field. Do you have a table that holds all the categories?? If so I'd create a field in there as well, maybe 'CatSortOrder', that way w/each category you have a sort order. Then when you're @ the item level, you can just order those items by the 'sortOrder' field you've previously created.

  12. #12
    SitePoint Member rodin7's Avatar
    Join Date
    Mar 2004
    Location
    ohio
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok
    I have my tables down but I am a little unsure on this query.

    I need to be able to update the menu table and the meny_order table at the same time.

    Code:
    INSERT into menu (id, name, description, price, cat) VALUES 
    (NULL $name', '$description', '$price', '$cat') 
    INSERT INTO order_menu (menu_id, order_id) VALUES 
    (LAST_INSERT_ID() NULL);
    I know that isn't quite right but I am not sure how wrong it is
    I am trying to just make sure the menu_id is the same as the id column in the menu table. I will use another query to change the sort order as need be

    Rodin


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
  •