Updating sequence of numbers (display order) using PHP & MySQL

Question

I have a int(11) DisplayOrder column in my database of slideshow items which tells my code what order to display the items in. An admin can update the order with a form, changing, for example, number 20 to number 3. Without forcing the user to manually find number 3 and swap it with 20, is there an easy way to update the sequence of numbers in the database? Either using MySQL, or changing items in in array() before sending a query.

I've searched and searched, and there doesn't seem to be many solid answers. The issue is that I currently have 400 rows, and that number will grow. I don't really want to have to do 400 updates every time the order changes. Is there a better way? Are there existing scripts?

It's easy to write yourself, but do you want to swap or insert and increase everything after the insert? You can also look at a client-side solution like jQuery sortable and rebuild the entire list on submit. — jeroen
– jeroen, Commented Jul 17, 2012 at 0:19
@jeroen Insert and increase everything after the insert. For example, if someone adds a new item to the slideshow, it will default go to the number 1 position. I will then have to increase every single row. Or insert an item into number 2, it will then have to increase everything after row 2. — James Glass
– James Glass, Commented Jul 17, 2012 at 0:23
If you want to increase all numbers that come after the new insert, I'm afraid the only way is updating all records after that one... — jeroen
– jeroen, Commented Jul 17, 2012 at 0:30

u25891 · Accepted Answer · 2012-07-17 00:23:40Z

2

Why do you need to fully resequence the DisplayOrder values?

An ORDER BY clause will work just as well if there are duplicate values. To maintain a stable sort, you can order by a secondary column such as item name or id.

answered Jul 17, 2012 at 0:23

u25891

6114 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

James Glass Over a year ago

For most situations, ORDER BY on duplicate DisplayOrder values, and then a secondary sort on an auto-increment column (ID) would work (assume DESC). But, what about a situation where a lower ID has a duplicate DisplayOrder value as a higher ID, but the admin actually wants that lower ID displayed as the higher display order? It's probably rare, but this solution doesn't allow for that, as far as I can see.

James Glass Over a year ago

Using your solution, since it seems to be the least resource intensive, here's what I'll do: add a DisplayOrderSecondary field which will be set to 1 upon INSERT, and I'll run a query which sets all other rows that have the same DisplayOrder number to have DisplayOrderSecondary = 0. Then when pulling items from the DB, ORDER BY DisplayOrder ASC, DisplayOrderSecondary DESC, so the newest duplicate will always be displayed. Thanks!

Collectives™ on Stack Overflow

Updating sequence of numbers (display order) using PHP & MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related