PHP从SQL联接结果构建嵌套数组的有效方法

[
  {
    "id": "x",
    "title": "x",
    "questions": [
      {"id": "x", "text": "x"},
      {"id": "x2", "text": "x2"}
    ]
  }
]

SELECT 
    questionnaires.id AS QuestionnaireId, 
    questionnaires.title AS QuestionnaireTitle, 
    questions.id AS QuestionId, 
    questions.text AS Question 
FROM 
    questionnaires 
INNER JOIN 
    questionnaireshasquestions qa ON qa.idQuestionnaire = questionnaires.id 
INNER JOIN 
    questions ON questions.id = qa.idQuestion;

$data = [];
while ($row = $conn->fetch()) {
    if (!isset($data['questionnaires'][$row['QuestionnaireId']])) {
        $data['questionnaires'][] = [
            'id' => $row['QuestionnaireId'],
            'title' => $row['QuestionnaireTitle'],
            'questions' => [
                'id' => $row['QuestionId'],
                'text' => $row['Question']
            ]
        ]; 
    } else {
        // 这里的逻辑可能导致问题，例如覆盖或不正确的追加
        $data['questionnaires'][$row['QuestionnaireId']][] = [
           'questions' => [
                'id' => $row['QuestionId'],
                'text' => $row['Question']
            ]           
        ];  
    }
}

<?php

// 假设 $conn 是您的数据库连接对象，并且 $conn->fetch() 方法可以逐行获取查询结果
// 示例数据模拟，实际应用中替换为您的数据库查询循环
$rows = [
    ['QuestionnaireId' => '1', 'QuestionnaireTitle' => 'Are you hungry?', 'QuestionId' => '1', 'Question' => 'How is your passion?'],
    ['QuestionnaireId' => '1', 'QuestionnaireTitle' => 'Are you hungry?', 'QuestionId' => '2', 'Question' => 'Do you drink?'],
    ['QuestionnaireId' => '2', 'QuestionnaireTitle' => 'How are you feeling?', 'QuestionId' => '1', 'Question' => 'How is your passion?'],
    ['QuestionnaireId' => '2', 'QuestionnaireTitle' => 'How are you feeling?', 'QuestionId' => '3', 'Question' => 'Do you like fish?'],
    ['QuestionnaireId' => '5', 'QuestionnaireTitle' => 'Is testing working?', 'QuestionId' => '4', 'Question' => 'How is the testing?']
];

$data = ['questionnaires' => []]; // 初始化一个用于存放最终数据的数组

foreach ($rows as $row) { // 实际应用中替换为 while ($row = $conn->fetch())
    $questionnaireId = $row['QuestionnaireId'];

    // 检查当前问卷ID是否已经存在于 $data['questionnaires'] 中
    if (!isset($data['questionnaires'][$questionnaireId])) {
        // 如果是第一次遇到这个问卷ID，则初始化问卷信息
        // 并为其 'questions' 字段初始化一个空数组，用于后续添加问题
        $data['questionnaires'][$questionnaireId] = [
            'id'        => $row['QuestionnaireId'],
            'title'     => $row['QuestionnaireTitle'],
            'questions' => [], // 初始化一个空数组来收集问题
        ];
    }

    // 将当前行的问题信息添加到对应问卷的 'questions' 数组中
    $data['questionnaires'][$questionnaireId]['questions'][] = [
        'id'   => $row['QuestionId'],
        'text' => $row['Question']
    ];
}

// 由于我们使用了问卷ID作为键，最终的 $data['questionnaires'] 会是一个关联数组
// 如果需要一个索引数组（数字键），可以使用 array_values()
$finalQuestionnaires = array_values($data['questionnaires']);

// 打印最终的JSON格式数据
echo json_encode($finalQuestionnaires, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);

?>

[
    {
        "id": "1",
        "title": "Are you hungry?",
        "questions": [
            {
                "id": "1",
                "text": "How is your passion?"
            },
            {
                "id": "2",
                "text": "Do you drink?"
            }
        ]
    },
    {
        "id": "2",
        "title": "How are you feeling?",
        "questions": [
            {
                "id": "1",
                "text": "How is your passion?"
            },
            {
                "id": "3",
                "text": "Do you like fish?"
            }
        ]
    },
    {
        "id": "5",
        "title": "Is testing working?",
        "questions": [
            {
                "id": "4",
                "text": "How is the testing?"
            }
        ]
    }
]