Keep Data in User Defined Order-Real-Time Order Manipulation

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Keep Data in User Defined Order-Real-Time Order Manipulation

Post by Ollie Saunders »

OK I've got a really difficult problem here so If you can solve it you are probably an expert.

I need to build a web application where the user has control over the order records appear in. I need to do this in a simple and elegant way and also one what won't require rebuilding ids across entire tables. I'm looking to simulate the behavour of Winamp:

Image

Winamp lets the user
  • Select any number of records
  • Select records ranges that are incontigious (can have gaps) - actually this is not essenuial
  • Move them up or down
  • Prevents overflow - Can't go over the edges
  • Real time - or close
Ideally I would like my web app to be able to move them by any amount so 10 steps forward or 8 steps back etc.

I thought the solution to this problem would be a liked list type system which I just about managed to implement using MySQL and some stored procedures but it was far from elegant and I couldn't work out how to reorder without having to copy all the data out into a temporary table and then all back into the original table. That's something that would take a long time with any more than a couple of thousand rows.

In simple terms I need a flexible data storage method (XML?) and an awesome algorithm that orders things on the fly.

Any ideas? Thanks for any help that you can provide.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Have an "order" field in the table. Alter this field to change their display order. As for the actual changes needed to reorder them, it involves a bit of math and calculation of offsets to move the records around the selected parts. Moving them on screen is a bit more difficult if you want it near real-time. Basically, this will involve some Ajax and DHTML to alter the display. The difficulty varies depending on how you are displaying the data (table versus div versus something else), but generally it's not too difficult.. just involves walking the document tree moving this and that from here to there.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

feyd you are right.
I was using a number in a table to keep the order originially but because of the difficulty of the math i decided to try and emulate linked list behavour in the database. Not a good idea it turns out. For those who haven't tried stored procedures in MySQL 5+ before I advise you to steer clear of very complex task like emulating linked list behavour because its very difficult to do and really pushes the limits of what you can achieve with SQL.

So this leaves me attempting to work out the maths. I'm not doing to use any DHTML in this instance I just wanted it to be fast on the server.
I've worked out an algorithm and I'm going to try it out. It basically involves:
  • identifing the rows to be move (by an id that won't change as a result of the move)
  • identifing the rows that will be displaced by the move. If you are moving rows down these will be the rows below if you are moving up these will be the rows above. Its a little bit more complex than that but you get the picture
  • update all the rows you want to move
  • and finally update all the displaced rows to go in all the places where the original moved rows used to be unless a moved row already exists (caused by when a two or more moved rows were next to each other) where you skip onto the next
Anyway so that could all be wrong but i'm going to give this a go after i've tidied my room. Its a mess of socks, books and bits of paper. mmm smells like programmer.
Post Reply