I wouldn't use a loop but an UPDATE statement instead.
If I understand you correctly, all of your records are in the same group
but you need them displayed in a user-defined order. It would be MUCH
easier to manage that if the sort order values were already in sequential
order. You may need to do a one-time loop-based query to individually
reset each row to an appropriate value but after that these UPDATE
statements should keep everything in order.
Assume you want to move an item of group 6 from position 2 to position 6
SELECT @id := id from basictable where group_id = 6 and sort_order = 2;
UPDATE basictable
SET sort_order = if (id = @id, 6, sort_order -1)
WHERE group_id = 6
AND sort_order BETWEEN 2 AND 6;
and going the other way, from position 10 to position 2
SELECT @id := id from basictable where group_id = 6 and sort_order = 10;
UPDATE basictable
SET sort_order = if (id = @id, 2, sort_order +1)
WHERE group_id = 6
AND sort_order BETWEEN 2 AND 10;
make sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Michael J. Pawlowsky" <[EMAIL PROTECTED]> wrote on 11/29/2004 11:17:01
AM:
>
> I'm trying to create the most efficient way to allow a user to change
> the display order of a group of rows in a table.
>
> Lets say the basic table is:
>
> id
> group_id
> name
> sort_order
>
> The query to display it would be
>
> "SELECT id, name FROM mytable WHERE group_id = $x ORDER BY sort_order"
>
> Now when I display it they currenlty all have the same sort_order value
> so they come in the order however the db finds them.
> In my PHP app... I have a small arrow that allow them to move a row up
> or down changing the display order.
>
> Currently this is done by looping through the results of all the items
> in a group and reassigning a new sort_order value to each one.
> Meaning 1 SELECT and MANY updates (1 for each item in the group).
>
> I was wondering if anyone has come up with a better way to do this.
>
>
> Thanks,
> Mike
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
