Nested array response in JSON returning only last row from Mysql table

Question

My database have three tables(category,catgory_details,questions), Now one category have many questions. I want to have a JSON response like this:

[
  {
    "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?"

      ]
    }
  }
]

but my code is returning only one row from questions tables like this:

[
  {
    "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?"
      ]
    }
  }
]

Following is my php code:

<?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);

?>   

What changes should I make in order to get my required JSON response?

Answer 1

Instead of building $row_array['deatils'] within the question_fetch loop, you should do it within the detail_fetch loop, and then populate just the questions sub-array within the question_fetch loop

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'];

    }
}

Answer 2

Try to change :
'questions' => [$question_fetch['question'][1],$question_fetch['question'][2]],
to :
'questions' => $question_fetch['question'],

So you will have the full array of questions included in the response.