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
Michael J. Pawlowsky 's gravatar image asked Nov 29 2004 at 16:17 in Mysql-General by Michael J. Pawlowsky

3 Answers

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" wrote on 11/29/2004 11:17:01
AM:
SGreenunimin.com
Date: Mon, 29 Nov 2004 11:40:07 -0500




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 's gravatar image answered Nov 29 2004 at 16:40 by SGreenunimin.com Date: Mon, 29 Nov 2004 11:40:07 -0500 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
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
Roger Baklund 's gravatar image answered Nov 29 2004 at 16:57 by Roger Baklund
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
Michael J. Pawlowsky 's gravatar image answered Nov 29 2004 at 17:32 by Michael J. Pawlowsky

Related Discussions

  • Order Of Items In A WHERE...IN Clause in Mysql-general

  • Hello, Is it permissible to order a clause such that the search term is the first item (in the clause)? standard: field1 IN (123, 654, 789) in question: 123 IN (field1, field2, field3) I am interested to know if the optimizer treats this any differently if anybody can shed any light on it (except for the obvious difference in the above queries!) Thanks, Andy...

  • Selecting A Group Of Distinct Items? in Mysql-general

  • Hi, I'm pretty much an SQL newbie, so apologies in advance if this is basic stuff. That'll teach me for skipping the databases class in college... I just created an app using a table that has entries like this simplified version: name date value Fred 2005-10-01 7 Fred 2005-10-02 10 Joe 2005-10-01 4 Joe 2005-10-01 10 and so on. New values...

  • Total Items in Mysql-general

  • Hi, If I use LIMIT it returns X rows in set, is it possible to get the total rows but show only X rows? I am doing it from DBI # $sth is a prepared and executed sql object $sth -> rows #returns 10 if LIMIT is 10, but matches are 100! Thanx. Are there functions for doing complex search on the database? It wouldn't be nice if I extract all entries and manually search in them :( ...

  • Items Quantity in Mysql-general

  • ...

  • DELETING Items in Mysql-general

  • ...

  • How To Find Last Price Of Items That Were Sold? in Mysql-general

  • Is there a fast way to find the last price of an item that was sold, without doing a table join or subselect? The Details table has Product_Code: Char(20), Date_Sold: Date, and Price_Sold: Double. The products are sold for different prices during the month and I need to retrieve the last price it was sold for. The only thing I can think of would be to do a Group By to get the max(Date_Sold...

  • Counting Number Of Associated Many-to-many Items in Mysql-general

  • Hello! I have three tables, mapping out a n:n relationship of authors and the books they worked on: table 1: authors (id, name) table 2: authorships (author_id, book_id) table 3: books (id, name, bestseller tinyint) Here's two different queries I want to run: 1. Select each author, and how many books he has worked on. 2. Select each author, and how many bestseller books (bestseller...

  • Select A Certain Num Of Items In Sql Query in Mysql-general

  • Suppose I want to select items 10 to 30 from a table tab, which has 36 rows. What could be the sql query ? Thank you. Aman PS: I am using cgi-perl. MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx...

  • Problems Deleting Items in Mysql-general

  • Hi, This is probably an easy question for the MySQL pros out there. One of our customers has a web-based forum driven by MySQL. He has a pretty popular web site, and his database has grown to about 5MB. We used a command like this to get rid of alot of his messages: DELETE FROM whatever WHERE cls < 952000000; That took out alot of his messages, but when you look at his database...

  • SV: DELETING Items in Mysql-general

  • sub queries are not yet implemented in MySql, but will be in 4.0... Regards, Johan Nilsson...

  • Keeping Items Unique in Mysql-general

  • Good morning, Say I have a table of two columns, name and email_address. I want to make sure there are never two rows that have the same name.. I don't mind if email_address is repeated, as long as the name associated with it is distinct.. What is the best way to accomplish this? TIA .. Elliot Tobin - UD/CiS '02 [[email protected]] University of Delaware - User Sevices Computer and...

  • Fulltext TODO Items in Mysql-general

  • Hi all, I've searched the development roadmaps and I can't find any of the Fulltext TO DO items (http://www.mysql.com/doc/en/Fulltext_TODO.html) on them. Can anyone point me to something which would confirm or deny plans to implement these features? I am particularly interested in proximity operators. Matt Griffin Software Developer Nerac, Inc. Tolland, CT 06084 phone: 860-872-7000...

  • Selected Items Delete in Mysql-general

  • Hi, I am kinda new to this mysql thing, so please forgive the basic question. What i need to do is, list my records on a table with a checkbox for each. After we choose several of the records, we click a button and i want to be able to delete the ones selected. Is there a simple mysql command that could perform this? I can do all the html/form/php list things, but the mysql is out of...

  • Count Unique Items in Mysql-general

  • Sorry if this has been asked already. I have a table of records, and one of the columns is department. What I need to do is output a list of departments and a count of how many records correspond to each department. Any suggestions would be great. Thanks a lot. -Stuart Stuart Jackson Network Services The Johns Hopkins University [email protected]..

  • Select Without Taking All Items in Mysql-general

  • Hi I am trying to write a query that pulls all the items from a database except two here is what I am using: $query = 'SELECT DISTINCT Type FROM Products WHERE Type != "System, Upgrade"'; This populates the drop down box with all including the System and Upgrade. What do I have to do to make this work? Thanks Kevin Internet Designer O'Dell Web Design http://www.odellwebdesign...

  • Finding Rows With Common Items in Mysql-general

  • Hi all, Lets say I have a table with two colums: 'orderid' and 'productid'. This table contains line items for purchases in a store where a purchase could be 1 or more line items (1 or more rows). A typical order may look like this: orderid | productid 12345 | 9876 12345 | 6789 12345 | 7698 Is there a simple way to query the table to pull orders which have 2 or more products...

  • Finding Missing Items In A Series in Mysql-general

  • I have a mysql table like this mysql> describe reports; +---------+---------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+------------+-------+ | rptdate | date | | PRI | 0000-00-00 | | | id | int(11) | | PRI | 0 | | | summary | text | | | | | +---------+---------+------+-----+------------+-------+ ...

  • Multiple Items In An ALTER TABLE Statement in Mysql-general

  • Was in the midst of doing something today and I attempted to drop a number of columns in a table with the following: ALTER TABLE tmp DROP COLUMN col_1, col_2, col_3, col_4; Unfortunately MySQL gave me an error reading: ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'col_2, col_3, col_4...

  • Unique Items From All Columns, Very Slow in Mysql-general

  • Hello, I have been attempting to uncover a fast method for retrieving unique items from every column in a table. The table has approximately 60 thousand rows. Each row has an index associated with it, and running 'explain select distinct class from dlist' shows that it is using the index. However, since there are 24 columns, running that select query for each column takes about 4 long seconds...

  • Enum Column - Possible Items From Another Table in Mysql-general

  • Dear all, I am trying to create table "B" with a column what is "set" type, and the possible values are all that which occur in a given column from table "A". That is possible values = select A.id from A group by A.id create table B (B.enum set "possible values") Of course, I'd like to do this in an automated way, and from mysql. It is not a big deal to do it from shell script or tcl ...