Query to update auto incremented id numbers?

[webguync]

I have an auto incremented field in my table called id. The numbers were from 1-125. I had to delete one record with an id of 1. Now I want to change the numbering since it is from 2-125 now (back to 1 to the end). Is there a Query to do this without going into each individual record in PHP MyAdmin and doing it?

[Pikachu2000]

No, you really don't want to do that. There's no reason to manipulate an autoincremented index field.

[webguync]

ok, well I am displaying the id info on a page using PHP so my numbering display is off now. The only other option I have is to re-upload my content to get the numbering order correct.

[gizmola]

ok, well I am displaying the id info on a page using PHP so my numbering display is off now. The only other option I have is to re-upload my content to get the numbering order correct.

 

Again the purpose of the key is to provide uniqueness, and to facilitate joins to other related tables.  It is not a counter. 

 

With that said, what you can do is:

 

-reset the auto_increment counter for the table to 1

-drop the column

-re-add it

 

You can run these commands in the mysql client or use the sql panel of phpMyAdmin, or whatever mysql admin app you might be using. 

 

alter table yourtable auto_increment = 1
alter table yourtable drop id
alter table yourtable add id int unsigned primary key auto_increment first

 

[fenway]

ok, well I am displaying the id info on a page using PHP so my numbering display is off now. The only other option I have is to re-upload my content to get the numbering order correct.

No -- the "other option" you have is to number them properly -- with a counter.

 

gizmola has given you a way to fix this once -- but don't.

[webguync]

ok, thanks I will create a counter. I guess that is something for the PHP forum if I run into questions.