Forum Moderators: open
I have a table that will allow a user to sort a potentially large list of photos. Each photo has its own record in the table and there is a web interface to allow the user to specify the sort order of each one - this value is stored in its own "sort_order" column, separate from the primary key.
A user can have thousands of photos and I need to make sure the sequence is maintained properly - sequential integers with no gaps. It's easy to add a record, but I'm curious about the best way to insert one.
If a user has 3,000 photos and they add a new one into position #2, that would mean to maintain the order I would need to increment the "sort_order" column of positions 3 thru 3,000 by 1.
I feel like running almost three thousand update queries might not be the best solution to such a seemingly simple problem... or is it?
Any advice would be greatly appreciated.
Thanks, Brian
Don't run 3,000 update queries, run one UPDATE query and modify 3,000 rows, or in your example, 2,998 rows ;)
UPDATE myTable SET sort_order = sort_order + 1
WHERE imagePrimaryKey = $someNumber AND sort_order >= 3;
-Brian
