Reusing Auto-increment Id's

[Err]

I'm using a MySQL database. I use it with PHP and in one of my applications I have the option to delete a feedback comment. The problem is, when I list the comments by ID's and delete a comment, lets say id 4, my feedback comments aren't numbered correctly anymore. On INSERT the MySQL does not reuse id 4, but instead adds to the latest auto-increment value. I have done some reading and found out it's not necessary to reuse id's, but I disagree. Since I'm using the id to display the comment number, if I delete one, it messes up the count from 1-10 by completely missing 4. I can solve this problem by using a variable and add one to everyone comment to keep the comments properly numbered, but I feel that's an unnecessary step. There is some way I can properly maintain the id's in my table?

[GerryH]

I would ask how do you want the data ordered, by a timestamp perhaps? If so, why not add a field with a UNIX_TIMESTAMP?

[Err]

I would ask how do you want the data ordered, by a timestamp perhaps? If so, why not add a field with a UNIX_TIMESTAMP?

That would be the same thing has having it the way it is right now and using a variable to number the comments. If I can keep the comments numbered correctly in the database, I can use the id's that I already have to number them.

[GerryH]

I'm not quite sure I understand what you want to do. If you have setup a primary_key / auto_increment named "id", even if you could recycle the deleted numbers what purpose would that serve? for example if you have 10 entries, and delete the second one, you want to have the next INSERT go to the oldest deleted id? Forgive me, I'm not following your logic.

[Err]

I have a feedback table, the primary key is the id which has a auto-increment value. I want to reuse id's that were deleted so the number order of my comments are correct. I am simply looking for a method where I can reuse deleted id's.

[justsomeguy]

Using a variable to keep track of the counter is the right way to go, not to use the ID. Your application may only store comments for one thing, but imagine if it's storing comments for more than one thing. You might have 5 comments for the first thing, so they get numbered 1-5, then 5 comments for another thing, so they get numbered 6-10, then another 5 comments for the first thing, which get 11-15. So if you use the ID as a counter you'll show comments 1-5 and 11-15 for one thing, and comments 6-10 for another. The ID is just supposed to uniquely identify the record in the database, it's not supposed to be a counter. You can either keep track of the counter in PHP, keep a counter column in the table which you keep updated as you add or delete things, or you can use a stored procedure to add a counter column to the result set like this:http://forums.mysql.com/read.php?10,113604,113606#msg-113606

[Err]

Thanks for all the help guys, I think I will go with the counter method.