PHP中基于ID精确更新数据库数组数据的实践指南

UPDATE suppliersOrdersAddress SET address = ?, operator = ? WHERE orderId = ? AND addressType = ?;

// 假设前端传递过来的地址数据结构如下：
$addressPickup = [
    ['id' => 101, 'address' => '新取货地址A'],
    ['id' => 102, 'address' => '新取货地址B']
];

$addressDelivery = [
    ['id' => 201, 'address' => '新送货地址X'],
    ['id' => 202, 'address' => '新送货地址Y']
];

<?php

// 示例函数：更新供应商订单及其关联地址
function updateSupplierOrder($conn, $orderDate, $datePickup, $dateDelivery, $timePickup, $timeDelivery, $car, $carType, $goodsDescription, $paletChange, $paletNo, $supplier, $orderObservation, $paymentDate, $value, $addressPickup, $addressDelivery, $userid, $orderID) {

    // 1. 更新主订单信息 (此部分与原逻辑相同)
    $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);

    // 2. 更新取货地址 (基于地址ID进行精确更新)
    // 假设 $addressPickup 是一个包含 ['id' => ..., 'address' => ...] 结构数组的数组
    foreach ($addressPickup as $addressData) {
        $addressId = $addressData['id']; // 获取地址的唯一ID
        $address = $addressData['address']; // 获取地址内容
        $type = '1'; // 取货地址类型

        // 修改 SQL 语句，在 WHERE 子句中加入 AND id = ?
        $sql2 = "UPDATE suppliersOrdersAddress SET address = ?, operator = ? WHERE orderId = ? AND addressType = ? AND id = ?;";
        $stmt2 = mysqli_stmt_init($conn);
        if (!mysqli_stmt_prepare($stmt2, $sql2)) {
            header ("location: ../suppliersOrders?error=failedupdateaddress");
            exit();
        }
        // 绑定参数时，额外绑定地址ID
        // "siiii" -> s(address), i(userid), i(orderId), i(type), i(addressId)
        mysqli_stmt_bind_param($stmt2, "siiii", $address, $userid, $orderID, $type, $addressId);
        mysqli_stmt_execute($stmt2);
        mysqli_stmt_close($stmt2);
    }

    // 3. 更新送货地址 (基于地址ID进行精确更新)
    // 假设 $addressDelivery 是一个包含 ['id' => ..., 'address' => ...] 结构数组的数组
    foreach ($addressDelivery as $addressData) {
        $addressId = $addressData['id']; // 获取地址的唯一ID
        $address = $addressData['address']; // 获取地址内容
        $type = '2'; // 送货地址类型

        // 修改 SQL 语句，在 WHERE 子句中加入 AND id = ?
        $sql2 = "UPDATE suppliersOrdersAddress SET address = ?, operator = ? WHERE orderId = ? AND addressType = ? AND id = ?;";
        $stmt2 = mysqli_stmt_init($conn);
        if (!mysqli_stmt_prepare($stmt2, $sql2)) {
            header ("location: ../suppliersOrders?error=failedupdateaddress");
            exit();
        }
        // 绑定参数时，额外绑定地址ID
        // "siiii" -> s(address), i(userid), i(orderId), i(type), i(addressId)
        mysqli_stmt_bind_param($stmt2, "siiii", $address, $userid, $orderID, $type, $addressId);
        mysqli_stmt_execute($stmt2);
        mysqli_stmt_close($stmt2);
    }

    // 更新完成后重定向
    header("location: ../suppliersOrders-edit.php?id=$orderID");
    exit(); // 确保重定向后代码不再执行
}

// 示例用法 (假设 $conn 已建立数据库连接)
// $conn = mysqli_connect("localhost", "user", "password", "database");
// updateSupplierOrder($conn, /* ...其他参数... */, $addressPickup, $addressDelivery, $userid, $orderID);

?>