sorting after updating

[mptolman]

Okay, I'll try to explain this the best I can...

I made a simple news database where I can enter / edit / delete news entries on my site. Now, I have an id field as my primary key. If I delete an entry, the id field doesn't get replaced. For example, if I deleted the post in ID field 7, the other elements don't fill up that 7 field, and there is no id field 7 anymore. How do I make so my table stays in numerical order without leaving empty fields?

[valshooter]

The best way of doing this is:

1. remove auto_increment attribute from the table
2. before inserting new row perform "select max(id)" query
3. apply id to the news automatically max(id)+1

mysql auto_increment MEANS UNIQUE id for each row (no matter if it was deleted or not)

N.B. for my method (max(id)+1). Maybe LOCK table would be more stable if it's possible to update your table by adding 2 news simultaneously