Changing Index in MySQL

[stephanie]

If someone could riddle me this - it would be a great help.

I have a mySQL table that is ordered by an ID number, which is auto increment. The program I am supporting REQUIRES the items to be in
order of ID number. However, now, I need to add an item to the middle of the table (let's say before id 9 and after id 8, for example. I need an item to be added between those).

Besides renumbering the index/id from the bottom up in the table and inserting an item, is there any way to do this in mySQL?

Yep, I realize the program shouldn't be coded to depend on the database table never changing...
Thanks,
S.

[Pig]

Yeah, but it is a bad situation for sure.
Add another column called 'order' or something. To intialize it, do a query to go through all the rows ordered by ID. As you loop through them, increment a value that you are adding to the order column.

Then, when you want to add an item, give the column an inbetween value (9.5 or whatever). Loop through the rows, ordered by the order column, and update the id incrementally.

Hope that made sense. Course the real fix is to fix the script that uses such a crappy method in the first place.

[stephanie]

Thanks, that helps!

S.