How to search and filter in JSON data using PHP and MySQL?

How to search and filter in JSON data using PHP and MySQL?

With the rapid development of the Internet and the continuous updating of application scenarios, front-end developers often need to search and filter in large amounts of JSON data. The combination of PHP and MySQL can provide fast and efficient search and filtering functions. This article will introduce how to use PHP and MySQL to search and filter JSON data, and provide corresponding code examples.

1. Create database tables and data

First, create a table in MySQL and insert some JSON data. The code sample looks like this:

CREATE TABLE json_data(
    id INT PRIMARY KEY AUTO_INCREMENT,
    data JSON
);

INSERT INTO json_data (data) VALUES 
    ('{"name":"John","age":25,"city":"New York"}'),
    ('{"name":"Alice","age":30,"city":"London"}'),
    ('{"name":"Bob","age":35,"city":"Paris"}'),
    ('{"name":"Emma","age":28,"city":"Dublin"}');

2. Connect to the database

Use the following PHP code to connect to the database:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
?>

3. Perform the search and Filter operation

Use the following code example to obtain JSON data from the database and perform search and filter operations:

<?php
$sql = "SELECT data FROM json_data";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // 输出数据
    while($row = $result->fetch_assoc()) {
        $json = json_decode($row['data'], true); // 将JSON数据解码为关联数组

        // 搜索条件
        $searchTerm = "John";

        // 如果姓名匹配搜索条件，则打印出结果
        if($json['name'] == $searchTerm) {
            echo "姓名：" . $json['name'] . ", 年龄：" . $json['age'] . ", 城市：" . $json['city'] . "<br>";
        }
    }
} else {
    echo "0 结果";
}
$conn->close();
?>

The $searchTerm variable in the above code defines the search condition as "John". If the name matches the search criteria, the results are printed.

4. Add filter conditions

To add filter conditions, you can use the following code example:

<?php
$sql = "SELECT data FROM json_data";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // 输出数据
    while($row = $result->fetch_assoc()) {
        $json = json_decode($row['data'], true);

        // 过滤条件
        $ageFilter = 30;
        $cityFilter = "London";

        // 如果满足过滤条件，则打印出结果
        if($json['age'] > $ageFilter && $json['city'] == $cityFilter) {
            echo "姓名：" . $json['name'] . ", 年龄：" . $json['age'] . ", 城市：" . $json['city'] . "<br>";
        }
    }
} else {
    echo "0 结果";
}

$conn->close();
?>

$ageFilter and $cityFilter in the above code are respectively Filters for age and city are defined. If the data meets these filter conditions, the results are printed.

Summary:

Through the above steps, we learned how to use PHP and MySQL to search and filter in JSON data. PHP can query JSON data through MySQL connection, and search and filter according to specified conditions. This combination provides efficient search and filtering capabilities that can help front-end developers quickly process large amounts of JSON data.

I hope this article can provide you with basic guidance on how to use PHP and MySQL to search and filter in JSON data, and help readers better understand and apply it through code examples. I wish you all good results when using PHP and MySQL for JSON searching and filtering!

The above is the detailed content of How to search and filter in JSON data using PHP and MySQL?. For more information, please follow other related articles on the PHP Chinese website!