pushing mysql rows around

alright... aside from simply getting rows from a query and then updating those rows, and then putting the old row info into the next row, is there any way to jsut, make the row scoot down? So that when I update a row, it doesn't replace it, jsut pushes that row down, and makes a new row?
And if not...
I want my users tobe able to have six items in their personal database info. I want them to be able to change these items, adding a new one, pushing the other 5 items down, which would delete the si basically. I had done this with complex queries and passing from the url, but their must be a better way!

any one have answers to any of this? Thanks!

You just need to give each row a numeric position column whose value the users can change at will; then order by position when selecting out of the database.

well I don't want the users to change the order, not as though it were something they even know is changing. Just so when they upload a picture, it would push the other pictures down the lsit, making the most recent pic uploaded the first pic loaded... I think i will have to do this how I had done it before, passing a lot of variables through the url and such. But maybe I misunderstood you. Thanks at any rate.

Nah, you could still add an auto_increment field and order by that field descending when you display the pictures. Or if you've already got an auto_increment field, maybe a timestamp. No need to move whole sets of rows, at any rate.

hmm, that's true... but will all of those extra old entries get a bit big and fill the db? Although I have been meaning to look into that, I can't remember what it was called, but I think it sort of cleaned out of db entries and stuff... is there a way to do that.... maybe jsut a script that checks the date of an entry, oh wait I know, maybe jsut does a query between the 1st entry and the 7th to last entry or something... is that possible? Or the best way to go about it? And then the old pics, I suppose that result from that query can just be used to delete them at the same time... am I on the right track here?
thanks for your help, you've got me thinkin about it again, heh.

Yeah, you could either delete old entries upon insert or have a script that runs weekly or nightly that deletes old entries. And I wouldn't even worry about that unless you expect a lot of activity and have severely limited db space.

A small question from a newbie about this. How do I do this? I assume I can't have the same number more than once, so do I have to change EVERY number of EACH row every time I alter one number? That's much too big a query, isn't it?

If your position isn't an auto_increment field or a unique field, you can have duplicate numbers and just do a second order by for name or another criterion in case there are multiples of the same number. Unless you've got somebody organizing a whole lot of information, this wouldn't require too many queries. In fact, if you follow the lead of the Nukes and have an interface that allows you to push items up or down in a stack, you're only ever changing two rows for a given item shift. (increment the number of the one and decrement the one adjacent to it).

You're right, I had just realised it, too. I just need to add one more field for the "order number", instead of using the auto-increment "id" field. Thanks for your reply.