How to update sort order when using a input sequence?

I want to allow users to rearrange items by providing a number. So if there are 5 items

Id, Item, Display Order 
1, Item a, 1
2, Item b, 2
3, Item c, 3
4, Item d, 4
5, Item e, 5

users get 5 input boxes and they can provide a number and click update to reorder the items. If user enters 125 as the new position in the above, it just sets it as the max (which is 5) and update the other items, if user enters -10, it just makes that item as 1 and update others.

I just can't figure out how to update the order once the user submits the form. any help would be appreciated. Thanks

UPDATE

Using the data above I present 5 rows to the user with 5 input boxes containting display_or开发者_如何学编程der value as the default value. If the user wishes to reorder the list, they can update the input box value and click submit to update the database. I am using ORDER BY displayorder query to list the reocrds.

Any questions please ask

How about a form something like this:

<form ...>
<table>
<tr>
   <th>Item A</th>
   <td><input type="text" name="order[1]" value="1" /></td>
</tr>
<tr>
   <th>Item B</th>
   <td><input type="text" name="order[2]" value="2" /></td>
</tr>
etc...

The subscripted value in the name field array is the ID of that particular item. When the form's submitted, you'd do something like:

foreach($_POST['order'] as $key => $val) {
   $val = intval($val);
   $key = intval($key);
   $sql = "UPDATE yourtable SET DisplayOrder=$val WHERE id=$key;";
   mysql_query($sql) or die(mysql_error());
}

Then for retrieval, it'd simply be:

SELECT ...
FROM yourtable
ORDER BY DisplayOrder ASC

The user enters any number they want, and it's up to them to make sure they're ascending in the right order. Your script just has to record those numbers and sort by them at retrieval time.

Why don't you just use jQuery Sortable. Much easier, user friendly and you will not have "duplicated order values" problem.

http://jqueryui.com/demos/sortable/