php - JSON 中的嵌套数组响应仅返回 Mysql 表中的最后一行

Question

我的数据库有三个表(category，catgory_details，questions)，现在一个类别有很多问题。我想要一个像这样的 JSON 响应:

[
  {
    "category": "Accountant",
    "deatils": {
      "video_link": "https://www.youtube.com/",
      "form_link": "https://docs.google.com/forms/u/0/",
      "questions": [
        "Who is your idiol",
        "What is ur name?"
      ]
    }
  },
  {
    "category": "Actuary",
    "deatils": {
      "video_link": "https://www.youtube.com/",
      "form_link": "https://docs.google.com/forms/u/0/",
      "questions": [
        "What is great?",
        "What is ur name?"

      ]
    }
  }
]

但我的代码只从问题表中返回一行，如下所示:

[
  {
    "category": "Accountant",
    "deatils": {
      "video_link": "https://www.youtube.com/",
      "form_link": "https://docs.google.com/forms/u/0/",
      "questions": [
        "Who is your idiol"
      ]
    }
  },
  {
    "category": "Actuary",
    "deatils": {
      "video_link": "https://www.youtube.com/",
      "form_link": "https://docs.google.com/forms/u/0/",
      "questions": [
        "What is great?"
      ]
    }
  }
]

以下是我的 php 代码:

<?php
header("Content-Type: application/json");
include('db.php');      
    $result = mysqli_query($conn,"SELECT * FROM categories ORDER BY id ASC"); 
    $json_response = array();
    while ($row = mysqli_fetch_array($result))
    {
        $row_array = array();
        $row_array['category'] = $row['category'];
        $id = $row['id'];

        $detail_query = mysqli_query($conn,"SELECT * FROM category_details WHERE category_id=$id");
        $question_query = mysqli_query($conn,"SELECT * FROM questions WHERE category_id=$id");
        if($question_query->num_rows !== 0){
        while ($detail_fetch = mysqli_fetch_array($detail_query))
        { 

         while ($question_fetch = mysqli_fetch_array($question_query))
        {  
            $row_array['deatils'] = array(
                'video_link' => $detail_fetch['video_link'],
                'form_link' => $detail_fetch['form_link'],
                'questions' => [$question_fetch['question'][1],$question_fetch['question'][2]],


                );


        }
    }

    }
    else{
        while ($detail_fetch = mysqli_fetch_array($detail_query))
        { 

            $myid = $detail_fetch['id'];
            $row_array['deatils'] = array(
                'video_link' => $detail_fetch['video_link'],
                'form_link' => $detail_fetch['form_link'],
                );

        }
    }
    array_push($json_response, $row_array); 
    }
    echo json_encode($json_response);

?>   

我应该进行哪些更改才能获得所需的 JSON 响应？

Answer 1

您不应在 question_fetch 循环中构建 $row_array['detils']，而应在 detail_fetch 循环中构建，然后仅填充 question_fetch 循环中的 questions 子数组

while ($detail_fetch = mysqli_fetch_array($detail_query))
{ 
    $row_array['deatils'] = array(
        'video_link' => $detail_fetch['video_link'],
         'form_link' => $detail_fetch['form_link'],
         'questions' => array(),
    );

    while ($question_fetch = mysqli_fetch_array($question_query))
    {  
        $row_array['deatils']['questions'][] = $question_fetch['question'];

    }
}