renumbering mysql records

[PhoenixDown]

k i have a mysql db with records similiar to this:

1 test1 test1 test2
2 test2 test2 test2
3 test3 test3 test3

i made the delete code for it so i was wondering if somebody could help me with a code that will renumber records when one get deletes. so if "2 test2 test2 test2" gets deleted the code would make it:

1 test1 test1 test2
2 test3 test3 test3

thanx in advance

 

[KarveR]

If the id is auto_increment the table will not re-use deleted id's ***************************************
Party on, dudes!

 

[DivineDragon]

i dont understand what you mean. divine_dragon9999@yahoo.com

 

[KarveR]

The record number 1, 2, 3 what type of field is it? how is it set up in mysql?
is it a plain INT field or does it have the auto_increment flag set? ***************************************
Party on, dudes!

 

[DivineDragon]

no, no auto_increcment

it has one int(10) for post ids, and for the rest varchars.

the one i posted above was just an example. divine_dragon9999@yahoo.com

 

[KarveR]

How many records are you likely to have?
Renumbering a database in this way will be very intensive and could be overcome by using an auto increment field and renumbering the results on the fly. ***************************************
Party on, dudes!

 

[DivineDragon]

I don't know... just depends on how many people post. divine_dragon9999@yahoo.com

 

[KarveR]

If its likely to be busy don't even think about renumbering, really.

I post on quite a few boards, one is for a group of 40ish people and the posts run into thouands, to renumber them would be just plain silly.

Keep an id that is static and it will allow you to edit or delete etc etc.
No-one needs to see the post id as you can number posts as you display them in php. ***************************************
Party on, dudes!

 

[DivineDragon]

I wanted to do the same thing with members, so maybe i'll just forget about the post one. can you show me how to do it for the member table?

theres 12 columns, right now on my test script, theres 3 members. divine_dragon9999@yahoo.com