how to reset the auto increment?

[CyberShot]

using phpmyadmin, I tried to reset the auto_increment but it won't work. I tried a query that I found googling the issue that sets it to one but then whey I tried a new query to see what the id was, it incremented from 8 to 9 instead of showing 1. how do you do this without recreating the database?

[Maq]

May I ask why you even want to do this?

[CyberShot]

because I am teaching myself how to work with databases. When a query fails, it still seems to auto increment the count which eventually throws off my count for doing what I want to do do. So I am using insert_id to get the ID of the primary key so that I can insert that number into the foreign key of my child tables. last night, the insert_id was return 19 but the primary key of the parent was only on 9 So I needed to reset things to keep working properly

[fenway]

When a query fails, it still seems to auto increment the count which eventually throws off my count for doing what I want to do do.......So I needed to reset things to keep working properly

Don't use these values as counters -- that's just wrong.  Nothing will work properly if you reset them.  Trust me.

[shlumph]

Use MySQL's COUNT() for counting. Auto increment is used on primary keys to keep each row unique; that's it, it's not meant to be changed. If you change its value when there is already existing data, all of your foreign keys will be messed up and you're going to run into problems.

 

But just for clarity, you can adjust the auto increment value by doing something like:

ALTER TABLE table_name_here AUTO_INCREMENT = 131285;

[CyberShot]

Use the COUNT() for what? to get the id's to put into the foreign keys?

[shlumph]

Use COUNT() for counting rows in the table.

 

Use ID's for foreign keys.

[CyberShot]

So this is the standard practice to get the id to insert into the foreign key? write another query every time to count the rows and then insert that number into the foreign key? That seems like it would take much more programming than just using insert_id

[shlumph]

Do *not* use COUNT() to create a foreign key. Use the primary key of the original table, which is generated via auto increment, as a foreign key value in other tables.

 

Use COUNT() if you want to count the number of rows in a table.

[CyberShot]

why would I want to count the rows of a table? that was the whole point to this post. the only way I know of to get the primary key is by using insert_id which is why I wanted to know how I could reset the count. insert_id won't use count() for anything that I know of. it gets the id of the current query and I plan on using that to insert into my foreign key like this

 

("INSERT INTO phone(home, cell) VALUES($home, $cell, $mysql->insert_id)");

[Pikachu2000]

That isn't what you said in the OP. You said that the gaps in the autoincremented pk id were messing up your count. 

[PFMaBiSmAd]

The only reason count got mentioned in the replies is because your post implied that the auto-increment value was somehow a counter and that you expected it to have a specific count value rather than an id -

... it still seems to auto increment the count which eventually throws off my count for doing what I want to do do. ...  So I needed to reset things to keep working properly

 

Edit: They are already working properly.

[shlumph]

why would I want to count the rows of a table?

I do not know, why would you?

 

that was the whole point to this post.

Ok then, use MySQL's COUNT() if you want to count the rows in a table.

 

the only way I know of to get the primary key is by using insert_id

Correct.

 

which is why I wanted to know how I could reset the count

No, do not reset the auto increment ID.

 

insert_id won't use count() for anything that I know of. it gets the id of the current query and I plan on using that to insert into my foreign key like this

("INSERT INTO phone(home, cell) VALUES($home, $cell, $mysql->insert_id)");

Correct. Which is why you do *not* want to reset the auto increment ID.

 

I hope this clears things up.

[CyberShot]

when I did my test queries, I had several that failed. so when I first discovered insert_id and echoed it to see what it did, I got back the value of 19 when I looked in the database, my last primary id was 9. They don't match. which is why I wanted to reset the values. What do you do when insert_id returns 19 and you are only on 9?

[shlumph]

Delete the test data that's messed up, and then fix the code so the query doesn't fail. Then you should be good to go. Your next insert_id will be 20.