Renumber row in sequential order using PDO

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
happypete
Forum Newbie
Posts: 3
Joined: Thu Mar 24, 2011 7:56 am

Renumber row in sequential order using PDO

Post 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
User avatar
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

Post 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.
(#10850)
Post Reply