Tables ID

[AlabamaHit]

I have a table in this case its my citys....the ID went from 9 to 100 so in order its like

1

2

3

4

5

6

7

8

9

100

101

102

103

Ok...now i have went back and changed everything to get the back in order...9, 10, 11, 12.....

But when I make a new on in the table it goes to exp....104

Is there a way tio make it pick up from where it left off? I hope you understand what i mean I suck at explaing, lol...I jjust want to get the table back in the right order.

[Godhand]

Re: Tables ID

Did you make any changes within your DB other then you setting them back in order?

[Haunted Dawg]

Re: Tables ID

You will need to create your cities table gain.

If you send me your cities table sql i could re create it for you and resend it back for you.

[AlabamaHit]

Re: Tables ID

I have made lots of changes to my DB..........

I can recreate it if thats my only option....So ther is no way to tell it to just start listing IDs from the last one?

[Spudinski]

Re: Tables ID

Somewhere somehow your script must have made an addition of 90+ rows to the table, and then deleted it.

No need to delete the whole thing, just reset the AUTO_INCREMENT value.

I reckon you have set the ids of the cities already, so based upon your last id, modify the following:

ALTER TABLE `cities` AUTO_INCREMENT = 12

The number, change it to the last id in the table, plus one.

Now just run that in phpmyadmin and it should be fine.

[Floydian]

Re: Tables ID

Just to confirm Spudinski's post, you DO NOT NEED TO DELETE THE TABLE

Doing what Spudinski says will take your autoincrement back down where it should be.

There is a counter stored in the information schema that tells mysql what the next id should be in an auto increment field. It does NOT look at the last ID in the table, but at that counter stored in the information schema.

[Haunted Dawg]

Re: Tables ID

But it would be faster for him to just add this:

DROP TABLE cities;

CRATE TABLE cities blah blah;

INSERT INTO cities VALUES(null,blah,blah,blah,blah);

 

Then he would need to go to every row and change the id.

[Spudinski]

Re: Tables ID

It would take a few milliseconds(0.004?) to alter the AUTO_INCREMEMENT value of a table.

It would take about 5 minutes to do the way you are suggesting.

[AlabamaHit]

Re: Tables ID

ok...my last last city ID was 15 so i racn

ALTER TABLE `cities` AUTO_INCREMENT = 15;

now its working right Thank you so much, the next city i made was 16.. :-D

[Floydian]

Re: Tables ID

Sweet!!

Just a side note, you could set it to 1 and mysql would then check and see that hm, you've got id's going up to 15 and then correct that for you automatically.

So, when I need to do those things, I just set them to 1 and I'm done with it lol.

[AlabamaHit]

Re: Tables ID

So even though i had a list up to 15. i could have done

ALTER TABLE `cities` AUTO_INCREMENT = 1;

and it would ahve dont the same thing?

[Floydian]

Re: Tables ID

That's correct. ;)