PHP多表匹配查询问题求助：重复响应与数据结构异常

我看到你遇到了接口返回重复结果、响应结构不符合预期的问题，咱们来一步步修复这段代码：

问题分析 #

你的代码里有几个关键问题导致了当前的异常：

• 内层的election_mul查询结果集只会被读取一次：第一次外层循环时，fetch()会把结果集指针移到末尾，后续外层循环就再也读不到数据了
• $resOutput2没有在每次外层循环时重置，导致每次迭代都会累加之前的政党数据，造成重复内容
• 代码末尾重复执行了echo json_encode(...)，会让响应输出两次结果
• 外层循环每次都会覆盖$resOutput["election1"]，如果有多个选举记录，最终只会保留最后一条

修改后的代码 #

<?php
// include db details
include_once('include/conn.php');
header("Content-type: application/json");

/* ----- Get Request Method, Path Info, Input JSON ----- */
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $reqInput = file_get_contents('php://input');
}

$database = new Database();
$db = $database->getConnection();
$resOutput = array();

try {
    $lclQuery1 = " SELECT el_id as id , el_title as title , el_start_date as startDate , el_end_date as endDate , el_status FROM election WHERE el_status = 0 AND el_election_status = 2 GROUP BY title ";
    $qryResult1 = $db->query($lclQuery1);

    if($qryResult1->rowCount() > 0) {
        $resOutput["status"] = 1;
        $resOutput["msg"] = "election";
        
        // 若仅需单个选举记录，可直接用对象而非数组；支持多个则保留数组形式
        $resOutput["election1"] = [];
        
        while($row1 = $qryResult1->fetch(PDO::FETCH_ASSOC)) {
            $resOutput1["desc"] = $row1;
            $resOutput2 = []; // 每次循环重置政党数据数组，避免重复累加
            
            // 按当前选举ID精准查询对应政党，替代全局查询后过滤，性能更优
            $lclQuery2 = " SELECT em_id as id1 , em_el_id AS emID , em_party_name AS partyName , em_party_image AS partyImage , em_total_seat AS totalSeat , em_lost_seat AS lostSeat , em_won_seat AS wonSeat , em_pending_seat AS pendingSeat , em_status FROM election_mul WHERE em_status = 0 AND em_el_id = :el_id ";
            $stmt = $db->prepare($lclQuery2);
            $stmt->bindParam(':el_id', $row1['id'], PDO::PARAM_INT);
            $stmt->execute();
            $qryResult2 = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            foreach($qryResult2 as $row2) {
                $resOutput2[] = [
                    'id1' => $row2["id1"],
                    'partyName' => $row2["partyName"],
                    'partyImage' => $row2["partyImage"],
                    'totalSeat' => $row2["totalSeat"],
                    'lostSeat' => $row2["lostSeat"],
                    'wonSeat' => $row2["wonSeat"],
                    'pendingSeat' => $row2["pendingSeat"]
                ];
            }
            
            $resOutput1["election"] = $resOutput2;
            // 支持多个选举则添加到数组；仅需单个则直接赋值并break跳出循环
            $resOutput["election1"][] = $resOutput1;
            // 单个选举场景：$resOutput["election1"] = $resOutput1; break;
        }
        
        // 仅输出一次JSON结果，避免重复响应
        echo json_encode($resOutput);
    } else {
        $resOutput["status"] = 0;
        $resOutput["msg"] = "No Records";
        echo json_encode($resOutput);
    }
    
    $db = null;
} catch (Exception $e) {
    // 异常时返回结构化JSON错误响应，符合接口规范
    echo json_encode([
        'status' => 0,
        'msg' => 'Error: ' . $e->getMessage()
    ]);
}
?>

修改说明 #

1. 精准查询政党数据：用预处理语句根据当前选举ID查询对应政党，既解决了结果集指针耗尽的问题，也提升了查询性能
2. 重置临时数组：每次外层循环开始时清空$resOutput2，防止旧数据累加导致重复
3. 移除重复输出：确保接口只返回一次JSON结果，避免重复响应
4. 灵活适配结果数量：代码中注释了单个/多个选举记录的调整方式，可根据实际需求选择
5. 优化异常处理：捕获异常时返回结构化的错误JSON，更符合接口的统一响应规范

这样修改后，接口就能返回你预期的格式，也不会出现重复结果的问题了。

内容的提问来源于stack exchange，提问作者J.Doe