ChartJS | How to create Line graph using data from MySQL (MariaDB) table and PHP

ChartJS

chartjs logo

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.

chartjs 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);
chartjs line graph team and match

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.

chartjs line graph score data

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.

chartjs line graph data

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);
    }
  });

});