我如何组织我的数据库，以便我可以上下移动项目？

I am creating a bit of software where people have birthday lists and they can put items in their list, and it’s viewable by other people. I have a table in my database where everything is organized called birthday lists. It is arranged like this:

An example row would be:

The person field is who owns the item, and all the items for everybody are in the same table. The id, name, link, and description are pretty self explanatory. The number field is how it is shown when you view the list page, so number 10 would appear above 0, and the priority is irrelevant to this question. This is an example of what happens when I view Jimmy’s list in my browser. (don’t worry, I will arrive at my point eventually)

I’m adding a lot of pictures because I never make any sense and this is the best way I can explain things :)

Anyway, here is another picture showing an example database.

If I viewed Russell’s list in the browser, it would look like this:

Notice how the $$$$ item is above the stapler because it has a higher number. That’s what it’s for. If I was logged in as Russell viewing this list, I would see buttons saying Move Up and Move Down that increment and decrement the number property on that item. I shall demonstrate my problem with an example scenario.

Let’s say Jimmy logs on and he has a lot of list items. He moves some up quite a few times, so it gets that the number value on one item is way above that of all the others. He then moves another item up, reloads the page, and thinks it didn’t do anything, because the number value of the second item is still less than that of the first. I hope that makes sense. Another problem is that if the number value on two items is the same, it is a huge issue. The point is, the way I have it isn’t going to work.

Does anyone know of a way I could get this number thing to work properly, and tell me how I could make it so users could move items up and down on their list? (also, if my database is organized badly, I’d love to know too :))

Thanks a bunch in advance!

Ending information, in case you need it:

• I am building this with PHP 5 and MySQL
• This is on a remote Linux server with GoDaddy

When you move an item up or down by one 'move' swap the 'number' values of the item being moved with the 'number' value of the item it is jumping over.

I would handle it this way:

1. For moving/sorting your items, you could use jQuery UI sortable for example.

2. You can get the order by it's method serialize().

3. Send the serialized data to your PHP script and swap/update the changed positions (number in your table)

4. Update your dataset in your database.

5. To SELECT your sorted data use ORDER BY number DESC (more)

Good luck! :-)