Updating Sort Order

[aviddv1]

Hey there,

I've got a table with a field called sort_order.  I want to be able to change the sort order of a particular record and move all the other affected records either up or down.

so if I've got 1,2,3,4,5 and i want 3 to be 1 then it would do this:
3 => 1
1 => 2
2 => 3
4
5

any ideas?  I'm sure I can use a loop in php, but I'm wondering if there's a more efficent way using mysql.

thanks,
Howie

[shoz]

If the sort order is being changed to a lower number as is the case in your example then this query should give the desired result
[code]
UPDATE
tablename
SET
sort_order =
    CASE
        WHEN sort_order = 3 THEN 1
        WHEN sort_order < 3 AND sort_order >= 1 THEN (sort_order + 1)
        ELSE sort_order
    END
WHERE
sort_order <= 3 AND sort_order >= 1
[/code]
The query should work even without the WHERE clause but using it makes the query more efficient by looking only at those records that will be affected. An index should be on "sort_order".

This script shows how you can generate the query based on whether the sort order is being changed to a higher or lower number.

You should add a check to ensure that both $from and $to exist in the table.
[code]
<?php
$from = 3;
$to = 1;
if ($from != $to)
{
    $query = "UPDATE tablename SET sort_order = CASE WHEN sort_order = $from THEN $to ";

    if ($from > $to)
    {
        $query .= "WHEN sort_order < $from AND sort_order >= $to THEN (sort_order + 1) ";
        $where = "WHERE sort_order <= $from AND sort_order >= $to ";
    }
    else
    {
        $query .= "WHEN sort_order > $from AND sort_order <= $to THEN (sort_order - 1) ";
        $where = "WHERE sort_order >= $from AND sort_order <= $to ";
    }
    $query .= "ELSE sort_order END ".$where;
    mysql_query($query) or die(mysql_error());

}
?>
[/code]

http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html

[shoz]

I should also mention that you can use 2 queries which may or may not be faster.
[code]
UPDATE tablename SET sort_order = 1 WHERE sort_order = 3

UPDATE
tablename
SET
sort_order = sort_order + 1
WHERE
sort_order < 3 AND sort_order >= 1 AND id != $id_of_row_that_held_3
[/code]