php - 使用PHP将JSON中的多个数组数据插入MySQL

我想使用PHP将JSON数据插入MySQL数据库...

这是我的JSON数据，称为“ 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
        }]
    }]
}]

}

这是我的PHP脚本

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

？>

我发现一个错误，内容为“注意：未定义的索引：...中的allRoundData”
但是它出现在那里。

最佳答案

您需要使用嵌套循环来获取所有此类值，并尝试了解foreach的用法。 foreach用于遍历数组以获取直到第N个值的值。

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>";
            }

       }


    }

输出：

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','')