[Suggestions] - Ordering Items Dynamically

[Adamhumbug]

Hi All,

I am looking for some suggestions here on a good way to order items.

I have a table that has an id, name and display order columns.

I am looking for suggestions on the best way (in a ui) to set the display order of each item without having the same value as any other item.

So the items table looks like this.

Item Name    -     Display Order

Item 1.            -     1

Item 2.           -     2

Item 3.           -     3

I want to be able to change the display order and automatically rearrange everything else.

I can think of many crude ways to change the order but none that would update the rest.

I want to be able to add Item 4 and it have display order 2 - item2 and item3 would have their display orders increased to move down.

As always, appreciate your input.

[Barand]

3 minutes ago, Adamhumbug said:

I want to be able to add Item 4 and it have display order 2 - item2 and item3 would have their display orders increased to move down.

 

-- first
UPDATE thetable
       SET display_order = display_order + 1
       WHERE display_order >= 2;

-- then
INSERT INTO thetable (item_name, display_order)
       VALUES ('Item 4', 2);

 

[Adamhumbug]

4 minutes ago, Barand said:

 

-- first
UPDATE thetable
       SET display_order = display_order + 1
       WHERE display_order >= 2;

-- then
INSERT INTO thetable (item_name, display_order)
       VALUES ('Item 4', 2);

 

oh, and i thought it was going to be complicated.

Thanks

[maxxd]

It gets a little more complicated if you want to automatically reorder things when a new item is put into a display slot that already contains an item, but honestly not a whole lot. If this is a one-off though, yeah - you're done now.