ChartJS | How to create Line graph using data from MySQL (MariaDB) table and PHP
ChartJS
In this tutorial we will learn to create line graph using data from MySQL (MariaDB) table and PHP.
Using ChartJS 2.x Download
Note! You can get the code of this tutorial from my GitHub repository.
Task
Our task is to create line graph for two teams - TeamA and TeamB. The line graph will show the scores of the teams in 5 matches namely match1, match2, ..., match5.
Prerequisite
A localhost development environment for PHP. If you are using Microsoft Windows then you can use XAMPP. If you are using Apple macOS then you can use MAMP.
Both XAMPP and MAMP will have PHP and MySQL (MariaDB) so you don't have to install them separately.
Here are some of my YouTube tutorial video on XAMPP and PHP IDE setup.
PHP | How to setup XAMPP and Zend Eclipse PDT for PHP project
PHP | How to setup XAMPP and NetBeans for PHP project
XAMPP | How to setup XAMPP Apache Port Number
htdocs
Once you have the development environment ready you need to place the chartjs2 project in the public folder of your localhost. This public folder is generally named htdocs.
Project structure
We will start with the following project structure.
We have a css folder containing stylesheet files. And we have a js folder containing some javascript files.
Backend
First we will setup the backend part i.e., the database table and server side php code.
matchscore table
In this tutorial we will use the matchscore table.
CREATE TABLE `matchscore` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`team` varchar(20) NOT NULL,
`matchname` varchar(20) NOT NULL,
`score` int(11) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And then we will insert the following data in the table.
INSERT INTO matchscore (team, matchname, score) VALUES
('TeamA', 'match1', 10),
('TeamA', 'match2', 50),
('TeamA', 'match3', 25),
('TeamA', 'match4', 70),
('TeamA', 'match5', 40),
('TeamB', 'match1', 20),
('TeamB', 'match2', 35),
('TeamB', 'match3', 40),
('TeamB', 'match4', 60),
('TeamB', 'match5', 50);
data.php
Now inside the chartjs2 project folder create a new folder and name it api and inside this folder create a new file data.php. This file when called will return the score of the teams for the matches.
<?php
/**
* filename: data.php
* description: this will return the score of the teams.
*/
//setting header to json
header('Content-Type: application/json');
//database
define('DB_HOST', '127.0.0.1');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', 'root123');
define('DB_NAME', 'mydb');
//get connection
$mysqli = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);
if(!$mysqli){
die("Connection failed: " . $mysqli->error);
}
//query to get data from the table
$query = sprintf("SELECT team, matchname, score FROM matchscore ORDER BY matchname");
//execute query
$result = $mysqli->query($query);
//loop through the returned data
$data = array();
foreach ($result as $row) {
$data[] = $row;
}
//free memory associated with result
$result->close();
//close connection
$mysqli->close();
//now print the data
print json_encode($data);
Output of data.php
When we call data.php file we will get the following output.
Frontend
Now its time for us to work on the frontend part.
In the project folder chartjs2 create a new html file and inside the js folder create a new js file. I will name the html file as line-db-php.html and js file as line-db-php.js.
At this point the project structure will look like the following.
Note! Other files shown in the above image are from my different ChartJS tutorial.
HTML
Open line-db-php.html file and write the following.
<!DOCTYPE html>
<html>
<head>
<title>ChartJS - Line</title>
<link href="css/default.css" rel="stylesheet">
</head>
<body>
<div class="chart-container">
<canvas id="line-chartcanvas"></canvas>
</div>
<!-- javascript -->
<script src="js/jquery.min.js"></script>
<script src="js/Chart.min.js"></script>
<script src="js/line-db-php.js"></script>
</body>
</html>
JavaScript
Now open the line-db-php.js file and write the following code.
$(document).ready(function() {
/**
* call the data.php file to fetch the result from db table.
*/
$.ajax({
url : "http://localhost/chartjs2/api/data.php",
type : "GET",
success : function(data){
console.log(data);
var score = {
TeamA : [],
TeamB : []
};
var len = data.length;
for (var i = 0; i < len; i++) {
if (data[i].team == "TeamA") {
score.TeamA.push(data[i].score);
}
else if (data[i].team == "TeamB") {
score.TeamB.push(data[i].score);
}
}
//get canvas
var ctx = $("#line-chartcanvas");
var data = {
labels : ["match1", "match2", "match3", "match4", "match5"],
datasets : [
{
label : "TeamA score",
data : score.TeamA,
backgroundColor : "blue",
borderColor : "lightblue",
fill : false,
lineTension : 0,
pointRadius : 5
},
{
label : "TeamB score",
data : score.TeamB,
backgroundColor : "green",
borderColor : "lightgreen",
fill : false,
lineTension : 0,
pointRadius : 5
}
]
};
var options = {
title : {
display : true,
position : "top",
text : "Line Graph",
fontSize : 18,
fontColor : "#111"
},
legend : {
display : true,
position : "bottom"
}
};
var chart = new Chart( ctx, {
type : "line",
data : data,
options : options
} );
},
error : function(data) {
console.log(data);
}
});
});
ADVERTISEMENT