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!
