In many of our applications we support dragging and dropping datasets to change the order. We save this order in a database column that is an integer and is in sequential order.

There are many ways in javascript to handle drag and drop. I will show an example using jquery sortable (opens new window). If you'd like to use something else there are three pieces of key information that need to be sent to the server:

1. Current position
2. Desired position
3. User's id

HTML list

[embed]https://gist.github.com/robertvrabel/7f878cebd0e62ec9ed0cb2779a3097a9#file-list-html[/embed]

Javascript sortable

[embed]https://gist.github.com/robertvrabel/7f878cebd0e62ec9ed0cb2779a3097a9#file-sort-js[/embed]

Example data set for table todos

user_id

display_order

todo

2000

1

Take out garbage

2000

2

Clean house

2000

3

Do dishes

2000

4

Cut grass

2000

5

Change light bulb

Step #1 - Determine the position

[embed]https://gist.github.com/robertvrabel/7f878cebd0e62ec9ed0cb2779a3097a9#file-position-php[/embed]

Step #2 - Update the dragged item

[embed]https://gist.github.com/robertvrabel/7f878cebd0e62ec9ed0cb2779a3097a9#file-set-php[/embed]

Step #3 - Move the item down

[embed]https://gist.github.com/robertvrabel/7f878cebd0e62ec9ed0cb2779a3097a9#file-move-down-php[/embed]

Step #4 - Move the item up

[embed]https://gist.github.com/robertvrabel/7f878cebd0e62ec9ed0cb2779a3097a9#file-move-up-php[/embed]

Step #5 - Update the dragged item to the desired position

[embed]https://gist.github.com/robertvrabel/7f878cebd0e62ec9ed0cb2779a3097a9#file-reset-php[/embed]

With this approach the server will do a total of three queries for every change to the display order no matter how many items are in the list. Examples typically tell you to iterate over every item to do an update query to set the new order. That approach results in total queries = total amount of items. This new approach is a significant improvement and has reduced a lot of our large data sets from 40+ queries down to three.