How to operate the database through PHP pop-up windows

As a PHP developer, we often need to operate the database. In actual projects, we often need to use pop-up windows to implement some operations, such as deletion, modification, etc.

This article will introduce how to operate the database through PHP pop-up windows, aiming to help readers better master this skill.

1. Preparation work

Before implementing the pop-up window, we need to prepare the following work:

1. Database connection: We need to use PHP to create a database connection.

2. Database query: We need to use PHP to query the database to obtain the data that needs to be operated.

3. Pop-up window code: We need to use JavaScript or jQuery to write the pop-up window code.

For the convenience of demonstration, here we use MySQL as the database and PHP PDO to connect to the database.

2. Delete data

Below we will introduce how to delete data through pop-up windows. First, we need to get the data to be deleted from the database and then display it on the page to facilitate user selection.

1. Query data

We can use the following code to query the data that needs to be deleted:

<?php
//连接数据库
$dsn = &#39;mysql:host=localhost;dbname=test;charset=utf8&#39;;
$username = &#39;root&#39;;
$password = &#39;123456&#39;;
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, //异常处理模式
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, //默认的查询结果类型
];
$pdo = new PDO($dsn, $username, $password, $options);

//查询要删除的数据
$sql = "SELECT * FROM test_table WHERE status = 0"; //status字段表示待删除数据
$stmt = $pdo->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll();
?>

In the above code, we first connect to the database and use the SELECT statement to query the data that needs to be deleted. The data. Among them, the status field indicates the data to be deleted.

2. Display data

Next, we display the queried data on the page for users to select.

<table>
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Action</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach($data as $index=>$row): ?>
        <tr>
            <td><?php echo $row[&#39;id&#39;]; ?></td>
            <td><?php echo $row[&#39;name&#39;]; ?></td>
            <td><?php echo $row[&#39;email&#39;]; ?></td>
            <td><button class="delete-btn" data-id="<?php echo $row[&#39;id&#39;]; ?>">删除</button></td>
        </tr>
        <?php endforeach; ?>
    </tbody>
</table>

In the above code, we use HTML tables to display the queried data on the page. In order to implement the delete function, we add a delete button to each row of data and store its corresponding ID value through the data-id attribute.

3. Delete data

Finally, let’s implement the delete function. When the user clicks the delete button, we will pop up a confirmation box asking the user whether to delete the data. If the user confirms the deletion, we will send an AJAX request to delete the data from the database.

$('.delete-btn').on('click', function() {
    var id = $(this).data('id');
    if (confirm('确定要删除吗？')) {
        $.ajax({
            url: 'delete.php', //处理删除请求的PHP文件
            type: 'POST',
            data: {id: id},
            success: function(res) {
                if (res.code === 0) {
                    alert('删除成功');
                    window.location.reload(); //刷新页面
                } else {
                    alert('删除失败，请稍后再试');
                }
            },
            error: function() {
                alert('请求失败，请稍后再试');
            }
        });
    }
});

In the above code, we use jQuery to bind the click event of the delete button. When the user clicks the delete button, we first get the ID value of the data, and use the confirm function to pop up a confirmation box asking the user if they want to delete it. If the user clicks to confirm, we will send a POST request to the delete.php file to delete the data from the database.

Note: The code in the delete.php file is as follows:

<?php
//连接数据库
$dsn = &#39;mysql:host=localhost;dbname=test;charset=utf8&#39;;
$username = &#39;root&#39;;
$password = &#39;123456&#39;;
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, //异常处理模式
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, //默认的查询结果类型
];
$pdo = new PDO($dsn, $username, $password, $options);

//删除数据
$id = $_POST['id'];
$sql = "DELETE FROM test_table WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$res = $stmt->execute();

//返回结果
if ($res) {
    echo json_encode(['code' => 0, 'msg' => '删除成功']);
} else {
    echo json_encode(['code' => 1, 'msg' => '删除失败']);
}
?>

In the above code, we first connect to the database, and then use the DELETE statement to delete the data from the database. Finally, we return the results in JSON format.

3. Modify data

In addition to deleting data, we often need to modify data. Below, we will introduce how to modify data through pop-up windows.

1. Query data

First, we need to query the data that needs to be modified from the database and display it on the page to facilitate users to modify it.

<?php
//连接数据库
$dsn = &#39;mysql:host=localhost;dbname=test;charset=utf8&#39;;
$username = &#39;root&#39;;
$password = &#39;123456&#39;;
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, //异常处理模式
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, //默认的查询结果类型
];
$pdo = new PDO($dsn, $username, $password, $options);

//查询要修改的数据
$id = $_GET['id'];
$sql = "SELECT * FROM test_table WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetch();
?>

In the above code, we first obtain the ID of the data that needs to be modified through $_GET['id']. Then, we use the SELECT statement to query the data from the database.

2. Display data

Next, we display the queried data on the page to facilitate user modification. Here we still use the HTML form to fill the queried data into the form.

<form>
    <input type="hidden" name="id" value="<?php echo $data[&#39;id&#39;]; ?>">
    <div>
        <label>Name:</label>
        <input type="text" name="name" value="<?php echo $data[&#39;name&#39;]; ?>">
    </div>
    <div>
        <label>Email:</label>
        <input type="text" name="email" value="<?php echo $data[&#39;email&#39;]; ?>">
    </div>
    <button class="submit-btn">保存</button>
    <button class="cancel-btn" type="button" onclick="history.back()">取消</button>
</form>

Note that we added a hidden field to the form to store the ID value of the data that needs to be modified.

3. Modify data

Finally, let’s implement the modification function. When the user clicks the save button, we will send an AJAX request to update the modified data into the database.

$('.submit-btn').on('click', function() {
    var data = $('form').serialize();
    $.ajax({
        url: 'update.php', //处理修改请求的PHP文件
        type: 'POST',
        data: data,
        success: function(res) {
            if (res.code === 0) {
                alert('修改成功');
                window.location.href = 'index.php'; //跳转到列表页
            } else {
                alert('修改失败，请稍后再试');
            }
        },
        error: function() {
            alert('请求失败，请稍后再试');
        }
    });
});

In the above code, we use jQuery to bind the click event of the save button. When the user clicks the save button, we first get all the data in the form through the serialize function and send it to the update.php file. Here we use POST method to send data.

Note: The code in the update.php file is as follows:

<?php
//连接数据库
$dsn = &#39;mysql:host=localhost;dbname=test;charset=utf8&#39;;
$username = &#39;root&#39;;
$password = &#39;123456&#39;;
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, //异常处理模式
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, //默认的查询结果类型
];
$pdo = new PDO($dsn, $username, $password, $options);

//修改数据
$id = $_POST['id'];
$name = $_POST['name'];
$email = $_POST['email'];
$sql = "UPDATE test_table SET name = :name, email = :email WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->bindParam(':email', $email, PDO::PARAM_STR);
$res = $stmt->execute();

//返回结果
if ($res) {
    echo json_encode(['code' => 0, 'msg' => '修改成功']);
} else {
    echo json_encode(['code' => 1, 'msg' => '修改失败']);
}
?>

In the above code, we first connect to the database, and then use the UPDATE statement to update the data into the database. Finally, we return the results in JSON format.

Note that for the convenience of demonstration, here we use the serialization function to convert the form data into a string, but in actual projects, we usually use the FormData object to process the form data to support functions such as uploading files.

4. Summary

Through the introduction of this article, we have learned how to operate the database through PHP pop-up windows. Whether it is deletion or modification, we can use similar methods to achieve it. I hope this article can provide some help to readers. If there is anything you still don’t understand, you can leave a message for discussion.

The above is the detailed content of How to operate the database through PHP pop-up windows. For more information, please follow other related articles on the PHP Chinese website!