How to handle "order" of a list of items in DB (php)

Question

I would like my client to be able to order items in the backend. I figured the easiest way to do it is to have the database table for an item have a field called "order" which gets a value. This is a value the client puts in. Then I can just run SQL queries ordering by the "order" field.

Right now I have it very simple.. there is no logic.. so the user would put in order=100 for one item and order=200 for another.. and the one with the lower value would get listed first. Is there a more elegant way of doing this? I am thinking of something like "move up" and "move down". etc. I am trying to make administrating items more convenient.

Any ideas would be greatly appreciated!

Answer 1

You could provide UI functions (i.e. buttons and supporting code) to implement "move up" and "move down", but at the database, in order to persist the user's sequence, you have to store a sort key as you have already discerned. There's no other way to do this. If you want different users to be able to order the same rows differently then the sortkey will have to be part of a separate "user sort" table with a schema like this:

itemKey     The primary key of the item table   
userKey     The primary key of the userID table (or maybe just the userID)
sortOrder   The sort key for this combination of user and product

Then to determine a given user's sort sequence you would join the item table to the userSort table where userKey=[the user you're interested in]

Answer 2

Your approach is just fine for what you're trying to do. For the client interface side of things I would suggest using the jQuery UI Sortable library. What's nice about the sortable interface is that it can be easily linked to your storage code through AJAX as in the following: http://harm.glucose-ontwerp.nl/dev/sortlist.php. The data sent over AJAX is a list of IDs and the order they should be in.

Answer 3

As far as the database goes, your solution using an order field is correct. You probably don't want to expose this to the user though, and using a pair of up- and down arrows the text input can be circumvented. When a user clicks the 'up' arrow, you basically switch the specific record with the record above it. In meta-language:

// $myId holds id of record, $myOrder holds the order of the record

SELECT id, order FROM t WHERE order < $myOrder LIMIT 1
// Store id in $nextId, order in $nextOrder

UPDATE t SET order = $nextOrder WHERE id = $myId
UPDATE t SET order = $myOrder WHERE id = $nextId

Note that this is only a very rough draft, you will have to add proper escaping. Also, if you want a UNIQUE index on order to ensure a strict ordering, you will need to change the last two statements.