I'm creating a database that auto increments. I made some error so I had to delete and insert a few id's in the middle of the database. Now, every time I insert a new id data into the table, despite explicitly saying what id I want the new data to be assigned to, it's still assigning the new data to a completely wrong id number. For example, I have datas with ID 1-20. I deleted datas from 5-20, leaving id 1-4. Now when I insert a new ID, I'd like it to be 5 instead of 21. How do I do this using terminal or the php (below)?
<?php
// 1. Create a database connection
$dbhost = "127.0.0.1";
$dbuser = "widget_cms";
$dbpass = "xxxxxxx";
$dbname = "widget_corp";
$connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
// Test if connection failed. If yes, exit.
if(mysqli_connect_errno()) {
die("Database connection failed: " .
mysqli_connect_error() .
" (" . mysqli_connect_errno() . ")"
);
}
$id = 5;
$menu_name = "New ID";
$position = 5;
$visible = 1;
// 2. Perform database query
$query = "UPDATE subjects SET ";
$query .= "menu_name = '{$menu_name}', ";
$query .= "position = '{$position}', ";
$query .= "visible = '{$visible}' ";
$query .= "WHERE id = '{$id}' ";
$result = mysqli_query($connection, $query);
if ($result && mysqli_affected_rows($connection) == 1) {
// Success. Usually redirects to some other page
echo "<h1>Success!</h1>";
} else {
die("Database query error. " . mysqli_error($connection));
}
?>
You can use this MySQL command:
ALTER TABLE subjects AUTO_INCREMENT = 5
But as noted by Mark Baker in his comment: "Autoincrement is intended for unique ids, not consecutive values.". So your code should not rely on consecutive values.