Reordering A column

[Xtremer360]

I'm trying to figure out how I should handle this. When a create new title form is submitted and it sends the post data to the database it gets the max value of the sort_order field and adds 1 to it to create the sort_order for the new entry. What I would like it to do is when a title gets deleted I'd like for it to make sure there are no gaps in the number chain for the column; and if there is, then correct it by adjusting the numbers accordingly moving the numbers upward. For example if the sort order column looked like this: 1,2,3,6,8... it would then make it 1,2,3,4,5.

[gizmola]

You could write a function to do this.  The basics would be:

- BEGIN TRANSACTION

-SELECT FOR UPDATE ... criteria ... ORDER BY sort_column

-Have intialize counter variable to 1

-Foreach through result set.

-- Compare counter variable to sort_column and if != update sort_column of row

 

Only caveat is that you need a database engine that support transactions.  Assuming mysql, this means your table needs to be innodb.  If you omit the select for update, you could still write the function, but you could encounter a concurrency issue if 2 people were adding rows at roughly the same moment.