Changing order of records from the front end

I am faced with a problem coding my next feature. I want the user to be able to rearrange records and change the display_order value. I'm using Jquery UI's draggabl开发者_如何学运维e and droppable to facilitate this.

I can see how a simple swap of display_order values would work. But I want to set a display order for a record and ideally have the others shuffle around so there are no repeated display_order values. Apart from not getting my head around how I would do that it seems like it would be hard to code and inefficient, shuffling every value around in the list.

So I am open to other suggestions of how this sort of thing is normally, or should be done.

I though of maybe using a value like 3.000 to represent the order and then when I want to make a record take its place make its value 3 - 0.001 so its 2.999 and will sort between 2 and 3. But I can see so many things wrong with that idea and doesn't seem like a good path to follow.

Thanks for any input!

• add a sorting column to your table, smallint, mediumint or int depending on the expected number of total entries
• A new entry is appended to the end, max(sorting) + 1

• when reordering an item, get the new position it will be in and alter the higher sorting entries accordingly:

  mysql_query('UPDATE yourTable set sorting = '.$yourNewposition.' where id='.$yourUniqueId .' LIMIT 1');

  mysql_query('UPDATE yourTable set sorting = sorting + 1 where sorting >= '.$yourNewposition.' AND id != '.$yourUniqueId );