Chart.js - Getting data from database using mysql and php

Chart.js - Getting data from database using mysql and php

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Chart.js - Getting data from database using mysql and php

Converting a static Chart.js implementation to one that pulls live data from a MySQL database is a fundamental shift in how you handle application logic. The transition involves moving away from JavaScript-side randomization or hardcoded arrays to fetching structured data via PHP and passing it directly to the chart configuration. This approach ensures your visualization reflects real-time business metrics rather than arbitrary values.

To achieve this, you need to bridge the gap between your database queries and the Chart.js data property. The source material provided demonstrates a classic pattern: executing multiple individual SQL queries to count records per month. While functional for small datasets, this approach relies heavily on the deprecated mysql_* functions. These extensions have been removed in PHP 7.0 and later due to significant security vulnerabilities and lack of support for prepared statements. You should migrate immediately to mysqli or PDO with prepared statements to ensure your application is secure against SQL injection attacks.

Fetching Data Efficiently

The most efficient way to feed data into a chart is to retrieve it in a single query rather than issuing one query per label. If you are counting customers by month, a simple GROUP BY clause can aggregate the results in one go.

Consider replacing the multiple sequential queries with this single aggregation:

$sql = "SELECT month, COUNT(*) as count FROM customer GROUP BY month ORDER BY month";
$result = mysqli_query($connection, $sql) or die(mysqli_error($connection));

$dataPoints = array();
$months = array();

while ($row = mysqli_fetch_assoc($result)) {
    $dataPoints[] = (int)$row['count'];
    $months[] = $row['month'];
}

This loop populates two arrays: $months for the X-axis labels and $dataPoints for the Y-axis values. This structure aligns perfectly with how Chart.js expects data in a dataset.

Passing Data to Chart.js

Once you have populated your PHP arrays, you can pass them directly to the JavaScript initialization code. The modern approach uses ES6 modules or requires blocks to define the chart instance. Here is how you would integrate the fetched data:

var ctx = document.getElementById("canvas").getContext("2d");

// Ensure dataPoints and months are passed from PHP scope
var myLine = new Chart(ctx).Line({
    labels: <?php echo json_encode($months); ?>,
    datasets: [
        {
            label: "Customer Count",
            fillColor : "rgba(220,220,220,0.2)",
            strokeColor : "rgba(220,220,220,1)",
            pointColor : "rgba(220,220,220,1)",
            pointStrokeColor : "#fff",
            pointHighlightFill : "#fff",
            pointHighlightStroke : "rgba(220,220,220,1)",
            data: <?php echo json_encode($dataPoints); ?>
        }
    ],
    responsive: true
});

The <?php echo json_encode(...) ?> calls ensure that the PHP arrays are safely serialized into JSON strings, preventing script errors and ensuring the JavaScript receives valid arrays.

Handling Multiple Datasets

If you require multiple datasets (e.g., counts for different customer types), you can extend the logic to fetch specific columns or run subqueries. However, looping inside jQuery is generally unnecessary if you handle the data structure in PHP first. Instead, construct a multidimensional array where each item represents a dataset:

$datasets = array(
    array("label" => "Type A", "data" => $typeAPoints),
    array("label" => "Type B", "data" => $typeBPoints)
);
// Then pass the entire $datasets array to the chart config

Modern Alternatives

While the example above uses Chart.js 1.x syntax, it is worth noting that newer versions of Chart.js (2.8+) use a different configuration structure (an object with options and data properties) and are more lightweight by default. Additionally, if you are building a larger application using PHP frameworks like Laravel, consider utilizing packages like barryvdh/laravel-debugbar for inspection or leveraging the official Laravel documentation to handle database interactions more robustly. For specific Chart.js version migration details, refer to the official documentation at https://laravelcompany.com to find resources on modernizing your visualization stack.

By aggregating data in PHP and passing it as JSON, you keep your JavaScript clean and decoupled from database logic, allowing your charts to scale as your dataset grows.

Our network