I want to list items by defined order.so my table has order column it'll keep the order number. so, i can list items using that order number.If the number of items are less means no problem,But while it is ge开发者_开发百科tting more. i'm getting troubles.

Item table:-

id   item varorder
1     A1    1
2     A2    2
3     A3    5
4     A4    3
5     A5    4
......
1000  A1k   1000

Using varorder, I can list items in defined order like bellow

SELECT item FROM tbl_item ORDER BY varorder ASC

The query results me correct order of items like A1,A2,A4,A5,A2,...,A1K

My Problems

Problem occurring while i try to make A1K varorder as 1.

• Using Up and Down arrows I can swap items ,or change the order of item using previous and next arrows.If i want to make A1K at first position,I should click the up arrow 1000 times.This is really poor idea for managing items.

• Using normal update If i want to make A1K at first position,I should update all rows(1000) to keep order correct.so I don't think this is good idea.

So suggest me. Is there another good idea to keep order in defined order ?

Thanks

Add a column "sortdatechanged" and make it now() on insert. Then update it when you hit the up arrow. When you hit the down arrow, update varorder to max(varorder)+1, and set sortdatechanged to min(sortdatechanged). Then select by sortdatechanged desc, varorder.

About UI: drag and drop = best way.

About updates: You can use varcodes like: 256 512 768 1024... So when you want to move some element you update his number to (lefter + righter) / 2. At cases like lefter = 1; righter = 2; (lefter + righter) / 2 = 1.5; you have to run some procedure which will update all element to (256 512 768 1024...) according to current sorting.

You might consider a sparse ordering, like:

id   item varorder
1     A1    10000
2     A2    20000
3     A3    50000
4     A4    30000
5     A5    40000

Then you can move an element in front of or in between any other element in the list without having to update every row in the table. I'd suggest an algorithm that appends to the end of the list using a varorder that is greater than the previous varorder by at least 10,000, and that handles an insertion between two existing elements by assigning the inserted element a varorder that is halfway between the varorder's of the two existing elements.

So with the above example, moving A3 to the second position in the list would yield:

id   item varorder
1     A1    10000
2     A2    20000
3     A3    15000
4     A4    30000
5     A5    40000

With a very large number of relocations this approach will eventually run out of space in the sparse index, so the question you have to ask yourself is whether or not you think there will ever be that many reorder operations in practice.

The updating of rows will be problem is they are millions, not 1000. In that case, you can have varorder and orderupdated, and order by varoder ASC, orderupdated DESC. In this way, when 2 records are with the same order (1), the one that was set to 1 later will be first, and the other will be second.