mysql last ID

[Anon]

I think this is not really good idea.
Imagine the situation when the last ID in the players table is 1700

you insert new row and it gives ID = 1701

for example you use it 2 generate link

see "SOME"player on

href="yoursite.com/yourscript.php?ID=$ID"

user sees this link and copy it for future use

some day further you delete row with id=1701

now the last is 1700 again

you insert new row it gives id=1701 again

What we have? the same link with..wrong content.
I've wasted the hell lotta time with bugzzz when wroting forums with posts IDed this manner

I use where it is possible AUTO_INCREMENT.
Where it is not I use file last.id contains the last id inserted. Everytime I insert new row, I make ++ for content of this file.

[philsown]

One idea is to not physically delete rows just set them as invisible.

instead of:
delete from forums where msg_id = 1700

say:
update forums set status = 'd' where msg_id = 1700;

Then make your query on the message page require the message to be active:
select * from forums where msg_id = 1700 and status = 'a';

Also you would need to change your insert statement to default to 'a' (active) or 'p' (pending if there was an approval process) or 'w' (whatever you wanted). This could be a global variable:

$forum_defaults['new_status'] = 'a';

$sql = "insert into forums ( message, name, status )
values ( '" . $message . "', '" . $name . "', '" . $forum_defaults['new_status'] . "');

The issue you mention with auto_increment only seems to happen when all rows are deleted from a table. I've not had a problem with a recent version MySql. If I have 2 rows (ids 1 and 2) and delete 2, then insert, I end up with ids 1 and 3, which is right. Maybe this is a recent improvement with MySql? When I drop all rows the id starts at 1 again.

Phil