Have a table photos
photos.id photos.user_id photos.order
A) Is it possible via a single query to group all photos by user and then update the order 1,2,3..N ?
B) added twist, what if some of the photos already have an order? Make sure that the new photos.order never gets repeated and fills in ant orders lower or higher than those existing (as best as possible)
My only thought is just to run a script on this and loop through it and re'order' everything?
photos.id int(10) photos.created_at datetime photos.order int(10) photos.user_id int(10)
Right now data may look like this
user_id = 1 photo_id = 1 order = NULL user_id = 2 photo_id = 2 order = NULL user_id = 1 photo_id = 3 order = NULL
the desired result would be
user_id = 1 photo_id = 1 order = 1 user_id = 2 photo_id = 2 order = 1 user_id = 1 photo_id = 3 order = 2