如何使用PHP更新数组对应的数据库特定地址条目

问题原因 #

你当前的地址更新语句WHERE条件仅筛选了orderId和addressType，同一订单下相同类型的所有地址都会被匹配到，所以会出现批量更新的问题，要实现单条地址更新，需要把地址表的唯一行id作为更新的核心筛选条件。

改造步骤 #

1. 调整前端传参结构：原来的$addressPickup、$addressDelivery是纯地址字符串数组，现在需要改为二维数组，每个元素包含对应地址的行id和地址内容，新增的未入库地址可单独标识出来走插入逻辑。
2. 修改更新逻辑的SQL语句，新增行id作为WHERE条件。

修改后代码示例 #

// update new supplier order
function updateSupplierOrder($conn, $orderDate, $datePickup, $dateDelivery, $timePickup, $timeDelivery, $car, $carType, $goodsDescription, $paletChange, $paletNo, $supplier, $orderObservation, $paymentDate, $value, $addressPickup, $addressDelivery, $userid, $orderID) {

    $sql1 = "UPDATE suppliersOrders SET supplierId = ?, date = ?, datePickup = ?, timePickup = ?, goodsDescription = ?, dateDelivery = ?, timeDelivery = ?, carType = ?, carNo = ?, paletChange = ?, paletNo = ?, value = ?, invoice = ?, observations = ?, operator = ? WHERE id = ?;";
    $stmt1 = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt1, $sql1)) {
        header ("location: ../suppliersOrders?error=failedupdateorder");
        exit();
    }

    mysqli_stmt_bind_param($stmt1, "isssssssisisssii", $supplier, $orderDate, $datePickup, $timePickup, $goodsDescription, $dateDelivery, $timeDelivery, $carType, $car, $paletChange, $paletNo, $value, $paymentDate, $orderObservation, $userid, $orderID);
    mysqli_stmt_execute($stmt1);
    mysqli_stmt_close($stmt1);

    // 更新取货地址
    for ($i=0; $i<count($addressPickup); $i++) {
        $addressItem = $addressPickup[$i];
        $addressId = $addressItem['id']; // 地址表行id
        $address = $addressItem['content'];
        $type = '1';
    
        // 新增行id作为筛选条件
        $sql2 = "UPDATE suppliersOrdersAddress SET address = ?, operator = ? WHERE id = ? AND orderId = ? AND addressType = ?;";
        $stmt2 = mysqli_stmt_init($conn);   
        if (!mysqli_stmt_prepare($stmt2, $sql2)) {
            header ("location: ../suppliersOrders?error=failedupdateaddress");
            exit();
        }
        // 参数类型对应调整
        mysqli_stmt_bind_param($stmt2, "siiii", $address, $userid, $addressId, $orderID, $type);
        mysqli_stmt_execute($stmt2);
        mysqli_stmt_close($stmt2);
    }

    // 更新送货地址
    for ($i=0; $i<count($addressDelivery); $i++) {
        $addressItem = $addressDelivery[$i];
        $addressId = $addressItem['id']; // 地址表行id
        $address = $addressItem['content'];
        $type = '2';
    
        $sql2 = "UPDATE suppliersOrdersAddress SET address = ?, operator = ? WHERE id = ? AND orderId = ? AND addressType = ?;";
        $stmt2 = mysqli_stmt_init($conn);   
        if (!mysqli_stmt_prepare($stmt2, $sql2)) {
            header ("location: ../suppliersOrders?error=failedupdateaddress");
            exit();
        }
        mysqli_stmt_bind_param($stmt2, "siiii", $address, $userid, $addressId, $orderID, $type);
        mysqli_stmt_execute($stmt2);
        mysqli_stmt_close($stmt2);
    }
        
    header("location: ../suppliersOrders-edit.php?id=$orderID");
}

注意事项 #

• 前端编辑页面渲染已有地址时，需要给每个地址项加隐藏域存储对应地址的行id，提交时一并传入后端
• 如果业务存在新增地址的场景，提交上来没有行id的地址项不要走上述更新逻辑，需要单独写INSERT语句入库
• 如果业务允许删除地址，可额外增加逻辑：先查询该订单下所有地址的行id，和提交上来的地址id做比对，删除不存在于提交列表中的地址行

内容的提问来源于stack exchange，提问作者Andrei Valentin Niculae