How to process key-value pair data in JSON using PHP and MySQL?

How to use PHP and MySQL to process key-value pair data in JSON?

In modern web development, processing and manipulating JSON data is a very important task. PHP and MySQL are two very powerful tools that can help us process JSON data efficiently. This article will teach you how to use PHP and MySQL to process key-value pair data in JSON.

First, we need to understand what JSON is. JSON (JavaScript Object Notation) is a lightweight data exchange format that stores and transmits data in a way that is easy to read and understand. JSON data consists of key-value pairs, where the key is a string and the value can be a string, number, object, array, etc.

In PHP, we can use built-in functions to process JSON data. First, we need to parse the JSON data into a PHP array. This can be achieved using the json_decode() function:

$json_data = '{"name":"John", "age":30, "city":"New York"}';
$php_array = json_decode($json_data, true);

echo $php_array['name']; // 输出：John
echo $php_array['age']; // 输出：30
echo $php_array['city']; // 输出：New York

The above code parses the JSON data into a PHP array and uses the array key to access the corresponding value.

Next, we will see how to store the parsed JSON data into a MySQL database. We can use PHP's MySQLi extension to connect and operate the MySQL database.

First, you need to make sure you are connected to the database. The following is a simple database connection example:

$servername = "localhost";
$username = "root";
$password = "";
$database = "my_db";

$conn = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) {
    die("连接失败：" . $conn->connect_error);
}

Next, we will store the parsed JSON data into the MySQL database. Suppose we have a table called "users" that contains the fields "name", "age" and "city".

$sql = "INSERT INTO users(name, age, city) VALUES (?, ?, ?)";

$stmt = $conn->prepare($sql);
$stmt->bind_param("sis", $php_array['name'], $php_array['age'], $php_array['city']);

if ($stmt->execute()) {
    echo "数据插入成功";
} else {
    echo "数据插入失败：" . $stmt->error;
}

$stmt->close();
$conn->close();

The above code uses prepared statements to insert the parsed JSON data into the "users" table. By binding parameters, data security can be ensured.

In addition to inserting data, we can also use the SELECT query statement to retrieve JSON data from the MySQL database.

$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $json_data = json_encode($row);

        echo $json_data;
    }
} else {
    echo "没有找到记录";
}

$conn->close();

The above code converts the data retrieved from the "users" table into JSON format and outputs it to the browser.

Through the above sample code, you can learn how to use PHP and MySQL to process key-value pair data in JSON. These technologies are useful in processing and storing user-submitted data, API data, etc. Hope this article helps you!

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