Reviews, updates and in depth guides to your favourite mobile games -
Forum Home Latest Posts Search Help Subscribe

Ordering tuples with mySQL / PHP

Page: 1 Reply
May 17th 2004#150715 Report
Member since: Jun 5th 2003
Posts: 93
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.
Reply with Quote Reply
May 17th 2004#150718 Report
Member since: Nov 26th 2001
Posts: 2586
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.


Hope that makes sense!
Reply with Quote Reply
May 18th 2004#150734 Report
Member since: Jun 5th 2003
Posts: 93
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!

Reply with Quote Reply
May 18th 2004#150741 Report
Member since: Nov 26th 2001
Posts: 2586
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.
Reply with Quote Reply
Page: 1 Back to top
Please login or register above to post in this forum