[php] Reset ID autoincrement ? phpmyadmin

I was testing some data in my tables of my database, to see if there was any error, now I cleaned all the testing data, but my id (auto increment) does not start from 1 anymore, can (how do) I reset it ?

I have just experienced this issue in one of my MySQL db's and I looked at the phpMyAdmin answer here. However the best way I fixed it in phpMyAdmin was in the affected table, drop the id column and make a fresh/new id column (adding A-I -autoincrement-). This restored my table id correctly-simples! Hope that helps (no MySQL code needed-I hope to learn to use that but later!) anyone else with this problem.

ALTER TABLE xxx AUTO_INCREMENT =1; or clear your table by TRUNCATE

I agree with rpd, this is the answer and can be done on a regular basis to clean up your id column that is getting bigger with only a few hundred rows of data, but maybe an id of 34444543!, as the data is deleted out regularly but id is incremented automatically.

ALTER TABLE users DROP id

The above sql can be run via sql query or as php. This will delete the id column.

Then re add it again, via the code below:

ALTER TABLE  `users` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST

Place this in a piece of code that may get run maybe in an admin panel, so when anyone enters that page it will run this script that auto cleans your database, and tidys it.

You can also do this in phpMyAdmin without writing SQL.

• Click on a database name in the left column.
• Click on a table name in the left column.
• Click the "Operations" tab at the top.
• Under "Table options" there should be a field for AUTO_INCREMENT (only on tables that have an auto-increment field).
• Input desired value and click the "Go" button below.

Note: You'll see that phpMyAdmin is issuing the same SQL that is mentioned in the other answers.