Coding

Creating Charts with PHP and MySQL Data without JavaScript Knowledge4 min read

November 5, 2019

I’ve recently taken on a school project to create a system that takes in application from students for some requirement. I can’t really give out a lot of details as it is confidential. Assume it’s a system that stores, retrieves and updates student application forms. The client wants us to include a dashboard that displays the total application forms and its status (approved, rejected, pending).

What I mean by “dashboard” is visualisation of data such as, the use of graphs and charts. With no JavaScript experience, I found it daunting to come up with a way to visualise data stored on my database (mysql) and I spent a few hours digging through Google “how to generate charts with php.” I couldn’t, however, find anything that doesn’t sacrifice aesthetic (and I’m a sucker for everything aesthetic).

So, the obvious choice was ChartJS – an open source JavaScript framework for creating aesthetically pleasing charts. The challenge, which every programmer experienced in their noob days, was making do with an unfamiliar programming language by copying, pasting and hoping it all works out. Of course, it rarely does on the first try. Which is why I’m writing this now – to help you be part of that rare exception.

Say, you have a MySQL database that stores student application forms. Each form has a status of pending, approved or rejected. You want to count the total number of forms with each status (how many forms are approved versus, pending and rejected) and display them in a bar graph. You will need to create the following files:

  • database.php
  • chart.html
  • bar.js

database.php

Next, we code the necessary function to connect to the MySQL database, query the table and output the data extracted from MySQL database into JSON to be used by ChartJS.

<?php

//declare variables for db connection
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "testdb";

//db connection
$conn = new mysqli($servername, $username, $password, $dbname);

//error handling
if ($conn->connect_error) {
 die("Connection failed: " . $conn->connect_error);
} 

//sql statement to run
$sql = "SELECT formStatus, COUNT(formStatus) AS total FROM studentForm GROUP BY formStatus;";

//run sql query and store into variable
$result = mysqli_query($conn,$sql);
$data = array();

foreach ($result as $row) {
 $data[] = $row;
}

//free memory and close db connection
$result->close();
$conn->close();

// IMPORTANT, output to json
echo json_encode($data);

?>

The database.php file should generate a JSON output before it can be used with ChartJS. Check the output of your php file by opening it on a browser, i.e. http://localhost/database.php getting an output that resembles below.

[{"formStatus":"Pending","total":"3"},
{"formStatus":"Approved","total":"2"},
{"formStatus":"Rejected","total":"1"}]

chart.html

This is where we display the chart. Basically, the placeholder file. Notice in the code below that script tag for calling bar.js is at the bottom (as opposed to the Jquery and Chart.min.js files). We should only load bar.js after we declare the canvas.

<!DOCTYPE html>
<html lang="en">
<head><title>Chart JS Sample</title>
<!-- Must have JQUERY -->
<script src="js/jquery.min.js"></script>
<!-- link to ChartJS cdn OR download latest chartjs.min.js from GitHub -->
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.8.0/Chart.min.js"></script>
</head>

<body>
<!-- this DIV will display the chart canvas -->
<div class="chart-container">
  <canvas id="bar-chartcanvas"></canvas>
</div>

<!-- javascript to run ChartJS with SQL data (JS to generate chart must come AFTER canvas HTML) -->
<script src="bar.js"></script>
</body>
</html>

bar.js

This is where we code the function to generate the bar chart with ChartJS. I have added inline comments to help you see the only changes you need to make for a straightforward bar chart.

$(document).ready(function () {
 showTotalGraph();
});

function showTotalGraph(){{
  // This is the database.php file we created earlier, its JSON output will be processed in this function
    $.post("database.php",
    function (data){
        console.log(data);
        // Declare the variables for your graph (for X and Y Axis) 
        var formStatusVar = []; // X Axis Label
        var total = []; // Value and Y Axis basis

        for (var i in data) {
            // formStatus is taken from JSON output (see above)
            formStatusVar.push(data[i].formStatus);
            total.push(data[i].total);
        }

        var options = {
            legend: {
                display: false
            },
            scales: {
                xAxes: [{
                    display: true
                }],
             yAxes: [{
                    ticks: {
                        beginAtZero: true
                    }
                }]
            }
        };

        var chartdata = {
            labels: formStatusVar,
            datasets: [
                {
                    label: 'Total',
                    backgroundColor: '#7B7979',
                    borderColor: '#46d5f1',
                    hoverBackgroundColor: '#CCCCCC',
                    hoverBorderColor: '#666666',
                    data: total
                }
            ]
        };

        //This is the div ID (within the HTML content) where you want to display the chart
        var graphTarget = $("#totalGraphCanvas"); 
        var barGraph = new Chart(graphTarget, {
            type: 'bar',
            data: chartdata,
            options: options
        });
    });
}}

And, that’s it. See below for final output.

You Might Also Like

Share your thoughts with us!