PHP开发MySQL API时两无关联表列求和相减实现问题
代码问题定位
- 差值计算逻辑完全错误:原代码书写的
$newamount = $stmttwo - $stmt,是直接对两个MySQLi预处理语句对象做算术减法,完全没有使用之前绑定取出的两个求和结果,且计算顺序和业务要求相反,无法产出正确数值。 - 聚合查询判断逻辑失效:SQL中的
SUM()属于无分组聚合函数,即使WHERE条件匹配不到任何记录,也会固定返回1行结果(值为NULL)。原代码中$stmt->num_rows > 0 && $stmttwo->num_rows > 0的判断会永远触发,完全起不到数据校验作用;同时如果某张表无对应仓库的记录,SUM返回的NULL直接参与计算会导致结果异常,需要将空值转换为0后再运算。 - 变量赋值逻辑混乱:原代码先将计算结果赋值给
$newamount,紧接着又将$newamount覆盖为数组,存在类型冲突风险。
修正后可运行代码
case 'amountleft': $warehouse = $_POST['warehouse']; // 查询purchases表对应仓库的采购总金额 $stmt = $conn->prepare("SELECT SUM(price) AS total_purchase FROM purchases WHERE warehouse = ?"); $stmt->bind_param("s",$warehouse); $stmt->execute(); $stmt->store_result(); $stmt->bind_result($total_purchase); $stmt->fetch(); // 无匹配记录时SUM返回NULL,统一转0做兼容 $total_purchase = $total_purchase ?? 0; // 查询fundruequest表对应仓库的资金申请总金额 $stmttwo = $conn->prepare("SELECT SUM(amountrequested) AS total_requsted FROM fundruequest WHERE warehouse = ?"); $stmttwo->bind_param("s",$warehouse); $stmttwo->execute(); $stmttwo->store_result(); $stmttwo->bind_result($total_requsted); $stmttwo->fetch(); $total_requsted = $total_requsted ?? 0; // 按业务规则计算剩余可用金额:采购总额 - 已申请总额 $newamount = $total_purchase - $total_requsted; // 组装接口响应 $response['error'] = false; $response['message'] = 'Amount Allocated Gotten'; $response['newamount'] = $newamount; break;
修正说明:去掉了无效的num_rows判断,适配聚合查询的返回特性;修正了差值计算的变量引用错误,统一对空求和值做0值兼容,完全匹配“同仓库采购总金额减去资金申请总金额得到剩余额度”的业务逻辑。
内容的提问来源于stack exchange,提问作者winfred adrah
相关产品推荐
相关产品推荐

