How to Change Next Autoindex ID Number in phpMyAdmin?

PHP
1

Can somebody tell me how I can change the Next Autoindex ID number for a row in phpMyAdmin (latest version)? Say I have 50 rows of information, then I delete 45 of them. How can I make it so it will start the next ID number in the table to 6 instead of 51?

2

only way is to pull all the info out (except the id) into a comma seperated values file (or any type of file really), delete the table, rebuild the table and re-insert the data.

there is a reason why mysql never uses the lowest available number in auto_increment fields. It stops hard coded pages accidentally linking to a new item from an old link.

Why would you want to use the missing numbers?
If it is because of any reason other than database tidyness, you may want to rethink your schema.

3

I basically just wanted to “reset it” because I had added the first 50 rows for testing.

Thanks for your help. I’ll just leave it the way it is.

4

try this

ALTER TABLE table_name AUTO_INCREMENT=51

or whatever number you want to reset it and start at

5

I tried this “ALTER TABLE review AUTO_INCREMENT=1” and it said this:

Error

SQL-query : ALTER TABLE reviewAUTO_INCREMENT = 1

MySQL said: You have an error in your SQL syntax near ‘= 1’ at line 1

Any ideas?

6

maybe it has to do with the spaces in the string???

ALTER TABLE review AUTO_INCREMENT=1
7

When I paste “ALTER TABLE review AUTO_INCREMENT=1” it says “ALTER TABLE reviewAUTO_INCREMENT = 1” even though I had the space after review.

8

try it with quotes

ALTER TABLE `review` AUTO_INCREMENT=1

not sure if those are quotes or what but thats what i use in my myphpadmin.

9

OK…I feel dumb, but I didn’t think the SQL link I clicked on would make a difference. I went to the table then clicked SQL which wasn’t working. When I clicked on the main database name then clicked SQL, it worked.

Thanks for all of your help!

10

This is a known bug in phpmyadmin (amoung many, many others) The stupid query parser is screwing up the SQL statement. I tried it in phpmyadmin as well with no luck. Use the commandline or write a php script to execute it if you have no shell access.