CODEWITHSUNDEEP
phpmysqlauto-increment
user2432782
user2432782

Reputation: 58

Anyway of making the IDs sorted?

I have 1061 users in my website and I delete ones that haven't paid every so often. I don't like how it says ID: 304 and then skips to ID:400...

Is there any way of changing the system so when I delete someone it changes their ID to e.g. 305 and then carries on?

This is OCD and really annoying me.

Upvotes: 0

Views: 83

Answers (3)

Sharky
Sharky

Reputation: 6284

It's not common practice but you could execute a 

ALTER TABLE users AUTO_INCREMENT = 1

after every removal of user

Then every new inserted user will get the first available id. I must say again this is not pretty. You must make sure that when an id is removed, anything that is based or connected somehow on that id must also be removed.

Upvotes: 2

symcbean
symcbean

Reputation: 48357

The only way to have consecutively numbered ids is to write lots and lots of code, and shutdwon access to your system regularly while it rebuilds the numbering. i.e. trying to get the result you want is very, very difficult and disruptive.

It's not broken.

Don't try to fix it.

Upvotes: 8

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

When you delete user, you can run UPDATE users SET id = id - 1 WHERE id > X order by id ASC where X is the ID of the deleted user then you have to alter the next autoincrement ALTER TABLE users SET AUTO_INCREMENT = Y where Y is MAX(id) + 1

Upvotes: 0

Related Questions