在数据库中存储帖子显示顺序

I am developing a web application which will have images displayed in a slider. It is important that I am able to modify the order that they are displayed in.

Currently, I have a column in the table of images called 'order', which must be set to ensure the ordering works correctly, but changing it requires changing every other, too, which becomes tricky when dealing with hundreds of records.

This table, as well as queries for modifying it, are in development, so it's completely fine to change the way I do this.

My questions are:

  • When I'm inserting a new row, how can I make sure it appears at the end of the list? Auto-Increment in SQL tends to leave gaps if you delete entries from the table. How do I make sure that the row is assigned an order 1 greater than the highest order in the table?

  • How do I reorder the table? Imagine that I use a drag and drop interface (or similar) to reorder one image, bringing it to the top, or a different part of the list. If I do that, I need to reset the numbering of every item, which is, again, inefficient when dealing with hundreds of rows.

  • How do I prevent two rows from having the same order at any given time? The UNIQUE flag on the column? Presumably if I set that flag, changing the order from, say, 8 in one row opens up another to use that number. Right?

Thank you for your time, and I hope this isn't too vague a question to be easily answered!

  1. It will be in the end of the list. Just use auto increment feature and do not set ID manually (like lastID + 1) and you'll never have described problem.

  2. You can allow user to change order of the list and then just update all order cells (for each member of the list). Or you can use Ajax and exchange order value for two members every time user drag-and-drops.

  3. Use one of approaches described in 2 and you'll never have this problem.

  1. Query the table you are about to insert into to find the MAX id in the table. Then on your insert just do this +1.

  2. When re-ordering, say you want to set image order 6 to order 3. You will want to update all rows from order 3 and onwards to order+1 then set the image to order 3.

UPDATE table SET id = id+1 WHERE id IN (SELECT * FROM table where id >= 3)

  1. If you are using unique on the order column to re-order you will have to update the current value to a temp value such as 99999, then use method in 2. However method 2 should keep this table from receiving any duplicated values.

sidenote

You could on delete of a picture re-evaluate the ids to keep no gaps

UPDATE table SET id = id-1 WHERE id IN (SELECT * from TABLE WHERE id > 3)

Where 3 is the deleted id