Id's being muddeled up!

[yc022]

Hi again, does anyone know how to make the automatic "ID's" in a table become synchronised again once something is deleted from the table.

I have a select list that holds the users ID whilst displaying the users user name. When the administrator clicks to delete the user the ID fields in the database table keep the same value i.e. an unordered value. The ID field in the database is set to automatic. I would like to know how to make it become an ordered list, any suggestions would be great.

If you need to see my code, i will post it.

Thank you.

[quill]

Hi,

If you are using auto_increment and the id is relational to other tables, that could muck up a lot.

Say you had in a users table:

id | name
-------------

1 | Fred
2 | Joe
3 | Mary

And they each posted a message on a forum, so

id | userId | message
------------------------------
1 | 1 | Fred's message
2 | 2 | Joe's message
3 | 3 | Mary's message

If you deleted Joe from the users table and reordered the list so it looked:

1 | Fred
2 | Mary

Then Mary may be attributed to Joe's posts, so when you look up the forum posts it would be:

id | userId | message
------------------------------
1 | 1 | Fred's message
2 | 2 | Joe's message // But the userId would link to Mary (As she is now "2")
3 | 3 | Mary's message
// The userId "3" would not link anywhere, or someone else

If it is not related to anything, then you could do an export the data, empty the database and submit the data again, thus reordering the auto_increment.