PHP读取MySQL表按ID更新status时点击按钮全量更新问题求助

故障原因 #

• 更新逻辑写在遍历每行的循环内部，触发POST请求后所有行的更新判断都会生效，导致所有记录都被修改
• 提交表单没有携带对应行的ID参数，后端无法识别需要修改的目标记录
• 直接拼接SQL语句存在SQL注入安全风险
• 表格结构不合理，循环输出重复的表头标签

修复后代码 #

<!DOCTYPE html>
<html lang="de">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, shrink-to-fit=no">
    <title>Table</title>
    <link rel="stylesheet" href="assets/bootstrap/css/bootstrap.min.css">
    <script src="assets/bootstrap/js/bootstrap.min.js"></script>
</head>
<body>  
<?php
// 数据库连接
$conn = new mysqli ("localhost", "root", "Z&r*%/Nm@X4x", "cms");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// 先处理更新请求，放在最顶部避免渲染后再修改数据
$update_msg = '';
if (isset($_POST['change']) && isset($_POST['record_id'])) {
    $record_id = intval($_POST['record_id']);
    // 使用预处理语句避免SQL注入
    $update_stmt = $conn->prepare("UPDATE data SET status = '2' WHERE id = ?");
    $update_stmt->bind_param("i", $record_id);
    if ($update_stmt->execute()) {
        $update_msg = "ID为{$record_id}的记录状态修改成功";
    } else {
        $update_msg = "修改失败：" . $update_stmt->error;
    }
    $update_stmt->close();
}

// 查询数据
$sql = "SELECT id, position, status FROM data";
$stmt = $conn->prepare($sql); 
$stmt->execute();
$result = $stmt->get_result();
$data = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
?>

<?php if ($update_msg): ?>
<div class="alert alert-info"><?php echo $update_msg; ?></div>
<?php endif; ?>

<div class="table-responsive d-table">
    <table class="table table-bordered">
        <thead>
            <tr>
                <th>ID</th>
                <th>POSITION</th>
                <th>STATUS</th>
                <th>操作</th>
            </tr>
        </thead>
        <tbody>
            <?php if ($data): ?>
                <?php foreach($data as $row): ?>
                <tr>
                    <td><?php echo $row['id']; ?></td>
                    <td><?php echo $row["position"]; ?></td>
                    <td><?php echo $row["status"]; ?></td>
                    <td>
                        <form method="post" style="margin:0;">
                            <!-- 隐藏域传递当前行ID -->
                            <input type="hidden" name="record_id" value="<?php echo $row['id']; ?>">
                            <button type="submit" name="change">CHANGE STATUS</button>
                        </form>
                    </td>
                </tr>
                <?php endforeach; ?>
            <?php else: ?>
                <tr>
                    <td colspan="4">No data found!</td>
                </tr>
            <?php endif; ?>
        </tbody>
    </table>
</div>

<?php $conn->close(); ?>
</body>
</html>

内容的提问来源于stack exchange，提问作者Cloud