dynamically change the auto-increment in mysql

Question

I have a form that adds some data to a table.and i have a php file that prints out the data.in this page in each row a link is printed that when you click on it you will proceed to a page that deletes that single row. this page is called delete.php but there is a problem:when a single row is deleted , if i add another row , the auto-increment will be printed wrong! fow example if you delete row 7 and there are 13 rows printed,when you add another row the printed auto-increment column will be something like this: 1,2,...,12,14

here's the code of delete.php:

$conn = new mysqli($servername, $username, $password, $dbname);  
    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
        } 
 else {
    echo "" . mysqli_error($conn);
}

$id=$_GET['id'];


$sql = "DELETE FROM qolak WHERE id='$id'";

if (mysqli_query($conn, $sql)) {
    echo "";
    for($i=$id+1;$i<=1000;$i++){
    $j=$i-1;
    $sql = "UPDATE qolak SET id='$j' WHERE id='$i'";
   // 

    if (mysqli_query($conn, $sql)) {
    echo "";
} else {
    echo "" . mysqli_error($conn);
}
 }
    }
$rownum=$_GET['rownum'];
$rownum+=1;
$ssql = "ALTER TABLE qolak AUTO_INCREMENT ='$rownum'";


    if (mysqli_query($conn, $ssql)) {
    echo "";
} else {
    echo "" . mysqli_error($conn);

}

mysqli_close($conn);

here is my problem:

$ssql = "ALTER TABLE qolak AUTO_INCREMENT ='$rownum'";

this query wont dynamically change the auto-increment please help!!!

Answer 1

The chosen approach doesn't meet the basic problem:

You must distinguish between your auto-increment as an ID and your Output as a kind of human readable numbering. Separate this, as auto-increment will never be continuous and that's by design!

You should never ever try to manually change auto-increment values at all. Period.

If you want to have some continuous row numbering, add an extra column on your html (NOT in the database as there are no "row numbers" in there!)

Deleting the row #7 with ID = 13 will then cause row #8 to become the new 7 and so on.