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]
