Renumber row in sequential order using PDO

PHP
1

lI 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

2

The next sequential number as in leaving the gap, so if you remove ID=4, you just put it at 5 anyways?

Or the next number as in filling the gap, so if you remove ID=4, you should get 3 for order?

3

If you delete ID 4

You have
ID ORDER
1 1
2 2
3 3
5 4
6 5

I need it to reorder starting at 1… ie 1,2,3,4,5,6

4

You need ID to reorder?

If that’s the case, I’ll warn you now: that is a BAD idea. You never ever want to change your id value.

Also, if you recorder them, doesn’t that mean ORDER always equals ID, so it doesn’t really matter?

Can you elaborate a bit more on what you’re trying to do. There is likely a better way to do what you’re trying to accomplish.

5

No, not the ID, that stays the same, the ORDER row will be renumbered… It wont be the same as the ID because ID may be deleted

6

Okay, now that I know the question. =p

The simplest way to do this would be to do a SELECT query and get all of the elements, ordered by ID. Then just go through and do an UPDATE on each one, changing the ORDER to an ever incrementing value.

However, there are two potentially better ways to do this:

  • If the ID and ORDER are always related, then why have order? Just arrange stuff based on ID.
  • When you remove a single row, you could do an UPDATE SET ORDER = ORDER - 1 WHERE ORDER > removedOrder.

If I knew more what you were doing with this I could help a bit more.

7

The idea is that the user enters a number to determine the order of the images (ORDER) so the ID will not be in any specific order,

ID ORDER
10 1

The 10th ID would be in first postions

So I need to Select the query in order of ORDER ASC then renumber ORDER 1,2,3,4,5, etc…

8

That’s what I figured. In that case, this:

  • When you remove a single row, you could do an UPDATE SET ORDER = ORDER - 1 WHERE ORDER > removedOrder.

Is how you would want to do it. It would just shift everything down to fill in the gap you just created, preserving the order but giving you no gaps.

9

A better solution would be to not store the order in the database at all but to generate that field as you read the records. That way the order values are automatically updated when a record is deleted.

10

That wouldn’t work with user-controlled ordering though… would it? If it would, can you elaborate?