How to update sort order when using a input sequence?

Question

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_order 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

For me at least, you need to provide clearer criteria for which the sort is determined. If the user enters 5, in what order are you wanting the items to redisplay? What if the user enters 3? What if they enter 1? — deefour
– deefour, Commented Jul 12, 2011 at 16:29
@deefour if they enter 1 then item they entered it for just becomes the top of the list item.. makes sense? items are displayed by their display order .. 1 being at the top. — Kay
– Kay, Commented Jul 12, 2011 at 16:35
why don't you give us some actual code to work with. Are you simply unclear how to deal with an UPDATE query in SQL? Are you having trouble getting the values from the $_POST superglobal? The root of your question is still quite unclear. — deefour
– deefour, Commented Jul 12, 2011 at 16:42
@deefour I am not sure what is not clear to you. I have not got any problem with _POST or writing queries .. Its a logical problem.. if I get the pseudo code I know what to do with it.. — Kay
– Kay, Commented Jul 12, 2011 at 16:52

Marc B · Accepted Answer · 2011-07-12 16:42:32Z

2

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.

answered Jul 12, 2011 at 16:42

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kay Over a year ago

Marc thanks for your answer. Actually this'd leave multiple same values for display_order field, imagine user changes Item3 which has current order as 3 to 4.. above would change the Item3 order to 4 and Item 4 has the display order as 4 as well??

Marc B Over a year ago

Well, you can do verfication before doing the database work and say "hey, you've got duplicate values. proceed y/n?". But at some point, you have to accept what the user provides and go with it.

Kay Over a year ago

well I could do what you suggested already but I am trying to avoid this so that's why I've asked questions here. Thx anyway.

Ergec · Accepted Answer · 2011-07-13 13:23:57Z

1

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/

answered Jul 13, 2011 at 13:23

Ergec

11.9k7 gold badges54 silver badges68 bronze badges

Collectives™ on Stack Overflow

How to update sort order when using a input sequence?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related