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?

You are overwriting your own values, everywhere where you use $row_array['deatils'] = ... you need something like $row_array['deatils'][] = .... — jeroen
– jeroen, Commented Oct 5, 2017 at 12:13

Patrick Q · Accepted Answer · 2017-10-05 12:13:47Z

2

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

    }
}

answered Oct 5, 2017 at 12:13

Patrick Q

6,3942 gold badges28 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Usama Iftikhar Over a year ago

Yes, It worked fine for me thanks, cant vote it due to less reputation

Tudor Danes · Accepted Answer · 2017-10-05 12:08:47Z

0

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.

answered Oct 5, 2017 at 12:08

Tudor Danes

451 silver badge7 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related