most efficient way of changing order of items in mysql database

Question

I'll be returning values from my sorting script, which provides the position of the element at the beginning and at the end of the change. So let's say 4th position goes to 1st. Knowing that I just need to add 1 to the original 1st,2nd, and 3rd position to retain the order.

Now, what's the best way of updating the database? Is it to run a queries in a loop for every id that needs to be changed? That's 4 update queries, which seems inefficient? Is there another way?

Answer 1

You can update the position value of rows in your table with a single query, regardless of whether you're moving the position up or down:

UPDATE tbl
SET    pos = CASE WHEN pos = [oldpos] THEN
                       [newpos]
                  WHEN [newpos] < [oldpos] AND pos < [oldpos] THEN
                       pos + 1
                  WHEN [newpos] > [oldpos] AND pos > [oldpos] THEN
                       pos - 1
             END
WHERE  pos BETWEEN LEAST([newpos], [oldpos]) AND GREATEST([newpos], [oldpos])

pos is your position field.

Just pass in the current position of a row as [oldpos] and the new position of that row to [newpos] and the query will work for you.

Ex. if you're changing a row from position 3 to position 10:

UPDATE tbl
SET    pos = CASE WHEN pos = 3 THEN
                       10
                  WHEN 10 < 3 AND pos < 3 THEN
                       pos + 1
                  WHEN 10 > 3 AND pos > 3 THEN
                       pos - 1
             END
WHERE  pos BETWEEN LEAST(3, 10) AND GREATEST(3, 10)

Answer 2

I have done something similar to what I think you're trying to do. I had a table where each row represented a certain UI element to be laid out on the page in a grid. The user was able to change the order of the UI elements, and I would store that number in the table so that it would come up in that order by default.

For this, if I recall correctly, I used two queries:

UPDATE table SET position = 0 WHERE position = 4;
UPDATE table SET position = position+1 WHERE position < 4;

First query sets that particular one to the lowest number, second query increments all the rows under the original value by 1 (including the one you just set to 0, making it 1).