Reordering a list practices

[nightdesigns]

I have a table of data pulled from a mysql database with each row being a different record.

I have a script where the user can drag a row into a different order and that script will pass into back to a php script to update the database.

I'm lost at how to set up my fields for my custom ordering.

Lets say I have these record numbers

1
2
3
4
5

And the user drags record 5 before record three to make the order this

1
2
5
3
4

How would I go about updating my database to that new order? I've thought about some sort of "sort" field, but still not sure how to update the values properly.

Any ideas are appreciated.

[cretaceous]

add a date time field throughout and update it every time an entry is added /edited

then 'after' the update do a 'select' using the datetime column to 'order by' - that gives you the latest update - if that's order position 3, that's the one that needs to override the old entry that is at position 3 - so then I run a loop to renumber /increment all items below position 3

(I guess you could do it by adding a boolean that you unset on 'all' during an update, apart from the entry you are interested in, which you set to 1)

though as you seem to be using some sort of ajax thing (I think) you might be able to do it before you hit the database with an update