StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUpdating display order of multiple MySQL rows in one or very few queries
text
Body
copied!<p>I have a table with say 20 rows each with a number for display order (1-20).</p> <pre><code>SELECT * FROM `mytable` ORDER BY `display_order` DESC; </code></pre> <p>From an admin area you can drag the rows around or type a new number manually for each row.</p> <p>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+?</p> <hr> <p><strong>Edit:</strong> A lot of good responses and ideas. I might expand on the ideas I've considered so far:</p> <p><strong>One array string</strong>: I could have the order in a string listing the unique row IDs in the order I want - eg rows 1,9,2,6,23. When the order is updated, a hidden field updates with JavaScript and adds that to the database or a text file when complete:</p> <pre><code>UPDATE `my_dispaly_order_table` SET `display_order`='1,9,2,6,23'; </code></pre> <p><strong>Update each row individually</strong>: 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:</p> <pre><code>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; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload