Post
Ask Question

Visualizing MySQL Date-Time Data with CanvasJS in PHP

Creating dynamic and interactive charts is essential for many web applications. CanvasJS charting library makes it easy to create visually appealing charts with data from a database. In this guide, let’s see how to build a CanvasJS chart using data fetched from a MySQL database with PHP.

Chart

Prerequisites

  • A web server with PHP and MySQL installed.
  • Basic knowledge of PHP and JavaScript.
  • CanvasJS library (you can download it from the CanvasJS website).

Step 1. Setting Up the Database

First, ensure you have a MySQL database set up with a table containing the data you want to visualize. For this example, let’s assume we have a table named data points with columns x (timestamp) and y (value).

CREATE TABLE datapoints (
    id INT AUTO_INCREMENT PRIMARY KEY,
    x TIMESTAMP NOT NULL,
    y FLOAT NOT NULL
);

Step 2. Fetching Data from the Database

Next, let’s write a PHP script to fetch data from the datapoints table and convert it into a format that CanvasJS can use.

<?php
$dataPoints = array();

try {
    $link = new PDO('mysql:host=your-hostname;dbname=your-db-name;charset=utf8mb4', 'your-db-username', 'your-db-password', array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_PERSISTENT => false
    ));

    $handle = $link->prepare('SELECT x, y FROM datapoints');
    $handle->execute();
    $result = $handle->fetchAll(PDO::FETCH_OBJ);

    foreach ($result as $row) {
        // Convert PHP timestamp to JavaScript timestamp
        $timestamp = strtotime($row->x) * 1000;
        array_push($dataPoints, array("x" => $timestamp, "y" => $row->y));
    }

    $link = null;
} catch (PDOException $ex) {
    print($ex->getMessage());
}
?>

In the PHP script above, the PHP timestamp is converted to a JavaScript timestamp by multiplying the Unix timestamp (seconds since January 1, 1970) by 1000 to convert it to milliseconds.

$timestamp = strtotime($row->x) * 1000;

This conversion is necessary because JavaScript’s Date object expects the timestamp in milliseconds whereas PHP timestamp is in seconds.

Step 3. Creating the HTML and JavaScript for the Chart

Now, let’s create an HTML file that includes the CanvasJS library and uses the data fetched by the PHP script to render the chart.

<!DOCTYPE HTML>
<html>
<head>
    <script>
        window.onload = function () {
            var chart = new CanvasJS.Chart("chartContainer", {
                animationEnabled: true,
                exportEnabled: true,
                theme: "light2",
                title: {
                    text: "PHP Column Chart from Database"
                },
                data: [{
                    type: "column",
                    dataPoints: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>
                }]
            });
            chart.render();
        }
    </script>
</head>
<body>
    <div id="chartContainer" style="height: 370px; width: 100%;"></div>
    <script src="https://cdn.canvasjs.com/canvasjs.min.js"></script>
</body>
</html>

By following these steps, you can easily create a dynamic CanvasJS chart using data from a MySQL database in PHP. This approach can be extended to various types of charts and datasets, making it a versatile solution for data visualization in web applications. Happy coding!


Similar Articles