Jump to content

Updating Sort Order


aviddv1

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.