What is the most efficent way of updating an "order" column in MySQL via PHP.

Question

I'm currently creating a Flash CMS via Flex and the Zend Framework. In the Flex interface, I have a list of drag-able items pulled from a MySQL database via the Zend Framework. The user can rearrange the items by dragging and dropping them which will change their order in the mapped Flex array.

Back on the server I have a column in my "items" table called "order" that keeps track of the items' order for presentation purposes. So my question is what is the most efficient way of updating my changes in the "order" column of my database?

I know I could just iterate through the entire array and run an update query on every row, updating the order value to the value of the counter/iterator, but that seems a bit ugly, I may not need to update every row, and I could potentially be firing a lot of queries very quickly to MySQL.

Any idea would be greatly appreciated.

Thanks for looking. :)

Answer 1

Check the following two question out;
Fetching linked list in MySQL database
What is the most efficient/elegant way to parse a flat table into a tree?

You have to implement a linked list, if you want fetch alway the entire list.

Answer 2

Are you updating the table each time the user moves a item?

If yes, then you can store the order of items in the database as a as linked list. Each item will know his parent - the item which is on top of him.

The move of one item to some other place will need 3 database updates, which will change the parents. If you have this order of elements : A B C D E and drag B between D and E so you have A C D B E

So instead of the "order" column in your database you would have a "id_parent" column which would point to the element which is one place up. And in this example you would do :

UPDATE C set parent = 'A' UPDATE B set parent = 'D' UPDATE E set parent = 'B'

Answer 3

I would store the original order in an array - for instance as a POST-variable like this: $_POST[order] = array(1,2,3). When the user Submits, I would store the new order in another array. Finally, I would compare the before and after values, and only update where they differ.

Code example:

$before = array(1,2,3);
$after  = array(2,1,3);

for($i=0; $i<count($before); $i++) {
    if($before[$i] !== $after[$i]) {
     $chg[] = $i;
    }
}

print_r($chg);

Result: Array ( [0] => 0 [1] => 1 )

Meaning that you will only need to update the first two rows.

Answer 4

If you're handling move of a single item, then you can do it in two queries, one being:

UPDATE items SET `order` = `order` + 1 
  WHERE `order` >= :index_of_inserted_item 
  ORDER BY `order` DESC

In MySQL update has to be done starting from highest value to avoid bogus duplicate key errors.

Answer 5

Is there a ROWNUM SQL clause in MySQL. If there is, something like this may work (or not):

UPDATE table
SET Order = ROWNUM
WHERE GroupId = Id