SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    toronto
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Ordering of Table Rows for Content Management

    I am building a simple PHP-MySQL content management tool to allow a user to maintain the banner links on her site.

    I need to allow the user to specify the order that the links should be displayed. And to allow this to be changed, and a new link inserted first, last, or in between existing links.

    I can have an "order" column in the DB table, but I think the PHP code to maintain this could get a little messy.

    Does this have an elogent solution?

    Many thanks!
    Stephen

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    I'd add an "order" column to the database. The PHP code for validation and maintenance should be simple; no numbers can be repeated, and any data entered has to be numeric.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    toronto
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But say I have four rows, with order values of 1, 2, 3 and 4 respectively.

    The user wants to add a new entry, such that it fits between the existing row 1 and row 2.

    I see messy code to do this.

    I am looking for a better idea.

    Thanks
    Stephen

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the way to do it, and the code is really simple, is like this:

    1) assign numbers in increments of 1000, for example

    id name seq
    41 horse 41000
    42 pig 42000
    43 cat 43000
    44 goose 44000
    45 snake 45000
    46 beaver 46000

    2) when a row has to be "moved" (it doesn't actually go anywhere), let's say you need to move beaver to be between cat and goose, you simply change beaver's seq to be half of the difference, thus

    id name seq
    41 horse 41000
    42 pig 42000
    43 cat 43000
    46 beaver 43500
    44 goose 44000
    45 snake 45000

    3) the secret is not to let the user see these sequence numbers, just use "up" and "down" arrows or links like you see on web forms

    example: http://r937.com/sortdropdown.html

    4) eventually you may have to renumber some seqs to make room (e.g. you have to "move" a row to fit between 42578 and 42579), so here you will need a couple extra database calls to get/update neighbouring rows' seqs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    toronto
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent!

    Thank you!
    Stephen


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
  •