Ordering tuples with mySQL / PHP

Ok, I'm trying to put a little photo gallery together and I've got almost everything working except the order...

I made an extra col in the database called "order" so each image has an order attached to it, I did this because I want to be able to have say the 5th image I upload come up at the top of the list of thumbs on the site, so when I do the mysql select I just do [PHP]ORDER BY order[/PHP]

My problem is this, say I want to change the order of the 6th to the first, is there any easy way to update the numbers of the other images without having to update each tuple induvidually?

Maybe I'm going about this completly wrong... any suggestions. Base case, I just need to be able to specify the order I want a record to appear in. Can't be ordered from the autoincremented key.

My take on this is you have a 'flag' that sets the order. Lets say you have 5 images, and image1 --> flag = 1, image2 --> flag = 2, etc... image5 --> flag = 5. This would be a default case. Now you want to reorder them so it goes:
image5 --> flag=1 etc.... image4 --> flag=5

So if you think about it all your are doing is re arranging the order by incrementing or decrementing a value. A loop can easily solve that problem. I would have to think about it a bit if you want to actually re order in a random order, or adjust all of the orders in one move.

But the logic would go like this for the first case:
1. Query db for all thumbnails.
2. Put this result into a select/option html list, then lets you select which image you want to be first.
3. Use a $_GET var based on that select to decide which image you want to put first.
4. Run the queries in a loop:

Rough idea:
First query updates the selected thumbnail so it has a order flag set to '1'.
--> mysql_insert_id(); --> grabs last run query ID.
Second query updates the rest of the thumbnails in a loop, except the last item (note grabbed ID) and increments the order flag by 1.

Finito.

Hope that makes sense!

Yeah, that makes a lot of sense. A little "for" loop with an incrementer for the images that will now be after the selected flags new position.

It would be nice to be able to change the order of multiple images at once, but then other factors need to be considered.

I'll just make it a "one change at a time" and it should be golden!

Thanks!

I will give it some thought and see if I can think of an easy way to do that....

Let me know if you need any help with any of it.