插入table1时更新table2的PHP代码问题求助

原代码核心问题 #

• 未从inv_tbl获取当前库存的inhouse和damaged值，直接使用未定义变量运算
• 变量声明缺失$符号、语句末尾缺少分号，存在语法错误
• 库存更新逻辑不符合业务场景，未正确处理出库、退货、损坏的数量关系
• 仅定义更新SQL语句但未执行
• 无事务控制，可能出现插入成功但库存更新失败的数据不一致问题

修正后的完整代码 #

<?php 
include("../connection.php");
// ADD INVENTORY

if(isset($_REQUEST['add'])){
    // 开启事务，保证操作原子性
    mysqli_begin_transaction($connections);

    try {
        // 赋值并转义变量
        $customer = mysqli_real_escape_string($connections, $_POST['customer']);
        $transaction = mysqli_real_escape_string($connections, $_POST['transaction']);
        date_default_timezone_set('Asia/Manila');
        $time = date('Y-m-d H:i:s');
        $item = mysqli_real_escape_string($connections, $_POST['item']);
        // 转换为数值类型避免运算错误
        $quantity = (int)mysqli_real_escape_string($connections, $_POST['quantity']);
        $price = (float)mysqli_real_escape_string($connections, $_POST['price']);
        $returns = (int)mysqli_real_escape_string($connections, $_POST['returns']);
        $damaged = (int)mysqli_real_escape_string($connections, $_POST['damaged']);
        $status = 0;

        $totalprice = $price * $quantity;

        // 插入交易记录
        $query = "INSERT INTO transac_tbl VALUES ('', '$customer', '$transaction', '$time', '$item', '$quantity', '$price', '$totalprice', '$returns', '$damaged', '$status')";
        $execute = mysqli_query($connections, $query);

        if (!$execute) {
            throw new Exception("插入交易失败: " . mysqli_error($connections));
        }

        addLog($connections,"Added a transaction");

        // 获取当前库存数据
        $invQuery = "SELECT inhouse, damaged FROM inv_tbl WHERE id=1";
        $invResult = mysqli_query($connections, $invQuery);
        $invData = mysqli_fetch_assoc($invResult);

        if (!$invData) {
            throw new Exception("未找到库存记录");
        }

        // 计算更新后库存值
        $currentInhouse = $invData['inhouse'];
        $currentDamaged = $invData['damaged'];

        // 业务逻辑：出库减库存，退货加库存，损坏减库存并增加损坏数
        $newInhouse = $currentInhouse - $quantity + $returns - $damaged;
        $newDamaged = $currentDamaged + $damaged;

        // 更新库存表
        $updateSql = "UPDATE inv_tbl SET inhouse = $newInhouse, damaged = $newDamaged WHERE id=1";
        $updateExecute = mysqli_query($connections, $updateSql);

        if (!$updateExecute) {
            throw new Exception("更新库存失败: " . mysqli_error($connections));
        }

        // 提交事务
        mysqli_commit($connections);
        header('location: index.php?success=1');
        exit;
    } catch (Exception $e) {
        // 回滚事务
        mysqli_rollback($connections);
        // 传递错误信息到前端
        header('location: index.php?error=' . urlencode($e->getMessage()));
        exit;
    }
}

header('location: index.php');
// ADD INVENTORY END
?>

关键改进说明 #

1. 事务控制：用事务保证插入和更新操作原子性，避免数据不一致
2. 数据类型转换：将数量、价格转为数值类型，防止字符串运算错误
3. 正确库存逻辑：
   • 出库quantity：库存inhouse减对应数量
   • 退货returns：库存inhouse加对应数量
   • 损坏damaged：库存inhouse减对应数量，同时损坏数damaged加对应数量
4. 错误处理：捕获异常并反馈错误信息，便于调试
5. 语法修正：补全变量符号和语句分号，修复原语法错误

表结构参考 #

transac_tbl结构 #

inv_tbl结构 #

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