PHP CRUD应用更新功能失效问题求助

云上老周

2026-6-22

修复CRUD应用更新模块异常问题

核心问题及修复方案

缺失POST请求中的ID参数
在POST处理分支中，代码未从表单提交的隐藏字段获取id值，导致UPDATE语句的WHERE id = '$id'中$id为空，无法定位到要更新的记录。需添加：
```
$id = $_POST["id"];
```
到POST逻辑的开头。

下拉选择框未预填充原有数据
pcs和warr下拉框没有根据数据库中的值设置选中状态，会导致用户提交时可能覆盖原有数据为空白。修改下拉框代码，添加条件判断设置selected属性：
例如pcs下拉框：

<select name="pcs" id="">
    <option value="" <?php echo empty($pcs) ? 'selected="selected"' : ''; ?>></option>
    <option value="1" <?php echo $pcs == '1' ? 'selected="selected"' : ''; ?>>1</option>
    <!-- 其他选项同理 -->
</select>

warr下拉框：

<select name="warr" id="">
    <option value="" <?php echo empty($warr) ? 'selected="selected"' : ''; ?>></option>
    <option value="yes" <?php echo $warr == 'yes' ? 'selected="selected"' : ''; ?>>Yes</option>
    <option value="no" <?php echo $warr == 'no' ? 'selected="selected"' : ''; ?>>No</option>
</select>

SQL注入风险及安全优化
直接将用户输入拼接到SQL语句存在安全漏洞，应使用预处理语句代替：

$sql = "UPDATE replog 
        SET service_dept = ?, po = ?, customer = ?, pcs = ?, equipment = ?, date_rec = ?, problem = ?, warr = ?, date_ship = ? 
        WHERE id = ?";
$stmt = $connection->prepare($sql);
$stmt->bind_param("sssssssssi", $service_dept, $po, $customer, $pcs, $equipment, $date_rec, $problem, $warr, $date_ship, $id);
$result = $stmt->execute();

这样可以避免SQL注入，同时处理特殊字符的转义问题。

检测更新是否实际生效
添加对受影响行数的检查，确保确实有记录被更新：

if (!$result) {
    $errorMessage = "Invalid Query: " . $connection->error;
    break;
}
if ($connection->affected_rows == 0) {
    $errorMessage = "No record found with this ID or no changes made";
    break;
}

完整修正后的代码

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database= "replog_test";

//create connection
$connection = new mysqli($servername, $username, $password, $database);

$id = "";
$service_dept="";
$po="";
$customer="";
$pcs="";
$equipment="";
$date_rec="";
$problem="";
$warr="";
$date_ship="";

$errorMessage = "";
$successMessage = "";

if ($_SERVER['REQUEST_METHOD'] == 'GET') {
    //GET method: Show the data of the client

    if (!isset($_GET["id"])) {
        header("location: /novasvm/index.php");
        exit;
    }

    $id = $_GET["id"];

    // read the row of the selected client from the database table
    $sql = "SELECT * FROM replog WHERE id=?";
    $stmt = $connection->prepare($sql);
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();

    if (!$row) {
        header("location: /novasvm/index.php");
        exit;
    }

    $service_dept = $row["service_dept"];
    $po = $row["po"];
    $customer = $row["customer"];
    $pcs = $row["pcs"];
    $equipment = $row["equipment"];
    $date_rec = $row["date_rec"];
    $problem = $row["problem"];
    $warr = $row["warr"];
    $date_ship = $row["date_ship"];
}
else {
    // POST method: Update the data of the client
    $id = $_POST["id"]; // 新增获取ID
    $service_dept = $_POST["service_dept"];
    $po = $_POST["po"];
    $customer = $_POST["customer"];
    $pcs = $_POST["pcs"];
    $equipment = $_POST["equipment"];
    $date_rec = $_POST["date_rec"];
    $problem = $_POST["problem"];
    $warr = $_POST["warr"];
    $date_ship = $_POST["date_ship"];

    do {
        if ( empty($service_dept) || empty($po) || empty($customer) || empty($pcs) || empty($equipment) || empty($date_rec) || empty($problem) || empty($warr)) {
            $errorMessage = "All fields except DATE SHIPPED are required";
            break;
        }

        // 使用预处理语句避免SQL注入
        $sql = "UPDATE replog 
                SET service_dept = ?, po = ?, customer = ?, pcs = ?, equipment = ?, date_rec = ?, problem = ?, warr = ?, date_ship = ? 
                WHERE id = ?";
        $stmt = $connection->prepare($sql);
        $stmt->bind_param("sssssssssi", $service_dept, $po, $customer, $pcs, $equipment, $date_rec, $problem, $warr, $date_ship, $id);
        $result = $stmt->execute();
        
        if (!$result) {
            $errorMessage = "Invalid Query: " . $connection->error;
            break;
        }

        // 检查是否有行被更新
        if ($connection->affected_rows == 0) {
            $errorMessage = "No record updated - either ID doesn't exist or no changes were made";
            break;
        }

        $successMessage = "Client Updated Correctly";
        header("location: /novasvm/general/replog.php");
        exit;

    } while(false);
}

?>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="../style.css" type="text/css">
    <link rel="stylesheet" href="../reset.css" type="text/css">
    <link rel="preconnect" href="https://fonts.googleapis.com">
    <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
    <link href="https://fonts.googleapis.com/css2?family=Poppins&display=swap" rel="stylesheet">
    <title>Repair Log</title>
</head>
<body>
    <div>
        <h2>New Data</h2>

        <?php  
        if ( !empty($errorMessage)) {
            echo "
            <div>
                <strong>$errorMessage</strong>
            </div>
            ";
        }          
        ?>

        <form method="post">
            <input type="hidden" name="id" value="<?php echo $id; ?>">
            <div>
                <label>Service Department</label>
                <div>
                    <input type="text" name="service_dept" value="<?php echo $service_dept; ?>">
                </div>
            </div>
            <div>
                <label>PO Number</label>
                <div>
                    <input type="text" name="po" value="<?php echo $po; ?>">
                </div>
            </div>
            <div>
                <label>Customer</label>
                <div>
                    <input type="text" name="customer" value="<?php echo $customer; ?>">
                </div>
            </div>
            <div>
                <label>Pieces</label>
                <div>
                    <select name="pcs" id="">
                        <option value="" <?php echo empty($pcs) ? 'selected="selected"' : ''; ?>></option>
                        <option value="1" <?php echo $pcs == '1' ? 'selected="selected"' : ''; ?>>1</option>
                        <option value="2" <?php echo $pcs == '2' ? 'selected="selected"' : ''; ?>>2</option>
                        <option value="3" <?php echo $pcs == '3' ? 'selected="selected"' : ''; ?>>3</option>
                        <option value="4" <?php echo $pcs == '4' ? 'selected="selected"' : ''; ?>>4</option>
                        <option value="5" <?php echo $pcs == '5' ? 'selected="selected"' : ''; ?>>5</option>
                        <option value="6" <?php echo $pcs == '6' ? 'selected="selected"' : ''; ?>>6</option>
                        <option value="7" <?php echo $pcs == '7' ? 'selected="selected"' : ''; ?>>7</option>
                        <option value="8" <?php echo $pcs == '8' ? 'selected="selected"' : ''; ?>>8</option>
                        <option value="9" <?php echo $pcs == '9' ? 'selected="selected"' : ''; ?>>9</option>
                    </select>
                </div>
            </div>
            <div>
                <label>Equipment</label>
                <div>
                    <input type="text" name="equipment" value="<?php echo $equipment; ?>">
                </div>
            </div>
            <div>
                <label>Date Received</label>
                <div>
                    <input type="date" name="date_rec" value="<?php echo $date_rec; ?>">
                </div>
            </div>
            <div>
                <label>Problem / Serial No.</label>
                <div>
                    <input type="text" name="problem" value="<?php echo $problem; ?>">
                </div>
            </div>
            <div>
                <label>Warranty</label>
                <div>
                    <select name="warr" id="">
                        <option value="" <?php echo empty($warr) ? 'selected="selected"' : ''; ?>></option>
                        <option value="yes" <?php echo $warr == 'yes' ? 'selected="selected"' : ''; ?>>Yes</option>
                        <option value="no" <?php echo $warr == 'no' ? 'selected="selected"' : ''; ?>>No</option>
                    </select>
                </div>
            </div>
            <div>
                <label>Date Shipped</label>
                <div>
                    <input type="date" name="date_ship" value="<?php echo $date_ship; ?>">
                </div>
            </div>

            <?php
            if (!empty($successMessage)) {
                echo "
                <div>
                    <strong>$successMessage</strong>
                </div>
                ";
            }
            ?>

            <div>
                <button type="submit">Submit</button>
            </div>
            <div>
                <a href="/novasvm/general/replog.php" role="button">Cancel</a>
            </div>
        </form>
    </div>
</body>
</html>

额外说明

预处理语句不仅提升了安全性，还能自动处理字符串中的特殊字符（如单引号），避免因输入内容导致SQL语法错误。
检查受影响行数可以帮助你排查ID不存在或无修改内容的情况，给用户更准确的反馈。

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

PHP CRUD应用更新功能失效问题求助

核心问题及修复方案 #

完整修正后的代码 #

额外说明 #

核心问题及修复方案

完整修正后的代码

额外说明