On Tue, 05 Feb 2008 15:30:49 +0100, Il tulipano nero
<bastiglia@vivlafrance.fr> wrote:

> Hi,
> I've a little problem with the ID field.
> Every time that I delete a news from my website database the ID
> associated to that news remain empty and unused because the ID pointer
> continues to go on.
>
> I don't want leave empty the ID associated with the deleted row and I
> wish to use it again for the next news. How to do it?
>
> For example:
>
> ID NEWS
> 1 *****
> 2 *****
> 3 *****
> 4 *****
>
> we suppose that from my website panel I want to delete the news
> associated with the ID n.3.
>
> This would be the new result:
>
> ID NEWS
> 1 *****
> 2 *****
>
> 4 *****
>
>
> Now if I add a news to the dbase it will have the ID n.5 but I wish that
> the it take the place of the Id deleted (the n.3) to avoid a too many
> irregular ID numeration.
>
> What kind of query I need?
> I suppose something like "ALTER TABLE newstable auto_increment=3" but in
> this case is there the risk that following news overwrite the row
> associated with ID n.4? I wish that the pointer skips automatically on
> to next available ID and doesn't overwrite anything.

One normally doesn't want this. Take this case:
1. You have news on your website.
2. The news is accessable by id, for instance: http://example.com/news/3
3. Someone links to that particular ID 3, which atm is a story about a new
plasme television.
4. You decide to delete news#3.
5. You add new news, with ID 3, and now this is about a stroll through a
meadow you once took
6. A lot of people will visit your site thinking they will read something
about a television, and instead of telling them this news no longer
exists, you present them with your meadow stroll. Not something they were
clicking on the link.

Result: disappointed visitors, content heavily changing to unrelated
topics with the same URL, not at all good advertisement for your site.

So, I'd ask you this: what is your _exact_ problem with 'irregular ID
numeration' as you put it? It should not relate to anything noteworthy for
the user except being the definitive key for that news, and that news
alone. I you wish to have a kind of enumeration of newsitems, not related
to the database, but just as a list, you can either:

1. In a HTML context, just use a ordered list with decimal type.
2. Create the numbering with the script you're accessing the DB with.
3.
SET @myvar := 0;
SELECT @myvar := @myvar + 1 as 'psuedoid',news FROM news ORDER BY id
--
Rik Wasmus