How to visualize JSON data using PHP and MySQL?-PHP Tutorial-php.cn

How to visualize JSON data using PHP and MySQL?

王林

Release： 2023-07-12 18:10:01

Original

1403 people have browsed it

How to visualize JSON data using PHP and MySQL?

Overview:
In today's data-driven world, data visualization has become a very important part. Through data visualization, we can understand and analyze data more intuitively. As a lightweight data exchange format, JSON is widely used in data transmission and storage. This article will introduce how to use PHP and MySQL to convert JSON data into visual charts to better understand and display the data.

1. Preparation
Before starting, we need to make sure that we have installed PHP and MySQL and have a basic development environment.

2. Create database and table
First, we need to create a database and a table to store JSON data. In the MySQL command line, execute the following statement:

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

Copy after login

Here we create a database named json_visualization, which contains a table named data , used to store JSON data.

3. Insert JSON data
Next, we insert some JSON data into the data table for subsequent use in visualization. Execute the following statement to insert data:

INSERT INTO data (json_data) VALUES
    ('{"name": "John Smith", "age": 30, "city": "New York"}'),
    ('{"name": "Jane Doe", "age": 25, "city": "London"}'),
    ('{"name": "Tommy Lee", "age": 40, "city": "Los Angeles"}');

Copy after login

4. Use PHP to read and process JSON data
Next, we use PHP to read JSON data from the MySQL database and process it. Create a file called index.php and add the following code:

<?php

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'json_visualization';

$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

if (mysqli_connect_errno()) {
    die("Failed to connect to MySQL: " . mysqli_connect_error());
}

$query = "SELECT json_data FROM data";
$result = mysqli_query($conn, $query);

$data = array();
while ($row = mysqli_fetch_assoc($result)) {
    $data[] = json_decode($row['json_data'], true);
}

mysqli_close($conn);

?>

Copy after login

In this code, we first establish a connection to the MySQL database. Then, execute the query statement to obtain the JSON data in the data table. Afterwards, the JSON string is parsed into a PHP array through the json_decode function and stored into the $data array.

5. Use data visualization tools to display data
Now, we have successfully read and saved the JSON data into the $data array. Next, we can use data visualization tools to display this data. Here we will use Chart.js to create charts. Add the following code to the index.php file:

<!DOCTYPE html>
<html>
<head>
    <title>JSON Visualization</title>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
    <canvas id="chart"></canvas>
    <script>
        var ctx = document.getElementById('chart').getContext('2d');
        var chart = new Chart(ctx, {
            type: 'bar',
            data: {
                labels: <?php echo json_encode(array_column($data, 'name')); ?>,
                datasets: [{
                    label: 'Age',
                    data: <?php echo json_encode(array_column($data, 'age')); ?>
                }]
            },
            options: {
                scales: {
                    y: {
                        beginAtZero: true
                    }
                }
            }
        });
    </script>
</body>
</html>

Copy after login

In this code, we first introduced the Chart.js library file, and then created a in the page canvas element, used to display charts.

Next, in the <script> tag, use new Chart to create a histogram. By calling the json_encode function, the data stored in the $data array is converted into JSON format and used as the label and data of the chart.

Finally, we configure the options of the chart through the options property, for example, the Y-axis starts from 0.

6. Operation and access results
Save the index.php file to the root directory of the PHP server and run the server. Visit http://localhost/index.php in the browser, and you can see a histogram showing JSON data.

7. Summary
This article introduces how to use PHP and MySQL to convert JSON data into visual chart display to better understand and display the data. By creating databases and tables, inserting JSON data, using PHP to read the data, and then using tools such as Chart.js for visualization, we can easily achieve visual display of data. This helps us better understand and analyze data to make more informed decisions. Hope this article is helpful to you.

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