Renumber row in sequential order using PDO

happypete

I have a table with an ID column which is the Primary key and auto incremented. I have another column named ORDER that contains a number. This number is used to define the order in which the row data is displayed on a page.

When I delete a row, the ORDER column will have a gap and I would like to renumber the subsequent rows to remove the gap. ie:

I want to renumber the rows:
ID ORDER
1 1
2 2
4 3
5 4

When I insert a new row I want the ORDER row to be given the NEXT sequential number

How do I do that using PHP PDO?

Thanks

dagon

this is bad db practice, the gap makes no difference when you do table sorting

happypete

dagon;10976874 wrote:
this is bad db practice, the gap makes no difference when you do table sorting

Its for the order of a photo gallery, the user enters the order that images show in changing the ORDER row not the ID, I then want them to update sequentially..

johanafm

It still makes no difference. Let's assume 3 images

You retrieve them with "ORDER BY o ASC", which gives you the images with id in this order
1, 3, 2.
If the user deletes image with id 3, you now have

id  o
1  1
2  3

And you get your images in order 1, 2 which is still correct.

What you need to do is create logic that deals with the user assigning a new order value to a specific image. But this logic will be the same wether or not you always have a new order value for an image collide with that of another image (like you will if you resequence your ordering to always be one greater than the previous value), or just sometimes (like you will if you do not resequence your order numbers. Only if you never get a collision (which you would not be able to guarantee anyway) could you make your reordering logic simpler.

This is based on my intuition, but it should be possible to prove this using induction.