A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
|Updating display order of multiple MySQL rows in one or very few queries||#1|
I have a table with say 20 rows each with a number for display order (1-20).
SELECT * FROM `mytable` ORDER BY `display_order` DESC;
From an admin area you can drag the rows around or type a new number manually for each row.
Surely it(s not good to loop over an UPDATE query for every row, what(s an alternative in one or very few queries suitable for updating one cell in 20 rows or even more, 50-200+?
Edit: A lot of good responses and ideas. I might expand on the ideas I(ve considered so far:
UPDATE `my_dispaly_order_table` SET `display_order`=(1,9,2,6,23(;
Update each row individually: This is what I was trying to avoid but it would only be changed very infrequently so 20-30 calls in one hit once a week or month might not be a problem so simply calling UPDATE on each row is what I usually do:
UPDATE `mytable` SET `display_order`=(1( WHERE `rowId` = 1;
UPDATE `mytable` SET `display_order`=(2( WHERE `rowId` = 9;
UPDATE `mytable` SET `display_order`=(3( WHERE `rowId` = 2;
UPDATE `mytable` SET `display_order`=(4( WHERE `rowId` = 6;
UPDATE `mytable` SET `display_order`=(5( WHERE `rowId` = 23;
posted date: 2009-04-14 10:04:00
|Re: Updating display order of multiple MySQL rows in one or very few queries||#3|
Add an id (or other key) to the table, and update where id (or key) = id (or key) of changed row. Of course, you(ll have to make sure that either there are no duplicate display_order values, or that you(re OK with ties in display_order displaying in any order, or you(ll introduce a second, tie-breaker to the order by list.
posted date: 2009-04-14 10:09:00
|Re: Updating display order of multiple MySQL rows in one or very few queries||#4|
You could try to wrap it into a few statements, I don(t think it(s possible in a single one. So for example, let(s say you are going to update the 10th row. You want every record after 10 to be bumped up.
UPDATE table SET col=col+1 WHERE col > 10UPDATE table SET col=10 WHERE id = X...
But it(s really tough to roll in all logic required. Because some records maybe need a decrement, etc.. You want to avoid duplicates, etc..Think about this in terms of developer time vs. gain.Because even if someone sorts this once per day, the overhead is minimal, compared to fixing it in a stored procedure, or pseudo-optimizing this feature so you don(t run 20 queries. If this doesn(t run 100 times a day, 20 queries are perfectly fine.
posted date: 2009-04-14 10:12:00
|Re: Updating display order of multiple MySQL rows in one or very few queries||#5|
You could delete an re-insert all the rows - that would do the whole operation in just two queries (or three if you need to select all the data). I wouldn(t count on it being faster, and you(d have to do it inside a transaction or you(ll be heading for your backups before too long. It could also lead to table fragmentation.A better option might be to record each change as a history then do something like this:Example, position 10 is moved down two to 12th
UPDATE table SET display_order = display_order -1 WHERE display_order BETWEEN 10 AND 12UPDATE table SET display_order = 12 WHERE row_id = [id of what was row 10]
posted date: 2009-04-14 10:14:00
|Re: Updating display order of multiple MySQL rows in one or very few queries||#6|
You should first ensure that the column has no UNIQUE index, otherwise mysql will tell you that the constraint is broken during the query. After that you can do things like:
-- Move #10 down (i.e. swap #10 and #11)UPDATE mytable SET display_order = CASE display_order WHEN 10 THEN 11 WHEN 11 THEN 10 END CASEWHERE display_order BETWEEN 10 AND 11;-- Move #4 to #10UPDATE mytable SET display_order CASE display_order WHEN 4 THEN 10 ELSE display_order - 1 END CASEWHERE display_order BETWEEN 4 AND 10;
But you should actually ensure that you do things in single steps. swapping in two steps will result in broken numbering if not using ids. i.e.:
-- Swap in two steps will not work as demostrated here:UPDATE mytable SET display_order = 10 WHERE display_order = 11;-- Now you have two entries with display_order = 10UPDATE mytable SET display_order = 11 WHERE display_order = 10;-- Now you have two entries with display_order = 11 (both have been changed)
And here is a reference to the CASE statement of mysql.
posted date: 2009-04-14 10:20:00
|Re: Updating display order of multiple MySQL rows in one or very few queries||#7|
Collect the new order in a temporary variable and put a "save this order" button to the admin area. Then save the order for the rows with one round.You(ll get better response times, undoable changes, fewer modified rows (because in low level in the dbms, practically no updates used to be possible, but save a new instance of the whole row and delete the old one).After all, it would be a lower cost solution for a whole reordering and would save some coding on the update optimization.
posted date: 2009-04-14 11:41:00
|Re: Updating display order of multiple MySQL rows in one or very few queries||#8|
I(m thinking about this problem, and the solution I came up is having a decimal number as order and change the number of the item change for a number between the next and the previous item
Order Item----- ----1 Original Item 12 Original Item 23 Original Item 34 Original Item 45 Original Item 5
If you change the item 4 to the 2nd position, you get:
Order Item----- ----1 Original Item 11.5 Original Item 42 Original Item 23 Original Item 35 Original Item 5
If you change the item 3 to the 3rd position, you get:
Order Item----- ----1 Original Item 11.5 Original Item 41.75 Original Item 32 Original Item 25 Original Item 5
Theoretically there is always a decimal between two decimals, but you could face some storage limits.This way you only have to update the row being re-ordered.
posted date: 2009-05-07 06:49:00
|Re: Updating display order of multiple MySQL rows in one or very few queries||#9|
If you need to drag you rows, this is a good implementation for a
.Having your rows ordered with a
means that you will update at most
rows at a time -- even if you move the whole block (as long as it(s contiguous).Create a table of your rows like this:
CREATE TABLE t_list ( id INT NOT NULL PRIMARY KEY, parent INT NOT NULL, value VARCHAR(50) NOT NULL, /* Don(t forget to create an index on PARENT */ KEY ix_list_parent ON (parent))id parent value1 0 Value12 3 Value23 4 Value34 1 Value4
and use this
query to select the rows in order:
SELECT @r := ( SELECT id FROM t_list WHERE parent = @r ) AS idFROM ( SELECT @r := 0 ) vars, t_list
This will traverse your linked list and return the ordered items:
id parent value1 0 Value14 1 Value43 4 Value32 3 Value2
To move a row, you(ll need to update the
of the row, the
of its current child, and the
of the row you(re inserting before.See this series of articles in my blog on how to do it efficiently in
:Sorting lists - how to select ordered items from a linked listSorting lists: moving items - how to move a single itemSorting lists: adding items - how to insert a single itemSorting lists: deleting items - how to delete a single itemSorting lists: moving blocks - how to move a contiguous blockSorting lists: deleting blocks - how to delete a contiguous blockThere are lots of articles because there are some issues with row locking which should be handled slightly differently for each case.
posted date: 2009-05-07 07:15:00
|Re: Updating display order of multiple MySQL rows in one or very few queries||#10|
I guess two issues with this is you still may need to update a few fields but at least not all... it might get a little hairy after time! I had a similar concept but started in units of 100 so it at least had ints instead of decimals but not too different... I'd say you'd still want to update the order every now and then to be normal consistent numbers. Thanks for the contribution.
posted date: 2009-05-07 18:10:00
|Re: Updating display order of multiple MySQL rows in one or very few queries||#11|
This seems very complicated... my main thought is it's adding more work instead of updating the order of one item it updates 3 fields for every order change...
posted date: 2009-05-07 18:13:00
|Re: Updating display order of multiple MySQL rows in one or very few queries||#12|
It may not work so well for frequent sorting so you keep adding or shifting items to the top (news items for example) so you'd end up with 0.1, 0.0001 - but then I guess it can go into the thousands... although it seems a slightly left-field idea I'm actually leaning towards this solution...
posted date: 2009-05-07 18:16:00
|select page: « 1 2...»|