PHP整合两张表数据：普通查询与GROUP BY分组查询问题

问题说明 #

需要从两张表获取并整合数据：

• stock表：查询全量数据
• vendor_stock_saved表：按product_sn分组查询聚合后的供应商及价格数据
  尝试通过嵌套while循环整合数据失败，最终希望将整合后的数据转为JSON格式用于渲染。

表结构关联说明：stock表的itemcode字段与vendor_stock_saved表的product_sn字段为关联字段，用于匹配对应商品的供应商数据。

原代码问题分析 #

1. 重复建立数据库连接，造成资源浪费
2. 嵌套while循环逻辑错误：外层每遍历一条stock数据，内层循环会一次性耗尽vendor_stock_saved的查询结果，后续stock数据无法匹配到分组数据
3. 未通过关联字段匹配对应数据，导致数据关联错误
4. JSON生成逻辑未正确启用，且数据整合逻辑不完整

修正后的代码 #

<?php
error_reporting(E_ALL);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
ini_set('log_errors', 1);

// 数据库连接（仅建立一次）
$hostname = 'localhost';
$username = 'root';
$password = '';
$database = 'garage';
$con = mysqli_connect($hostname, $username, $password, $database) or die("Error " . mysqli_error($con));

// 1. 先查询vendor_stock_saved的分组数据，转为以product_sn为键的数组，方便快速匹配
$vendorSql = "SELECT product_sn, GROUP_CONCAT(vendor) as grpd_vendors, GROUP_CONCAT(price_exclvat) as grpd_exclvat, GROUP_CONCAT(price_inclvat) as grpd_inclvat FROM vendor_stock_saved GROUP BY product_sn ORDER BY vendor";
$vendorResult = mysqli_query($con, $vendorSql);
$vendorData = [];
while ($row = mysqli_fetch_assoc($vendorResult)) {
    // 以product_sn作为键，存储对应的聚合数据
    $vendorData[$row['product_sn']] = $row;
}

// 2. 查询stock表全量数据
$stockSql = "SELECT id, itemcode, sdesc, sbrand, rprice, vendor, costExclTax, costInclTax, qtyonhand from stock";
$stockResult = mysqli_query($con, $stockSql);

$array = [];
while ($stockRow = mysqli_fetch_assoc($stockResult)) {
    // 通过itemcode匹配对应的vendor分组数据
    $matchedVendor = $vendorData[$stockRow['itemcode']] ?? [];
    
    // 生成更新按钮HTML
    $updateButton = "<button class='btn btn-sm btn-info' data-id='".$stockRow['id']."' style='float: right !important; background: #5E72E4;' ><i class='fa fa-pencil' style='margin-right: 5px;'></i> EDIT </button>";
    
    // 处理价格显示，若无匹配数据则显示默认提示
    $exclprices = "<strong>EXCL.TAX: </strong>". ($matchedVendor['grpd_exclvat'] ?? '无数据');
    $inclprices = "<strong>INCL.TAX: </strong>". ($matchedVendor['grpd_inclvat'] ?? '无数据');
    $combinedprices = $exclprices ."<br/>". $inclprices;
    
    // 整合数据到数组
    $array[] = [
        "sdesc" => $stockRow['sdesc'],
        "sbrand" => $stockRow['sbrand'],
        "rprice" => $stockRow['rprice'],
        "vendor" => $matchedVendor['grpd_vendors'] ?? '无数据',
        "combinedprices" => $combinedprices,
        "qtyonhand" => $stockRow['qtyonhand'],
        "action" => $updateButton,
    ];
}

// 生成符合要求的JSON数据
$dataset = [
    "echo" => 1,
    "totalrecords" => count($array),
    "totaldisplayrecords" => count($array),
    "data" => $array
];
echo json_encode($dataset);

// 关闭数据库连接
mysqli_close($con);
?>

关键优化点 #

• 复用单一数据库连接，减少资源消耗
• 将分组查询结果预存为关联数组，通过itemcode快速匹配对应数据，彻底解决嵌套循环的逻辑错误
• 增加无匹配数据时的默认值处理，避免页面报错
• 正确启用JSON生成逻辑，确保输出格式符合前端渲染要求

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