English
English
In modern web development, working with JSON data is an essential skill. PHP, as a server-side scripting language, combined with MySQL database, can efficiently manage and manipulate JSON-formatted data. This article explores how to use PHP and MySQL together to handle JSON arrays, with example code to help readers quickly master the techniques.
Before working with JSON data, the database table structure must be designed properly. For example, the "users" table contains basic user information and a JSON type field to store a list of skills:
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
email VARCHAR(255),
skills JSON
);When inserting JSON data, PHP can use the json_encode function to convert an array to a JSON string and then store it into the MySQL JSON field. Example code is as follows:
<?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();
?>MySQL has built-in JSON functions that allow for easy filtering of JSON data. The following example shows how to query users with a specific skill and parse the JSON field with PHP:
<?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 JSON_EXTRACT(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><br>";
}
} else {
echo "No results found.";
}
$conn->close();
?>Mastering how to handle JSON arrays in PHP and MySQL greatly enhances data manipulation flexibility and efficiency. This article covered the complete process of creating a database table with a JSON field, inserting JSON-formatted data, and querying and parsing JSON content using SQL and PHP. Hopefully, this guide will be helpful for your web development projects.
