How do PHP and MySQL handle nested JSON data?

How do PHP and MySQL handle nested JSON data?

In modern web applications, processing and transmitting data has become an important part of the development process. Traditional data formats such as CSV (Comma Separated Values), XML (Extensible Markup Language), etc. are gradually being replaced by JSON (JavaScript Object Notation). JSON is a lightweight and easy-to-understand data format widely used in web and mobile application development.

When processing JSON data, PHP, as a powerful server-side programming language, is often used to interact with the database. As a widely used relational database management system, MySQL cooperates even more closely with PHP.

This article will introduce how PHP and MySQL handle nested JSON data and provide corresponding code examples.

1. Create database and table

First, we need to create a MySQL database and table to store data. Suppose we want to create a database called "users" that contains a table called "users_info". The table contains three fields: "id", "name" and "info", where the "info" field will store nested JSON data.

The following is the SQL statement to create the table:

CREATE TABLE users_info (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  info JSON
);

2. Inserting nested JSON data

Inserting nested JSON data using PHP and MySQL is very Simple. We can use PHP's json_encode() function to convert the associative array into JSON format and insert the data into the table through MySQL's INSERT statement.

The following is a sample code:

<?php
// 用户信息数组
$userInfo = [
    'name' => 'John Doe',
    'info' => [
        'age' => 25,
        'email' => 'john.doe@example.com',
        'address' => [
            'street' => '123 Main St',
            'city' => 'New York',
            'state' => 'NY'
        ]
    ]
];

// 将数组转换为JSON格式
$jsonData = json_encode($userInfo);

// 连接数据库
$mysqli = new mysqli('localhost', 'username', 'password', 'users');

// 插入数据到表格中
$query = "INSERT INTO users_info (name, info) VALUES ('John Doe', '$jsonData')";
$mysqli->query($query);

// 关闭数据库连接
$mysqli->close();
?>

3. Query nested JSON data

When we need to query nested JSON data, we can use MySQL's JSON_EXTRACT() function. The JSON_EXTRACT() function is used to extract the value of the specified path from a JSON string.

The following is a sample code:

<?php
// 连接数据库
$mysqli = new mysqli('localhost', 'username', 'password', 'users');

// 查询嵌套的JSON数据
$query = "SELECT id, name, JSON_EXTRACT(info, '$.age') AS age, JSON_EXTRACT(info, '$.email') AS email FROM users_info";
$result = $mysqli->query($query);

// 打印查询结果
while ($row = $result->fetch_assoc()) {
    echo "ID: " . $row['id'] . "<br>";
    echo "Name: " . $row['name'] . "<br>";
    echo "Age: " . $row['age'] . "<br>";
    echo "Email: " . $row['email'] . "<br>";
    echo "<br>";
}

// 关闭数据库连接
$mysqli->close();
?>

In the above code, by using the JSON_EXTRACT() function, we can extract the values ​​of different fields from the nested JSON data, so as to facilitate Query and display.

Summary:

This article introduces how to use PHP and MySQL to process nested JSON data. With sample code, we show how to insert and query nested JSON data. As a lightweight and easy-to-understand data format, JSON, combined with PHP and MySQL, can make it easier for us to process and store complex data structures.

Come and try using PHP and MySQL to process nested JSON data! Happy coding!

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