Page 1 of 1

Renumber row in sequential order using PDO

Posted: Thu Mar 24, 2011 9:06 pm
by 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

Re: Renumber row in sequential order using PDO

Posted: Fri Mar 25, 2011 3:18 am
by Christopher
You will need to update all the rows with order value higher than the order value in the row you delete. However, you don't need to do this if you use SQL's ORDER BY. It will sort them correctly, even with gaps. If you need sequential numbers displayed then PHP can do that as you generate the HTML. For adding records, you might want to set the order value to the value of the autoincrement ID. There is no value higher in the table.