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

Question

I want my client to be able to order items in the backend. I decided that the easiest way to do this is to have a database table for the item that has an "order" field that receives a value. This is the value that the client inserts. Then I can simply run the SQL query query on the "order" field.

Right now it’s very simple for me .. there is no logic .. so the user would put in order = 100 for one element and order = 200 for another .. and the one with a lower value would be displayed first. Is there a more elegant way to do this? I am thinking of something like "moving up" and "going down." etc. I am trying to simplify item administration.

Any ideas would be greatly appreciated!

Answer 1

As for the database, your decision using the order field is correct. You probably don't want to show this to the user, and using a couple of up and down arrows, you can turn off text input. When the user clicks the up arrow, you basically switch the specific record with the record above it. In metalanguage:

// $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

Please note that this is just a rough draft; you will need to add the correct escaping. In addition, if you need a UNIQUE index to ensure strict ordering, you will need to modify the last two statements.

Answer 2

, . jQuery UI Sortable. , , AJAX, : http://harm.glucose-ontwerp.nl/dev/sortlist.php. , AJAX, , .

Answer 3

(, ) " " " ", , , , . . , -, sortkey " " , :

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, in order to determine the given sort order of users, you must join the table of elements in the userSort table where userKey=[the user you're interested in]