MySql - Increment values reassignable?

[jawn_tech]

A MySql database is set up where a user can input new row entries via a form. Each row has an id column which is auto-incremented by 1. Suppose a user enters a spam or nonsensical entry, and suppose that row generated gets an id# of 215. By the time admin catches it, rows are already at 300. Admin wants to delete row #215 from the db. Do the following rows keep their values, or does the row that was 216 become 215, and so forth? If not, is there a way to reassign the following rows so that there is not a gap for 215, therefore without breaking continuity in the numerical sequence of rows?

[rivux]

When you delete 215 the others will stay as they are, which is how you want it to be for tracking and auditing purposes. The last thing you would want is for your uniques ID to change depending on whether other rows are removed, it would make database integrity nearly impossible to maintain and tracking even worse.

[jawn_tech]

That makes sense. I'm just thinking ahead when I'm ready to add the feature when one can search the db, or view rows by a category field. I'm assuming I would need to set up a loop PHP that would display rows that match the query by that particular field. I'm assuming it should be that a blank id# would signal the loop to end...?

Unless, since it's searching the db by a particular field, should the data in row 215 be deleted -- but should I leave the id# (215), as it wouln't turn up for any of the queries anyway? I just want to make sure a loop doesn't stop at 215 where a row used to be.

[rivux]

Two options come to mind right away. The first is you keep the row but have a status field, called approved, or active or something along those lines. If you want to keep the data but not show it just set that to 0 (all the others would be 1) and when you do a query to loop through the table just check with the where clause that its set to 1 (where active = 1). This will leave out that row.

The second thing is to just delete the row and forget about it. The thing to watch out for is if any other rows are inserted into the database using that rows ID as a seconday key. So if you insert a record there and delete it (ID 215) are you referencing that ID of 215 in any other tables?

Either way, when it comes to display the contents you will just do a query to pull them from the db and then loop through that query. Having any missing IDs wont matter as its just looping through the data pulled back. You would only have an issue if you were running a straight for loop (from 1 to last row) and pulling data based on that number, cuz when you get to 215 it wouldnt find anything. But thats a bad way to do it so you won't have to worry about it.

[jawn_tech]

Great advice, very appreciated. I don't mod this particular category for good reason, as you can see. ;)

[computergenius]

I'm just thinking ahead when I'm ready to add the feature when one can search the db, or view rows by a category field. I'm assuming I would need to set up a loop PHP that would display rows that match the query by that particular field. I'm assuming it should be that a blank id# would signal the loop to end...?


The ID will probably never be used when you do any kind of search. If your table has 3 fields, ID, Category, Description, then your example above would be
SELECT Description FROM myTable WHERE Category="Good Stuff";
You wouldn't need to show the ID on screen. So you wouldn't select it. And whether or not you selected it, you asked for all records in the "Good Stuff" category. You don't need to "mention" the ID, it doesn't have any relevance at this point

When the record is deleted, yes, it correctly leaves a gap in the numbering sequence - but it doesn't matter. Which, when you think about it, is why most database systems are designed to run that way...


You would only have an issue if you were running a straight for loop (from 1 to last row) and pulling data based on that number, cuz when you get to 215 it wouldnt find anything. But thats a bad way to do it so you won't have to worry about it.
If you are just running through your table, it would show
213 This
214 That
216 The Other
And 215 would not be missed.

[rivux]

You would only have an issue if you were running a straight for loop (from 1 to last row) and pulling data based on that number, cuz when you get to 215 it wouldnt find anything. But thats a bad way to do it so you won't have to worry about it.
If you are just running through your table, it would show
213 This
214 That
216 The Other
And 215 would not be missed.

If you are running a straight for loop then the output generally won't be conditional, so formatting, text, TRs etc will all be outputted for each row, so even if their isn't a 215 everything that goes with it will be outputted, which will cause a space and problems.