Good morning. I'm doing a little job with PHP, and I need help removing a record from a table. I insert data, and then I delete them, but the auto_increment continues in the last inserted position. I want that when deleting a data, the auto_increment will also "do" it and restart it to the previous value. It's possible? It is advisable? Or I just ignore it? I greatly appreciate any help.
Go to the "Operations" tab in phpmyadmin and when you open it look for the "auto increment" field. Put "1" and that's it. That for each table in your database.
To edit it from the code, just do this:
$query = "ALTER TABLE tu_tabla_va_aqui AUTO_INCREMENT = 1";
Note: The value of "1" is not mandatory. You can do this to get the value of 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 removing the last element, as you asked 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!
P.D: Depending on how you handle your code, it is recommended that you use this practice. Since if at any point you need to show all the records you have in your table, you do not decrement the id of the auto-increment, and you do not clarify if (mysqli_num_rows ($ answer)> 0) {// send / show data} , errors will occur due to "id" that do not exist after having been deleted in the past. Therefore, whether you do this extra verification that I just mentioned or alter the auto_increment of the table, both could be considered as practical and correctly used solutions. Good luck!