3

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!

Forest
  • 938
  • 1
  • 11
  • 30
  • Why do you need consecutive id values? Gaps in the sequence shouldn't matter.... add an `sortOrder` column to your table that you can sort by if you need specific sequences for ordering – Mark Baker Apr 30 '16 at 15:17

2 Answers2

3

  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

Matt
  • 1,749
  • 2
  • 12
  • 26
  • `mysqli` in Laravel? Seriously? Also, this approach can cause damaged data on high loaded projects, it's a bad practice. – Alexey Mezenin Apr 30 '16 at 15:26
  • @AlexeyMezenin What damage can it cause? – Forest Apr 30 '16 at 15:27
  • removed it as an example. – Matt Apr 30 '16 at 15:27
  • @Furze, http://stackoverflow.com/questions/3511826/how-bad-is-using-select-maxid-in-mysql-instead-of-mysql-insert-id-in-php – Alexey Mezenin Apr 30 '16 at 15:29
  • @AlexeyMezenin If I use transactions or prevent auto-commit and lock the table for the duration of the query, that would be safe, wouldn't it? I wouldn't be likely to have many people writing to the table concurrently, though I'm not sure if that would break things for the other people if there was bad timing. – Forest Apr 30 '16 at 15:32
  • @Furze, what if two users will get `lastId = 7` simultaneously and both will try to write data in `8` cell? Will locking table help? – Alexey Mezenin Apr 30 '16 at 15:36
  • 1
    @AlexeyMezenin Damnit, this is as complicated as I predicted. You're right, of course, that won't help. Your answer is better in practice. – Forest Apr 30 '16 at 15:38
1

  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.

Alexey Mezenin
  • 158,981
  • 26
  • 290
  • 279