How to process JSON array using PHP and MySQL?

How to process JSON arrays using PHP and MySQL?

Introduction:
In modern web application development, processing JSON data has become a common requirement. PHP and MySQL are widely used technologies, and understanding how to use them to process JSON arrays will greatly improve development efficiency and flexibility. This article will introduce how to use PHP and MySQL to process JSON arrays, and provide corresponding code examples.

1. Create a database table
Before using PHP and MySQL to process JSON arrays, we first need to create a database table containing JSON data. Suppose we have a table called "users" with the following fields:

• id (integer, primary key)
• name (text)
• email (text )
• skills (JSON)

We can use the following SQL statement to create a table:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    email VARCHAR(255),
    skills JSON
);

2. Insert JSON data
Next, we will Insert some JSON data into the table. In PHP, we can use the following code to insert JSON data into the database:

<?php
$conn = new mysqli("localhost", "username", "password", "database");

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$data = array(
    "name" => "John Doe",
    "email" => "johndoe@example.com",
    "skills" => json_encode(array("PHP", "MySQL", "JavaScript"))
);

$sql = "INSERT INTO users (name, email, skills) VALUES (?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param("sss", $data['name'], $data['email'], $data['skills']);
$stmt->execute();

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

3. Query JSON data
To query JSON data from the database, we can use MySQL’s built-in JSON function and PHP mysqli extension. The following is a sample code for querying and processing JSON arrays using PHP and MySQL:

<?php
$conn = new mysqli("localhost", "username", "password", "database");

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, name, email, skills FROM users WHERE skills->'$[0]' = 'PHP'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . "<br>";
        echo "Name: " . $row["name"] . "<br>";
        echo "Email: " . $row["email"] . "<br>";
        echo "Skills: " . implode(", ", json_decode($row["skills"])) . "<br>";
        echo "<br>";
    }
} else {
    echo "No results found.";
}

$conn->close();
?>

Summary:
This article introduces how to use PHP and MySQL to process JSON arrays, and shows how to create Database tables, inserting JSON data, and querying and processing JSON data. Mastering these techniques will help developers process JSON data more efficiently and improve the flexibility and scalability of web applications.

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