How update order of rows stored in a mysql table?

Question

I want to update the order so that GALLERY goes before PRODUCTS.

What should be the query to do this?

Sorry for my English.

Answer 1

Use a linked list: keep parent_id instead of the order field:

id  name       parent_id
1   products   0
2   gallery    1
3   contacts   2

SELECT  mo.*
FROM    (
        SELECT  @r AS _parent,
                @r :=
                (
                SELECT  id
                FROM    mytable
                WHERE   parent = _parent
                ) AS _id,
                @order := @order + 1 AS _order
         FROM   (
                SELECT  @order := 0
                ) vars,
                mytable
         ) q
JOIN     mytable mo
ON       mo.id = q._id
ORDER BY
         _order

This way, you can easily move any item (or even a contiguous block of items) with at most 3 updates.

See this article in my blog on how to implement and query this model:

• Sorting lists

Answer 2

You need more than one query. Something like:

SELECT order FROM table WHERE id=X
UPDATE table SET order=order+1 WHERE order=Y
UPDATE table SET order=order-1 WHERE id=X

X is the ID of the element where you click "up" on. Y is the result of the first SELECT query.

Answer 3

Let's assume the request sends the current value of order and the action type (up/down) for the row the user clicked on. For your example it would be something like script.php?oid=2&action=up. Then you can simply swap the values of the rows having order=$oid and order=$oid-1 for action=up and the rows with order=$oid and order=$oid+1 for action="down".

E.g.

UPDATE
  foo as f1, foo as f2
SET
  f1.`order`=1,
  f2.`order`=2
WHERE
  f1.`order`=2
  AND f2.`order`=1

Answer 4

Assuming id is a primary or unique key...

INSERT INTO yourtable (id, order)
VALUES (1,2), (2,1)
ON DUPLICATE KEY UPDATE 
order=VALUES(order);

If the table is short then the application logic may be even simpler if you put (id,order) pairs in the VALUES clause for all table rows -- that way you don't need to test to see what changed.

Answer 5

I am assuming that the button includes the id of the entry you want to move (button_id)

To move an entry up in order, first shift the entry above the current entry down by 1:

UPDATE foo
SET order = order-1
WHERE order = ((SELECT order FROM foo WHERE id = "button_id") + 1)

Then move the current entry up 1:

UPDATE foo
SET order = order+1
WHERE id = "button_id"

OR To move an entry down in order, first shift the entry below the current entry up by 1:

UPDATE foo
SET order = order+1
WHERE order = ((SELECT order FROM foo WHERE id = "button_id") - 1)

Then move the current entry down 1:

UPDATE foo
SET order = order-1
WHERE id = "button_id"