Core PHP+SQLite：如何结合两张表展示商品入库出库及结存数据？

1. 安全的SQL查询语句（规避注入风险） #

直接拼接用户输入会引发SQL注入，必须使用参数绑定。以下SQL通过子查询分别统计指定商品的采购总量（Inward）和销售总量（Outward），用COALESCE处理无记录时的NULL值，确保计算逻辑正常：

SELECT
    (SELECT COALESCE(SUM(quantity), 0) FROM purchase_details_master WHERE item_id = ?) AS Inward,
    (SELECT COALESCE(SUM(quantity), 0) FROM sales_details_master WHERE item_id = ?) AS Outward

2. Core PHP 实现代码 #

替换原有代码，使用参数绑定执行查询并计算结存：

<?php
// 假设$conn是已建立的SQLite连接实例
$item = $_POST['item'];

// 准备带占位符的SQL语句
$sql = "SELECT
            (SELECT COALESCE(SUM(quantity), 0) FROM purchase_details_master WHERE item_id = ?) AS Inward,
            (SELECT COALESCE(SUM(quantity), 0) FROM sales_details_master WHERE item_id = ?) AS Outward";

// 初始化预处理语句
$stmt = $conn->prepare($sql);
// 绑定参数（两个占位符对应同一个商品ID）
$stmt->bindValue(1, $item, SQLITE3_TEXT);
$stmt->bindValue(2, $item, SQLITE3_TEXT);

// 执行查询并获取结果
$result = $stmt->execute();
$row = $result->fetchArray(SQLITE3_ASSOC);

// 期初库存：根据实际业务逻辑设置，可从库存表读取或固定值
$opening = 0;
// 计算结存
$closing = $opening + $row['Inward'] - $row['Outward'];

// 输出结果示例
echo "商品ID: {$item}<br>";
echo "期初库存(Opening): {$opening}<br>";
echo "采购数量(Inward): {$row['Inward']}<br>";
echo "销售数量(Outward): {$row['Outward']}<br>";
echo "结存(Closing): {$closing}<br>";
?>

3. 扩展说明 #

• 若系统存在单独的期初库存表（如stock_master），可修改SQL直接关联查询并计算结存：

  SELECT
      sm.opening AS Opening,
      COALESCE(pdm.total_inward, 0) AS Inward,
      COALESCE(sdm.total_outward, 0) AS Outward,
      sm.opening + COALESCE(pdm.total_inward, 0) - COALESCE(sdm.total_outward, 0) AS Closing
  FROM stock_master sm
  LEFT JOIN (SELECT item_id, SUM(quantity) AS total_inward FROM purchase_details_master GROUP BY item_id) pdm ON sm.item_id = pdm.item_id
  LEFT JOIN (SELECT item_id, SUM(quantity) AS total_outward FROM sales_details_master GROUP BY item_id) sdm ON sm.item_id = sdm.item_id
  WHERE sm.item_id = ?
  

• 所有用户输入必须通过参数绑定处理，杜绝SQL注入风险。

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