phpMyAdmin Autoindex Value

Hello, I created a database using myphpadmin, I have an ID field that gets an auto index value. Through testing and editing I have had to delete rows so my autoindex doesn't reflect the next real ID number, is there a way to change what the next value it will give for the next new entry?

Thanks,

In mySQL, you can set the number to the lowest auto-increment number + 1. You cannot fill in numbers that no longer exist with only mySQL syntax, however. PHP or another mySQL programming aid can do this for you. At any rate, unless you're a neat freak, this shouldn't be a problem.

Although I use a different database admin tool, if you go into phpMyAdmin and click on the link for the table in question, you should find one of the tabs is labeled 'Empty'.

Clicking on this should let you empty the table when you have finished testing and reset everything including indexes back as if you had just set the table up.

You cannot set a primary autoincrement key to a number that has been deleted. You have to do approximately what Geoff was suggesting (although myphpadmin works a little differently).

In Operations, you will see that you can copy the "structure only" of a table. Do that. Then copy all the values except the primary key, which will autoindex from 1 to n. Either change the name of the original or delete it, then rename the new table to the name you want.

Although your answers are perfectly valid, gents, shouldn't we be taking a step back from the original question here and asking why it's important that the ids are sequential / low starting for the questioner? As just a matter of interest, the question is a good one. But if a solution is based on the answer, then fixing it today will only be a temporary thing ... there will come other times that it needs to be reset, and a unique primary key column and a separate record ID might make more sense.