Create nested json using php mysql

Question

i have three tables Named below

Lecture(lec_id, name, description)
Test (test_id, test_name, lec_id, date)
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);

Naincy · Accepted Answer · 2017-02-27 15:59:23Z

4

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

edited Feb 27, 2017 at 15:59

answered Feb 27, 2017 at 12:52

Naincy

2,9431 gold badge15 silver badges21 bronze badges

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

7 Comments

Bilal Ch Over a year ago

#naincy thanks for your answer, i am getting error in while loop, its "Parse error: syntax error, unexpected '[' " while($test_fet = mysqli_fetch_array($test_query)){ $json[$i]['test']['question'][] = ['q_name' => $test_fet['q_name'], 'description' => $test_fet['q_desc']]; }

Naincy Over a year ago

@BilalCh It should not give error ... :( can you make sure you copy pasted properly... and this error on which line no and what is written in that line can u share

Bilal Ch Over a year ago

#naincy please take a look on these screen shots prnt.sc/edytln prnt.sc/edytrv

Jay Blanchard Over a year ago

Do or do not. There is no "try". A good answer will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO.

Naincy Over a year ago

@BilalCh which is line 33?

|

Collectives™ on Stack Overflow

Create nested json using php mysql

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related