Changing sort order of items.
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]
Re: Changing sort order of items.
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]
Re: Changing sort order of items.
Michael J. Pawlowsky wrote: 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. Here is one idea: The sort_order column could be an integer, you could put values 1,2,3... for each group in this column. When a user move a row down, you issue statements similar to this: SELECT @so:=sort_order FROM mytable WHERE id = $moving_id; UPDATE mytable SET sort_order = sort_order + 1 WHERE id = $moving_id; UPDATE mytable SET sort_order = sort_order - 1 WHERE group_id=$x AND [EMAIL PROTECTED] + 1 AND id != $moving_id; This assumes you only know the group_id $x and the $moving_id. The @so is a user variable, it wont work well with replication. You can easily do the same in your application. If you also know the id of the row you are swapping with, its easier: UPDATE mytable SET sort_order = sort_order + 1 WHERE id = $moving_id; UPDATE mytable SET sort_order = sort_order - 1 WHERE id = $other_id; It would be equally easy to swap any two items in the list, not just two adjecent items. Just swap sort_order values. To move any item to the top, you would need something like this: SELECT @so:=sort_order FROM mytable WHERE id = $moving_id; UPDATE mytable SET sort_order = 1 WHERE id = $moving_id; UPDATE mytable SET sort_order = sort_order + 1 WHERE group_id=$x AND sort_order@so and id != $moving_id; In this case all rows between the moving row and the row it is moving to needs to be changed. This is heavier on the db, but it should not be an issue, unless your users do this all the time. You should in any case index the table non-uniquely on (group_id,sort_order), in addition to the primary key on id. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing sort order of items.
It would be equally easy to swap any two items in the list, not just two adjecent items. Just swap sort_order values. Yup... I think that is the key! Basically as they are inserted I will look up the max sort_order value for that group so far and increase that by one for the current insert. Then when it come to changing the sort order values I will simply swap it with the value of the previous or next one. So only 2 updates. The only thing I need to look out for is not to allow them to try and move the first row up... or the last row down. Thanks for the help, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
