How to Store JSON Data Generated by PHP in MySQL

Storing JSON Data from PHP in MySQL

In modern web development, it's common to work with structured data in JSON format. PHP can easily generate JSON, and since MySQL 5.7, the database supports native JSON data types, making it easier and more efficient to store and manage such data.

Creating a MySQL Table for JSON Data

First, create a table with a JSON column to store the data. Here's a simple example:

CREATE TABLE `json_data_table` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `json_data` JSON NOT NULL,
  PRIMARY KEY (`id`)
);

This setup uses MySQL’s native JSON type, which validates the format and allows advanced operations later on.

Connecting to MySQL Using PHP

Use PHP's mysqli extension to connect to the MySQL database:

<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "test";

$conn = new mysqli($host, $user, $password, $database);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

Inserting JSON Data into MySQL with PHP

After preparing your data in an associative array, convert it to a JSON string and insert it into the database:

<?php
$data = array(
    'name' => 'John',
    'age' => 25
);

$jsonData = json_encode($data);
$sql = "INSERT INTO json_data_table (json_data) VALUES ('$jsonData')";

if ($conn->query($sql) === TRUE) {
    echo "JSON data inserted successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Note: To prevent SQL injection, use prepared statements instead of directly inserting JSON into SQL queries.

Retrieving and Decoding JSON Data from MySQL

To fetch and parse the stored JSON data back into PHP, use the following code:

<?php
$sql = "SELECT json_data FROM json_data_table WHERE id = 1";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $jsonData = $row['json_data'];
        $data = json_decode($jsonData, true);

        echo "Name: " . $data['name'] . "<br>";
        echo "Age: " . $data['age'] . "<br>";
    }
} else {
    echo "No data found";
}

$conn->close();
?>

Using json_decode with the true parameter converts the JSON string into an associative array, making it easier to work with in PHP.

Conclusion

This guide demonstrated how to work with JSON data in PHP and MySQL, including how to insert and retrieve JSON efficiently using MySQL's native JSON support. Implementing this approach allows for flexible and scalable data handling in web applications.