Insert JSON in multiple array data to MySQL using PHP

Question

I want to insert my JSON data to MySQL DB using PHP...

This is my JSON data called "data.json"

{
"allRoundData": [{
    "name": "Animals",
    "timeLimitInSeconds": 20,
    "pointsAddedForCorrectAnswer": 10,
    "questions": [{
        "questionText": "Lions are carnivores: true or false?",
        "answers": [{
            "answerText": "True",
            "isCorrect": true
        }, {
            "answerText": "False",
            "isCorrect": false
        }]
    }, {
        "questionText": "What do frogs eat?",
        "answers": [{
            "answerText": "Pizza",
            "isCorrect": false
        }, {
            "answerText": "Flies",
            "isCorrect": true
        }]
    }, {
        "questionText": "Where do mice live?",
        "answers": [{
            "answerText": "In the sea",
            "isCorrect": false
        }, {
            "answerText": "On the moon",
            "isCorrect": false
        }, {
            "answerText": "On land",
            "isCorrect": true
        }, {
            "answerText": "In a tree",
            "isCorrect": false
        }]
    }]
}]

}

This is my PHP script

<?php

include 'bl_Common.php';

$con = dbConnect();

 // use prepare statement for insert query
$st = mysqli_prepare($con, 'INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES (?, ?, ?, ?, ?, ?)');

// bind variables to insert query params
mysqli_stmt_bind_param($st, 'siisss', $name, $time, $points, $question, $answer, $isCorrect);

// read json file
$filename = 'data.json';
$json = file_get_contents($filename);   

echo $json;

//convert json object to php associative array
$data = json_decode($json, true);

// loop through the array
foreach ($data as $row) {

    // get the employee details
    $name = $row["allRoundData"]["name"];
    $time = $row['allRoundData']['timeLimitInSeconds'];
    $points = $row['allRoundData']['pointsAddedForCorrectAnswer'];
    $question = $row['allRoundData']['questions']['questionText'];
    $answer = $row['allRoundData']['answers']['answerText'];
    $isCorrect = $row['allRoundData']['answers']['isCorrect'];

    // execute insert query
    mysqli_stmt_execute($st);
}

//close connection
mysqli_close($con);

?>

I found an error that say "Notice: Undefined index: allRoundData in ..." But it's appearing there.

Answer 1

You need to use nested loop for getting all the values like this and try to understand the use of foreach . foreach is used to loop through the array to get the value until N'th values .

Foreach reference

    foreach ($array["allRoundData"] as $row) 
    {

        $name = $row["name"];
        $time = $row['timeLimitInSeconds'];
        $points = $row['pointsAddedForCorrectAnswer'];

       foreach($row['questions'] as $row1)
       {    
            $question= $row1['questionText'];

            foreach($row1['answers'] as $row2)
            {

                $answer = $row2['answerText'];

                $isCorrect= $row2['isCorrect'];

                echo "INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('". $name."',".$time.",".$points.",'". $question."','". $answer ."','".  $isCorrect."') </br>";  
            }

       }


    }

OUTPUT :

INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'Lions are carnivores: true or false?','True','1') 

INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'Lions are carnivores: true or false?','False','') 

 INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'What do frogs eat?','Pizza','') 

 INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'What do frogs eat?','Flies','1') 

 INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'Where do mice live?','In the sea','') 

INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'Where do mice live?','On the moon','') 

 INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'Where do mice live?','On land','1') 

INSERT INTO data (round_name, time_limit, points_added, question_text, answer_text, isCorrect) VALUES ('Animals',20,10,'Where do mice live?','In a tree','') 

Answer 2

Your loop is going wrong, you need to loop data inside the allRoundData. So, please try this code

foreach ($data["allRoundData"] as $row) {

    // get the employee details
    $name = $row["name"];
    $time = $row['timeLimitInSeconds'];
    $points = $row['pointsAddedForCorrectAnswer'];
    $question = $row['questions']['questionText'];//wrong code
    $answer = $row['answers']['answerText'];//wrong code
    $isCorrect = $row['answers']['isCorrect'];//wrong code

    // execute insert query
    mysqli_stmt_execute($st);
}

the comment //wrong code i have written because those are arrays, and you can't directly access without index you should use $row['answers'][$index]['answerText'] where $index is from 0 to count($row['answers'])-1

If you need static results then use specific index, otherwise you need to loop those variables to get your desired result

Answer 3

You’ve got lost in the complexity of the JSON data:

• Your JSON represents an array with one key: $data['allRoundData'].
• In turn, it has one element $data['allRoundData'][0]
• In that array, you have $data['allRoundData'][name],$data['allRoundData'][timeLimitInSeconds], $data['allRoundData'][pointsAddedForCorrectAnswer]
• After that you have another array: $data['allRoundData'][questions]

You should get the preliminary data directly from $data['allRoundData'][0], and then get the actual questions using:

foreach($data['allRoundData'][0][questions] as $question) {}