Create nested json using php mysql

Question

i have three tables Named below

1. Lecture(lec_id, name, description)
2. Test (test_id, test_name, lec_id, date)
3. Question (q_id, q_name, q_desc, test_id)

i want to generate json response like this

{
"lec_name": "Math",
"description": "Can you identify these brands by the background color?",
"test": [
    {
        "name": "Algebra",
        "date": "10-6-2017",
        "question": [
            {
                "q_name": "question 1",
                "description": "Lorem Ipsum is simply dummy text of the printing",
            },
            {
                "q_name": "question 2",
                "description": "Lorem Ipsum is simply dummy text of the printing",
            },
            {
                "q_name": "question 3",
                "description": "Lorem Ipsum is simply dummy text of the printing",
            }
        ]
    }


] }

but i am getting like this

[
    [
        {
            "algebra": "2017-02-28"
        }
    ],
    {
        "question 1": "Lorem Ipsum is simply dummy text of the printing"
    },
    {
        "0": "Math",
        "1": "1",
        "name": "Math",
        "lec_id": "1"
    },
    [
        {
            "trigonometry": "2017-02-28"
        }
    ],
    {
        "question 2": "Lorem Ipsum is simply dummy text of the printing"
    },
    {
        "0": "Chemistry",
        "1": "2",
        "name": "Chemistry",
        "lec_id": "2"
    },
    [
        {
            "Bio test 1": "2017-02-26"
        }
    ],
    {
        "question 3": "Lorem Ipsum is simply dummy text of the printing"
    },
    {
        "0": "Physics",
        "1": "3",
        "name": "Physics",
        "lec_id": "3"
    },
    [
        {
            "Bio test 2": "2017-02-28"
        }
    ],
    {
        "question 4": "Lorem Ipsum is simply dummy text of the printing"
    },
    {
        "0": "Biology",
        "1": "4",
        "name": "Biology",
        "lec_id": "4"
    }
]

Here is my code,

    $sql = "SELECT name, lec_id FROM lecture";

$sqlRun = mysqli_query($conn , $sql);
//var_dump($sqlRun);
//echo $sqlRun;     
$json = array();
$total_records = mysqli_num_rows($sqlRun);

if($total_records > 0){
    while($row = mysqli_fetch_array($sqlRun)){
        $row_array= array();


        $qus_pk = $row['lec_id'];
        $lec_desc = '';
        $lec_name = '';

        $option_qry = mysqli_query($conn, "SELECT test_name, date, test_id FROM test WHERE test_id= $qus_pk");
        //$option_qry = mysqli_query($conn, "SELECT t.name");
        while($opt_fet = mysqli_fetch_array($option_qry)){
            $row_array[]= array(
                $opt_fet['test_name'] => $opt_fet['date'],

            );
            $quest_array = array();
            $quest_pk = $opt_fet['test_id'];
            $test_query = mysqli_query($conn, "SELECT q_name, q_desc FROM question WHERE q_id = $quest_pk");
            while($test_fet = mysqli_fetch_array($test_query)){
                $quest_array= array(
                    $test_fet['q_name'] => $test_fet['q_desc'],
                );
            }
        }
        array_push($json, $row_array, $quest_array);
        $json[] = $row;
    }
}


echo json_encode($json);

Answer 1

Change your code by this.

if($total_records > 0){
    $i = 0;
    while($row = mysqli_fetch_array($sqlRun)){
        $row_array= array();
        $qus_pk = $row['lec_id'];
        $json[$i]['lec_name'] = $row['name'];    
        $json[$i]['description'] = $row['description'];    

        $option_qry = mysqli_query($conn, "SELECT test_name, date, test_id FROM test WHERE test_id= $qus_pk");
        //$option_qry = mysqli_query($conn, "SELECT t.name");
        while($opt_fet = mysqli_fetch_array($option_qry)){
            $json[$i]['test']['name'] = $opt_fet['name'];
            $json[$i]['test']['date'] = $opt_fet['date'];

            $quest_array = array();
            $quest_pk = $opt_fet['test_id'];
            $test_query = mysqli_query($conn, "SELECT q_name, q_desc FROM question WHERE q_id = $quest_pk");
            $j = 0;
            while($test_fet = mysqli_fetch_array($test_query)){
                $json[$i]['test']['question'][$j] = array('q_name' => $test_fet['q_name'], 'description' => $test_fet['q_desc']);
                $j++;
            }
        }
        $i++;
    }
}


echo json_encode($json);