Jump to content

Archived

This topic is now archived and is closed to further replies.

aviddv1

Updating Sort Order

Recommended Posts

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.