Chart JS is a very comprehensive framework for creating and customizing many charts and graphs. The ease of use and ability to configure the charts makes Chart JS a good choice for HTML5 charts.
Another method (24th March 2021).
Getting Chart JS to work with PHP and MySQL isn’t hard nor extensive. Here is how it is done:
Making a bar chart
Assume I have a database table that has an int column (value) and a datetime column (date_time). I am going to create a bar chart to show these values along with the date in a bar chart.
create_data.php
<?php
header('Content-Type: application/json');
require_once('db.php');
$db = db_connect();
$select = $db->prepare("SELECT `value`, `date_time` FROM `data` ORDER BY `date_time`;");
$select->execute();
$data = array();
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
$data[] = $row;
}
print json_encode($data);This will jsut print out the data as an array. This is what Chart JS uses to plot the points.
view_chart.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title>Chart</title>
<link rel="stylesheet" href="https://write.corbpie.com/wp-content/litespeed/localres/aHR0cHM6Ly9jZG5qcy5jbG91ZGZsYXJlLmNvbS8=ajax/libs/twitter-bootstrap/4.3.1/css/bootstrap.min.css"
<script src="https://write.corbpie.com/wp-content/litespeed/localres/aHR0cHM6Ly9jZG5qcy5jbG91ZGZsYXJlLmNvbS8=ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="value_chart.js"></script>
</head>
<body>
<div class='container'>
<div class="row text-center">
<div class='col-12'>
<div class="card">
<canvas id="value-data" style="height:480px; width:520px"></canvas>
</div>
</div>
</div>
</div>
<script type="text/javascript" src="https://write.corbpie.com/wp-content/litespeed/localres/aHR0cHM6Ly9jZG5qcy5jbG91ZGZsYXJlLmNvbS8=ajax/libs/Chart.js/2.6.0/Chart.min.js"></script>
</body>
</html>value_chart.js
$(document).ready(function () {
$.ajax({
url: "create_data.php",
type: "GET",
success: function (data) {
var date_time = [];
var value = [];
for (var i in data) {
date_time.push("" + data[i].date_time);
value.push(data[i].value);
}
var chartdata = {
labels: date_time,
datasets: [
{
label: "value",
fill: false,
lineTension: 0.3,
backgroundColor: chartColors.green,
borderColor: chartColors.green,
pointHoverBackgroundColor: chartColors.green,
pointHoverBorderColor: chartColors.green,
hoverBackgroundColor: chartColors.gold,
data: value,
yAxisID: "y-axis-1"
}
]
};
var ctx = $("#value-data");
var LineGraph = new Chart(ctx, {
type: 'bar',
data: chartdata,
options: {
title: {
display: true,
text: '',
maintainAspectRatio: false,
fontColor: chartColors.green
},
responsive: true,
scales: {
xAxes: [{
display: true,
scaleLabel: {
display: true,
labelString: ''
}
}],
yAxes: [{
type: "linear",
display: true,
position: "left",
id: "y-axis-1",
scaleLabel: {
display: false,
labelString: 'value'
}
}]
}
}
});
},
error: function (data) {
}
});
});
window.chartColors = {
red: 'rgb(255, 99, 132)',
orange: 'rgb(255, 159, 64)',
yellow: 'rgb(255, 205, 86)',
green: 'rgb(75, 192, 192)',
blue: 'rgb(54, 162, 235)',
purple: 'rgb(153, 102, 255)',
gold: 'rgb(248,193,28)',
grey: 'rgb(201, 203, 207)'
};Looks confusing right? Most of the above is just options and configuration as per the docs. You can see that i am after the value and date_time columns, these obviously are also the points on the axis.
The colors are defined at the end, this was to save repeating a hex code and to create readability. Here is an example
Making resource friendly
One issue with this current code is its lack of sustainability in resource usage. Every time someone goes to your chart page the database gets queried. You may not be getting 100 views per minute to your graph page but that doesn’t mean you can write code that has poor practice.
This becomes even more dependent when you start filtering, fetching more data per row (multiple bars) and formating the returned date time.
Avoiding this is simple, instead of create_data.php returning the array you get it to create a JSON file with the data in it.
This is done like:
<?php
require_once('db.php');
$db = db_connect();
$select = $db->prepare("SELECT `value`, `date_time` FROM `data` ORDER BY `date_time`;");
$select->execute();
$data = array();
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
$data[] = $row;
}
$fp = fopen('value_data.json', 'w');
fwrite($fp, json_encode($data));
fclose($fp);Just point a cron job to call create_data.php thus updating the JSON file (value_chart.json).
In value_chart.js change
url: "create_data.php",
to
url: "value_data.json",
Now when your chart page is viewed instead of the database being queried every single time, the JSON file gets loaded. Just remember to cron create_data.php every 2 or 5 minutes etc.