How can PHP developers prevent MySQL auto-increment values from skipping numbers when deleting entries?

When deleting entries from a MySQL table with auto-increment values, the auto-increment counter does not reset, causing the next inserted row to have a higher value and potentially skip numbers. To prevent this, developers can manually reset the auto-increment counter to the highest existing value in the table after deleting entries. 

// Connect to MySQL database
$connection = mysqli_connect("localhost", "username", "password", "database");

// Get the highest existing auto-increment value
$query = "SELECT MAX(id) as max_id FROM table_name";
$result = mysqli_query($connection, $query);
$row = mysqli_fetch_assoc($result);
$max_id = $row['max_id'];

// Reset auto-increment value
$query = "ALTER TABLE table_name AUTO_INCREMENT = " . ($max_id + 1);
mysqli_query($connection, $query);

// Close database connection
mysqli_close($connection);

Keywords

PHP MySQL auto-increment values deletion

Related Questions