move record in a sort

[jbsmith969]

I'm trying to figure out how to go about creating the ability to move a record within a sort. Here's an example of the situation I'm trying to figure out...

I have a column named sort_ID in MySQL that auto increments.
Let's say I have a PHP page that displays all 35 records from a table and I'm sorting by sort_ID.
I want to change the record where sort_ID = 24 to sort_ID = 3 and then increase all the records by 1 that follow until the record where sort_ID = 23.
I also do not want sort_ID to have any skipped numbers. Everything should be consecutive.
I would also need this sort_ID column to renumber itself if a record were deleted to keep everything consecutive.

I was thinking of somehow doing this with a calculation in Javascript that controls pull down menus that has all of the available sort_ID's using an onChange='renumber()' function or something like that. Then I would have to figure out how to update each and every record through a loop of some sort. Another thought was to have a hidden field that contains what the current sort_ID is and the pull down menu would be the new sort_ID. Then I would pass those parameters to a PHP/MySQL function that does the renumbering somehow through the database. Anyway, if I new a better way, I guess I wouldn't be here posting this.

I would think that this type of need isn't that uncommon, so I'm hoping there's some magical function or better way one of you geniouses can point out. I'm still pretty new to the area of PHP/MySQL.

[AndyB]

Whatever else you do, forget about renumbering auto-incremented ids so "it looks nice". The whole point of an index is so you can conveniently cross-reference records. Your (future) cross-referencing will be garbage if you start renumbering autoincremented IDs.

Instead, use a counter variable when displaying records if you want them to appear to have numbers in an order.

[fenway]

[!--quoteo(post=379058:date=Jun 1 2006, 09:43 AM:name=jbsmith)--][div class=\'quotetop\']QUOTE(jbsmith @ Jun 1 2006, 09:43 AM) [snapback]379058[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I have a column named sort_ID in MySQL that auto increments.

I also do not want sort_ID to have any skipped numbers. Everything should be consecutive.

I would also need this sort_ID column to renumber itself if a record were deleted to keep everything consecutive.
[/quote]
AndyB is correct -- don't even think about playing with this value! In fact, pretend it doesn't exist at all. It's not for you to know and/change, it for the DB and the DB alone. These values are used to uniquely identify a record, NOT to keep a count of how many records there are -- so playing with this number is going to be very, VERY bad. If you want a counter, make a counter, but don't abuse the UID.

[jbsmith969]

I wasn't thinking of actually changing the record ID. That's why I would have a whole different column named sort_ID designated for sorting purposes only. Is sort_ID a reserved name for MySQL or something? Is that why you guys are thinking I want to change the record ID?

In other words, I have one column named shot_ID that is the auto incremented record number and I would have an additional column named sort_ID. I guess auto-incrementing doesn't work for sort_ID now that I think this out, so I would have to do a PHP calc to generate those numbers, but leaving that aside, do you guys know of a good way or of something that will assist in allowing me to take a record where sort_ID = 23 (not record ID), change it to sort_ID=3, then fix all the records where the sort_ID >=3 and sort_ID < 23 by adding 1 to all of their values.

Or is there some other better way to do this. Surely I'm not the first person in the world that wants to have my records sorted in a certain way and have the ability to change the position of a record in the sort.

[fenway]

There's nothing wrong with storing a sortorder -- I do it all the time, and of course, you can calculate it and update it as you see fit. We were alarmed because you said "auto-increment".

[jbsmith969]

OK, but what would be the best way to do that? I'm not quite sure how to go about it. Is there possibly a function that MySQL has to do this. I've tried searching around on how to do this, but I can't seem to find anything and I'm not ever sure what to search for.

[fenway]

There is no MySQL function, because how you want the sortorder to be determined is up to, and MySQL can't guess. You'll have to issue these update statements yourself -- shouldn't be hard to find all sort_ids between a given range and update their sortorder, but I don't know how robust this is.