How to edit things in ORDER

[jumpthru]

What is the best interface for editing some items in a database, when the order of those items matters?

I current have a column called "ordering" and each item has an order. If there are 10 items, then item A might be order 1, item B might be order 2, etc.

So if someone wanted to change the order then they would have to make sure that they properly edit the values so that there aren't conflicts, etc.

Has anyone else ever had to deal with this, and what did you come up with? I was thinking of a system where you press an up arrow next to the item, and it then changes the order number for that specific item. However, you have to process every single item then, and increase/decrease the other ordering numbers as neccesary.

Any slick solutions?

[auricle]

Err... this doesn't really seem like a suitable topic for this forum, but...

Why not have a couple of buttons beside each item which allows one to push the item up the list by one or down the list by one? The script which processes the form would have the busines logic to re-order the items (and update the database accordingly).

[Captain Proton]

I usually put an up and a down arrow image next to each item. Another option is to put all of them in a listbox with up and down buttons and then to submit the new order all at once instead of having to reload the page for every ordering operation.

I also wrote an OrderedGroup class that tackles the problem of updating the database. It has methods for moving items 'up' and 'down' a group of ordered items and also for restoring the order when an object is deleted from the group. Nothing fancy but it's a useful thing. If anyone's interested, just let me know and I'll post the code.

[foofoonet]

Its not slick, but I gave users a list of items, with the order-number in a text field that they could override when they did a "save".

As they were assembling the list it allowed them to have two number 2s for example, because it had to go thru that final stage.

The way the app worked was when they got to the very last stage and did a "publish", it then did a comparison making no number was duplicated or threw up a "go back and put them in order you ..." msg.

That could have been done on any save, and it could have been done in JS too.

[maksimovic]

let's say you have a list with 10 items, each item having a select box with values 1 to 10.

<select name="order[1]">
  <option value="1">1</option>
  <option value="1">2</option>
  [...]
  <option value="10">10</option>
</select>

[...]

<select name="order[10]">
  <option value="1">1</option>
  <option value="1">2</option>
  [...]
  <option value="10">10</option>
</select>

Check submitted data:

$totalValues = count($_POST['order']);
$uniqueValues = array_unique($_POST['order']);

if ($totalValues == $uniqueValues) {
   // ok, update DB
}
else {
  // !ok, return error msg
} 

What's the big deal?

[foofoonet]

yeah, array_unique is a good one, I only found out about that recently. Good Point. (I wonder if there is a JS equivalent?)

[maksimovic]

Maybe there is a JS solution, but I don't count a lot on JS validation. Visitor can turn off Javascript and your app becomes vulnerable if you don't have server-side validation as well.

Personally, I always do server-side validation, and then if there is enough time to play around until the deadline - I add some fancy Javascript