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

[btherl]

For renumbering:

 

UPDATE table SET order = order - 1 WHERE order > deleted_order

 

For getting the next number

 

SELECT max(order) + 1 FROM table

 

It would be advisable to have an index on order to make finding the maximum fast.

[happypete]

For renumbering:

 

UPDATE table SET order = order - 1 WHERE order > deleted_order

 

For getting the next number

 

SELECT max(order) + 1 FROM table

 

It would be advisable to have an index on order to make finding the maximum fast.

 

Thanks, but I'm not sure how to implement them, I tried this but it doen't work.. (RANK is the ORDER row)

 

$order = 'SELECT max(rank) FROM photos';
  
  $sql = 'INSERT INTO photos (description, src, tn_src, rank) VALUES (?,?,?,?)';
  $stmt = $db->prepare($sql);
  $stmt->execute(array($_POST['description'], $imagename, $imagename, $order));

 

 

 

[btherl]

Sorry, I usually don't use PDO .. if you have used a select query elsewhere in the script, you can copy the way that one works.