How do PHP and MySQL handle nested loop data in JSON?

How do PHP and MySQL handle nested loop data in JSON?

In modern web development, using JavaScript Object Notation (JSON) to transmit and store data has become a common way. JSON is a lightweight data interchange format that is easy to read and write, and is supported in a variety of programming languages. In PHP and MySQL, processing JSON data and doing nested loops is a common task. This article will introduce how to process JSON data in PHP and perform nested loop queries in MySQL.

First, let's look at a sample JSON data:

{
    "students": [
        {
            "name": "Alice",
            "age": 20,
            "subjects": [
                {
                    "name": "Math",
                    "score": 85
                },
                {
                    "name": "English",
                    "score": 92
                }
            ]
        },
        {
            "name": "Bob",
            "age": 22,
            "subjects": [
                {
                    "name": "Math",
                    "score": 78
                },
                {
                    "name": "English",
                    "score": 95
                }
            ]
        }
    ]
}

In PHP, we can use the json_decode function to decode a JSON string into a PHP array or object. Here is the sample code on how to decode the above sample JSON data:

$jsonString = '{
    "students": [
        {
            "name": "Alice",
            "age": 20,
            "subjects": [
                {
                    "name": "Math",
                    "score": 85
                },
                {
                    "name": "English",
                    "score": 92
                }
            ]
        },
        {
            "name": "Bob",
            "age": 22,
            "subjects": [
                {
                    "name": "Math",
                    "score": 78
                },
                {
                    "name": "English",
                    "score": 95
                }
            ]
        }
    ]
}';

$data = json_decode($jsonString, true);

// 将JSON字符串解码为关联数组

In the above code, we set the second parameter of json_decode to true so that JSON string decoded into associative array. If it is set to false or the parameter is omitted, it will be decoded as an object.

In PHP, we can use loops to traverse associative arrays to process nested JSON data. Here is the sample code on how to handle the above sample data:

foreach ($data['students'] as $student) {
    echo "Name: " . $student['name'] . "<br>";
    echo "Age: " . $student['age'] . "<br>";
    
    foreach ($student['subjects'] as $subject) {
        echo "Subject: " . $subject['name'] . "<br>";
        echo "Score: " . $subject['score'] . "<br>";
    }
    
    echo "<br>";
}

In the above code, we first access the students key of the array via ['students'] and then Loop through it. Each student's name and age can be accessed using the ['name'] and ['age'] keys. Next, we loop over the ['subjects'] array to access the name and score for each subject.

In MySQL, we can use nested SELECT statements to query nested JSON data. Here is the sample code on how to query the above sample data:

SELECT students.name AS student_name, subjects.name AS subject_name, subjects.score
FROM students, JSON_TABLE(students.data, '$.students[*]' COLUMNS (
    name VARCHAR(50) PATH '$.name',
    age INT PATH '$.age',
    data JSON PATH '$.subjects'
)) AS subjects

In the above code, we first query the name and data## in the students table #List. Then, use the JSON_TABLE function to parse the data column into nested JSON data and name the result subjects. In the second parameter of the JSON_TABLE function, we specified the path using '$students[*]' to select a nested one from the data column array. In the COLUMNS clause, specify the column to be queried and its data type and path.

json_decode function and loop traversal, we can process nested JSON data in PHP. By using nested SELECT statements and the JSON_TABLE function, we can query nested JSON data in MySQL. These techniques are very useful for processing complex JSON data structures.

The above is the detailed content of How do PHP and MySQL handle nested loop data in JSON?. For more information, please follow other related articles on the PHP Chinese website!