ChartJS | How to draw Line graph using data from MySQL table and PHP - ChartJS - dyclassroom

In this tutorial we are learning to draw Line Graph using data from MySQL table and PHP.

Using ChartJS 2.x Download

Note! You can get the code of this tutorial from my GitHub repository.

Requirement

We will need the following items to complete this tutorial.

Text Editor like SublimeText, TextMate, Coda, NotePad++ or IDE like Eclipse
Web Browser like Chrome or Firefox
PHP development environment on your localhost. For this you can use XAMPP (Windows/Linux/OS X users) or MAMP (Windows/OS X users)
MySQL
ChartJS
jQuery

So lets get started...

MySQL table

For this tutorial we will consider a very simple followers table having four columns userid, facebook, twitter, googleplus and 3 entries.

followers table

CREATE TABLE `followers` (
    `userid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `facebook` INT(11) DEFAULT '0',
    `twitter` INT(11) DEFAULT '0',
    `googleplus` INT(11) DEFAULT '0',
    PRIMARY KEY (`userid`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8;

followers table data

INSERT INTO `followers`
VALUES
(1, 100, 200, 80),
(2, 60,  150, 180),
(3, 50,  90,  120);

Create a New Project

Create a new project folder and name it chartjs. Note! Feel free to name your project as per your choice. Inside the chartjs project folder create a subfolder and name it js. This will hold all the javascript files.

Moving the JS files in the js folder

Copy the Chart.min.js and jquery.min.js files inside the js folder that we created inside the chartjs project folder. And create an linegraph.js file inside the js folder. This will contain all the javascript code that we are going to write for this project.

Create followersdata.php file to fetch data from MySQL table

Create a new file followersdata.php inside the chartjs folder. This file is going to contain php code that will fetch data from the followers table and display it in JSON format.

File: followersdata.php

<?php
//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 userid, facebook, twitter, googleplus FROM followers");

//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 followersdata.php file

To get the output of data.php file open it in your web browser (Chrome for instance)

URL: localhost/chartjs/followersdata.php

[
  {
    "userid" : "1",
    "facebook" : "100",
    "twitter" : "200",
    "googleplus" : "80"
  },
  {
    "userid" : "2",
    "facebook" : "60",
    "twitter" : "150",
    "googleplus" : "180"
  },
  {
    "userid" : "3",
    "facebook" : "50",
    "twitter" : "90",
    "googleplus" : "120"
  }
]

Create linegraph.html file to display line graph

Now create a new file inside chartjs project folder and name it linegraph.html this file is going to display line graph.

File: linegraph.html

<!DOCTYPE html>
<html>
  <head>
    <title>ChartJS - LineGraph</title>
    <style>
      .chart-container {
        width: 640px;
        height: auto;
      }
    </style>
  </head>
  <body>
    <div class="chart-container">
      <canvas id="mycanvas"></canvas>
    </div>
    
    <!-- javascript -->
    <script type="text/javascript" src="js/jquery.min.js"></script>
    <script type="text/javascript" src="js/Chart.min.js"></script>
    <script type="text/javascript" src="js/linegraph.js"></script>
  </body>
</html>

Write some javascript in linegraph.js

We will be making AJAX call from linegraph.js file to followersdata.php file to fetch the JSON data which will be used to draw the line graph.

File: linegraph.js

$(document).ready(function(){
  $.ajax({
    url : "http://localhost/chartjs/followersdata.php",
    type : "GET",
    success : function(data){
      console.log(data);

      var userid = [];
      var facebook_follower = [];
      var twitter_follower = [];
      var googleplus_follower = [];

      for(var i in data) {
        userid.push("UserID " + data[i].userid);
        facebook_follower.push(data[i].facebook);
        twitter_follower.push(data[i].twitter);
        googleplus_follower.push(data[i].googleplus);
      }

      var chartdata = {
        labels: userid,
        datasets: [
          {
            label: "facebook",
            fill: false,
            lineTension: 0.1,
            backgroundColor: "rgba(59, 89, 152, 0.75)",
            borderColor: "rgba(59, 89, 152, 1)",
            pointHoverBackgroundColor: "rgba(59, 89, 152, 1)",
            pointHoverBorderColor: "rgba(59, 89, 152, 1)",
            data: facebook_follower
          },
          {
            label: "twitter",
            fill: false,
            lineTension: 0.1,
            backgroundColor: "rgba(29, 202, 255, 0.75)",
            borderColor: "rgba(29, 202, 255, 1)",
            pointHoverBackgroundColor: "rgba(29, 202, 255, 1)",
            pointHoverBorderColor: "rgba(29, 202, 255, 1)",
            data: twitter_follower
          },
          {
            label: "googleplus",
            fill: false,
            lineTension: 0.1,
            backgroundColor: "rgba(211, 72, 54, 0.75)",
            borderColor: "rgba(211, 72, 54, 1)",
            pointHoverBackgroundColor: "rgba(211, 72, 54, 1)",
            pointHoverBorderColor: "rgba(211, 72, 54, 1)",
            data: googleplus_follower
          }
        ]
      };

      var ctx = $("#mycanvas");

      var LineGraph = new Chart(ctx, {
        type: 'line',
        data: chartdata
      });
    },
    error : function(data) {

    }
  });
});

Output