Reorder AUTO_INCREMENT

[sftrprod]

Hi,
On my web site I've got a MySQL table of news with an AUTO_INCREMENT column that contains the ID of each news article. But when I delete an article it messes up the order.
1, 2, 3, 4, 5
after deleting '4' becomes:
1, 2, 3, 5
If this happens, my 10-articles-per-page thing won't work and may give errors.
How do I get around decreasing the ID of every article above the deleted article and decreasing the AUTO_INCREMENT by 1 without using a huge looping SQL query. Thanks.

[acorbelli]

You shouldn't have to re-order the auto-increment. You can change it using a MySQL query so that the next news article entered will have the ID that you removed and then it will return to normal operations.

But better than that, your script for grabbing the news articles shouldn't be grabbing ID 1-10, just grab a result set of 10 rows. Use the same MySQL query (to avoid looping) (I'm assuming you're using an 'WHERE ID <11' type query) and then simply fill in the gaps by goign to a small loop and grabbing more until you hit 10.

So, use the same query then check to see if you have ten rows. If not, see how many more rows you need and form another query to grab the remaining rows. Check again (to be sure you didn't skip over more), repeat until you have ten rows.

[chazzy]

you could issue an update to the table after the delete
"UPDATE your_table SET id=id-1 where id > the_deleted_id"