Jump to content


Photo

Updating Sort Order


  • Please log in to reply
2 replies to this topic

#1 aviddv1

aviddv1
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 12 October 2006 - 11:38 PM

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

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 13 October 2006 - 02:08 AM

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
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
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.
<?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());

}
?>

http://dev.mysql.com...-functions.html

#3 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 13 October 2006 - 02:34 AM

I should also mention that you can use 2 queries which may or may not be faster.
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





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users