php – How to restart MYSQL auto_increment?

Question:

I'm doing a little work with PHP, and I need help removing a record from a table. I insert data, and then delete it, but auto_increment continues in the last inserted position. I want that when deleting a data, the auto_increment also "does" it and it is reset to the previous value. Is it possible? Just ignore it? I greatly appreciate any help.

Answer:

Go to the "Operations" tab in phpmyadmin and when it opens, look for the "auto increment" field. Put "1" and you're done. That for each table within your database.

To edit it from code, just do this:

$query = "ALTER TABLE tu_tabla_va_aqui AUTO_INCREMENT = 1";

Note: The value of "1" is not required. You can do this to get the value of the autoincrement:

$ultimo_id;

$query = "SELECT * FROM tu_tabla_va_aqui ORDER BY id DESC LIMIT 0,1";

$resp = mysqli_query($conexion, $query) or die("Error");

while($row = mysqli_fetch_assoc($resp)){
    $ultimo_id = $row["id"];
}

And once you get the last ID (this before eliminating the last element, as you mentioned in your question), you subtract "1", and that value is the one that goes in:

$query = "ALTER TABLE tu_tabla_va_aqui AUTO_INCREMENT = '$ultimo_id'";

I hope my answer will help you. Greetings!

PS: Depending on how you handle yourself in your code, it is recommended that you use this practice. Since if at some point you need to show all the records that you have in your table, you don't decrement the id of the auto-increment, and you don't clarify if (mysqli_num_rows ($ answer)> 0) {// send / show data} , errors will occur due to "id" that do not exist after being removed in the past. So whether you do this extra check that I just mentioned or alter the auto_increment of the table, both could be considered as practical and correctly used solutions. Luck!

Scroll to Top