How to delete id and reset in php

As website data continues to accumulate, we often encounter situations where data needs to be deleted. Generally speaking, after deleting data, the ID needs to be reset to ensure that the structure of the data table is continuous. In PHP, there are two methods to implement this function: delete and then insert new data or modify the ID directly. Next, this article will introduce these two methods in detail.

The first method: delete and then insert new data

The main idea of ​​this method is to first delete the data to be deleted, and then reorder the remaining data and insert new data. ID.

1. Delete the data of the specified ID

First, we need to write a function to delete the data of the specified ID. The sample code is as follows:

function deleteDataById($id) {
    $sql = "DELETE FROM `table_name` WHERE `id` = $id"; // 将 `table_name` 修改为实际表名
    $result = mysqli_query($conn, $sql); // 将 $conn 修改为实际数据库连接变量名
    return $result;
}

2. Get the maximum ID of the data in the current table

Next, we need to write a function to get the maximum ID of the data in the current table. The sample code is as follows:

function getMaxId() {
    $sql = "SELECT MAX(`id`) AS max_id FROM `table_name`"; // 将 `table_name` 修改为实际表名
    $result = mysqli_query($conn, $sql); // 将 $conn 修改为实际数据库连接变量名
    $row = mysqli_fetch_assoc($result);
    $max_id = $row['max_id'];
    return $max_id;
}

3. Reorder and insert new ID

Finally, we need to write a function to reorder and insert new ID. The sample code is as follows:

function resetId() {
    global $conn; // 添加此行
    $max_id = getMaxId();
    for ($i = 1; $i <= $max_id; $i++) {
        $result = deleteDataById($i);
        if ($result) {
            $sql = "INSERT INTO `table_name` (`id`, `column1`, `column2`) VALUES ($i, &#39;value1&#39;, &#39;value2&#39;)"; // 将 `table_name` 修改为实际表名，`column1` 和 `column2` 修改为实际列名
            mysqli_query($conn, $sql);
        }
    }
}

Second method : Directly modify the ID

The main idea of ​​this method is to modify the ID of the data to be deleted to the ID of the subsequent data to maintain data continuity. The specific implementation method is as follows:

1. Delete the data of the specified ID

Same as the first method, we need to write a function to delete the data of the specified ID.

2. Modify the ID of the subsequent data

Next, we need to write a function to modify the ID of the subsequent data. The sample code is as follows:

function modifyId($start, $end) {
    global $conn; // 添加此行
    $sql = "UPDATE `table_name` SET `id` = `id` - 1 WHERE `id` > $start AND `id` <= $end"; // 将 `table_name` 修改为实际表名
    mysqli_query($conn, $sql);
}

3. Reset the specified ID

Finally, we need to write a function to reset the specified ID. The sample code is as follows:

function resetId($id) {
    global $conn; // 添加此行
    $max_id = getMaxId();
    if ($id > $max_id || $id < 1) {
        return false;
    }
    for ($i = $id; $i <= $max_id; $i++) {
        $result = deleteDataById($i);
        if ($result) {
            modifyId($i, $max_id + 1);
            $sql = "INSERT INTO `table_name` (`id`, `column1`, `column2`) VALUES ($i, 'value1', 'value2')"; // 将 `table_name` 修改为实际表名，`column1` 和 `column2` 修改为实际列名
            mysqli_query($conn, $sql);
            break;
        }
    }
}

In summary, we can use the above Two ways to delete data and reset ID. Which method to use depends on actual needs and project size. No matter which method you use, you need to pay attention to backing up your data to avoid data loss.

The above is the detailed content of How to delete id and reset in php. For more information, please follow other related articles on the PHP Chinese website!