Make ID-column decrease when deleting?

[micke]

I'm doin a news-publisher.. It's using a column with ID's. And when deleting one ID, I get a gap.. Say I've posted 7 news, and when deleting news #5, I got news 1,2,3,4,6,7 left.. Is there a way to make that 6 transform into a 5, and the 7 to 6?

[[deleted]]

Not without you manually updating the column.

The whole point of having an ID is that you can find an item by it's ID. If you start changing the ID's around, you can not use the ID to find records anymore, because you'll never know which record belongs to any ID.

[Anon]

Implement the news as a "linked list" if you want to maintain the specific order of items.

create table news( id int..., nextItem id...);

Where nextItem points to the next news in list and the first item points to NULL.
This way you only need to maintain ONE field in ONE record when you delete.

Unfortunately you cant fetch this model in one query, you'll have to do your doquery for every record you fetch.

[Anon]

Why are so many people afraid of gaps ? Primary keys are used internally. They're usually not intended to be printed. You want to display the number of the news ? Just do this :

$q=mysql_query("select text from news order by ...");
while(mysql_fetch_array($q))
{
$nb++;
print "$nb : $q[text]";
}