Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Saving the order of something

    Hey there,

    the user chooses 0 to n entries in a specified order and i've to save said order of the entries in the user table. My approach of saving the order in a single string (1,5,3,2) worked, but I think that's not exactly considered best practice.

    Table User: (int ID; string username; string order) e.g: "1";"Hans";"1,2,3,4"
    Table Entries: (int ID; string content) e.g. "1";"Fruityloops"

    Any ideas how to improve this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Hans Gruber View Post
    Any ideas how to improve this?
    depends entirely on what you want to do with it

    i.e. database design means squat without knowing the SQL that will be run against it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2012
    Posts
    2
    To be specific: The user customize the menu of a webpage, he can arrange the order of the menu items and close any of them. In order to print the menu for the current user, i need to get the order (Select Order From User Where Username = $logged_in), after that i select the actual context of every displayed Item (Select * From Entities where ID=$id).

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    looks like you're okay with running two queries, then ... which is good

    be careful naming the string in the users table, because ORDER is a reserved word

    the second query will use IN instead of equals, but you will still ahve to sequence them properly

    if you're using mysql, you can use the handy-dandy FIELD function in your ORDER BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2008
    Posts
    277
    Why not a 3rd table?

    user_entry: user_id*, entry_id*, entry_sequence

    Then to select the entries, just do a join between entry and user_entry ordering by entry_sequence.

    If you want to be really strict, you can put a separate unique constraint on (user_id, entry_sequence), though this can make updating the sequence a bit of a nuisance.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    consider storing the ORDER BY clause as a variable against the users current login.

    eg
    futurity logontime transaction ID ORDER BY manufacturer, type, price DESC
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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