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
Renumber row in sequential order using PDO
Moderator: General Moderators
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Renumber row in sequential order using PDO
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.
(#10850)