Store order of HTML list without updating every row in database?

Question

I have an HTML list that can be sorted (using jQuery's sortable API). I am storing the list items inside a database; each row stores the item's order, and text. How can I store the order of the list items so I do not need to update multiple rows when an item's order changes? List items can be added and removed.

Here's an example of what I'm talking about:

<ol>
  <li>Go shopping</li> <!-- Order: 1 -->
  <li>Go jogging</li> <!-- Order: 2 -->
  <li>Eat</li> <!-- Order: 3 -->
</ol>

The user moves the "Eat" list item to the top, now I must update every single row in the database to store the order.

<ol>
  <li>Eat</li> <!-- Order: 3, now update to 1 -->      
  <li>Go shopping</li> <!-- Order: 1, now update to 2 -->
  <li>Go jogging</li> <!-- Order: 2 now update to 3 -->
</ol>

Answer 1

What I did the one time I had to do that was have some kind of "header" record that stored the ID's of all the "items" in order, separated by commas...
So, in your case, I would've stored 1,2,3 in the first version, and 3,1,2 in the second one.

This is absolutely not normalized, and feels ugly, but:

• When the user changes the order, you only update ONE row. This was important, our lists could get big.
• When the user adds an item, that item naturally falls at the start or end of the list, depending on how you retrieve the items to show them.

When you want to show the list, you read the items and put them in a hashtable, ordered by their ID. Then you read the string that sets the order, split it by commas, and for each ID, you find the corresponding item in your hashtable, and add it to a "list" or "collection".
Then, if you have items left in your hashtable, you append them to the list at the start or the end, depending on where you want new things to appear.

Just my 2 cents