Chartjs-Automatic update a ChartJs with SQL

by

0👍

✅

Well, you’ll have to do a bit of cleanup and new steps:

1. Separate a bit your code into several files:

  • src/config.php : where you put your DB settings, etc.
  • src/data-functions.php : store some DB query functions.
  • index.php : your HTML page.
  • chart-data.php : a service to just retrieve fresh data in JSON.

2. In src/data-functions.php put your logic to retrieve the data in a PHP array:

<?php

/**
 * Get the pie chart data from the DB.
 *
 * @return array
 */
function getPieChartData()
{
    // Do the SQL queries and return an array with the labels,
    // values and colors. We'll be able to convert it to JS later.
    $data = [
        [
            'value' => rand(50, 200),
            'label' => '%.2f%% passed', // sprintf() format to inject pourcentage after.
            'color' => '#1cc88a', // green
        ],
        [
            'value' => rand(0, 30),
            'label' => '%.2f%% failed', // sprintf() format to inject pourcentage after.
            'color' => '#dc3545', // red
        ],
        [
            'value' => rand(0, 3),
            'label' => '%.2f%% not run', // sprintf() format to inject pourcentage after.
            'color' => '#4e73df', // blue
        ],
        [
            'value' => rand(0, 10),
            'label' => '%.2f%% NA', // sprintf() format to inject pourcentage after.
            'color' => '#5a5c69', // gray
        ],
        [
            'value' => rand(0, 2),
            'label' => '%.2f%% Error', // sprintf() format to inject pourcentage after.
            'color' => '#f59f00', // orange
        ],
    ];

    // Calculate the sum of the values so that we can calculate the % to put in the labels.
    $sum = 0;
    foreach ($data as $item) {
        $sum += $item['value'];
    }

    // Walk accross the array to replace the labels by the calculated % in the label.
    foreach ($data as $i => $item) {
        $data[$i]['label'] = sprintf($item['label'], $item['value'] * 100 / $sum);
    }

    return $data;
}

3. In index.php you generate the HTML and JavaScript code

<?php

require 'src/config.php';
require 'src/data-functions.php';

// Get the data from the DB.
$data = getPieChartData();
// Convert the PHP array to a JS array.
$data_json = json_encode($data);

// The URL to get the data in JSON format.
$data_json_url = '/chart-data.php';

// Print your HTML and JavaScript which will be done once
// at the first page load.
?>
<div id="canvas-holder" style="width:50%">
   <div class="chartjs-size-monitor">
      <div class="chartjs-size-monitor-expand">
         <div class=""></div>
      </div>
      <div class="chartjs-size-monitor-shrink">
         <div class=""></div>
      </div>
   </div>
   <canvas id="chart-area" style="display: block; width: 762px; height: 381px;" width="762" height="381" class="chartjs-render-monitor"></canvas>
   <button id="chart-refresh-button">Refresh data</button>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script type="text/javascript">
/**
 * @param array data The data for the pie chart.
 * @param string jsonDataUrl The URL of the pie chart JSON data.
 */
function initPieChart(data, jsonDataUrl) {

    var config = {
        type: 'pie',
        data: {
            datasets: [{
                label: 'Dataset 1',
                data: [], // filled later
                backgroundColor: [] // filled later
            }],
            labels: [] // filled later
        },
        options: {
            responsive: true
        }

    };
    
    /**
     * Helper function to set the data values, labels and colors.
     *
     * @param array data The new data to use.
     * @param object chart_data_to_set The chart.js data object to set.
     */
    function setData(data, chart_data_to_set) {
        // Loop over the data to fill the data object for chart.js.
        data.forEach((item, i) => {
            chart_data_to_set.datasets[0].data[i] = item.value;
            chart_data_to_set.datasets[0].backgroundColor[i] = item.color;
            chart_data_to_set.labels[i] = item.label;
        });
    }

    // Fill the config with all our data, labels and colors.
    setData(data, config.data);

    // Once JS is loaded and DOM ready (images & CSS later).
    document.addEventListener("DOMContentLoaded", () => {
        var ctx = document.getElementById('chart-area').getContext('2d');
        var myPie = new Chart(ctx, config);
        
        var updateButton = document.getElementById('chart-refresh-button');
        // Add a click handler on the refresh button.
        updateButton.addEventListener('click', function(event) {
            // Do an Ajax call to the server to just get the fresh data.
            fetch(jsonDataUrl).then(response => {
                return response.json();
            })
            .then(freshData => {
                // Update the chart data with the fresh data.
                setData(freshData, myPie.data);
                myPie.update();
            })
            .catch(error => {
                console.log('An error occured while fetching data from the server', error);
            });
        });
    });
}

initPieChart(<?php echo $data_json; ?>, '<?php echo $data_json_url; ?>');

</script>

As you can see, the PHP array is converted to a JavaScript array with the help of json_encode() and is printed in place of $data_json inside the HTML/JS output. This way you don’t need all the JS variables which where confusing and messing up the code. This solution is more generic since you could change your data in PHP and all the rest would update without changing the JS code.

4. Create chart-data.php to return the fresh data in JSON

<?php

require 'src/config.php';
require 'src/data-functions.php';

// We won't print any HTML but just return the data in JSON.
$data = getPieChartData();
// Sent the correct MIME type for JSON and return the fresh data.
header('Content-Type: application/json; charset=utf-8');
echo json_encode($data);

Leave a comment