Multi Series Line Chart in PHP with MySql using Google API Tutorial

12

This tutorial explains how to implement Multi-series Line Chart using Google API in PHP using Codeigniter.




linechart_php

Introduction

A Line chart or Line graph is a type of chart, represented by series of data points and connected by straight line.

In this tutorial, we will implement Multi-Series Line chart using Google APi in PHP using Codeigniter Framework. In this tutorial, we will create database to get data to display into the chart. We use codeigniter framework for PHP. We create a Controllers and Model to get data and pass it into view to display in charts. We will use Google Charts loader to load Line chart. This Line Chart displays tooltips when hovering over line.

We will use PHP only to get data from database. These data will be populate in Line chart.

Create Database

We need to get data from database, hence we will create Database. After successfully created database, we create a table. The structure and data of table given below:

--
-- Table structure for table `company_performance`
--

CREATE TABLE IF NOT EXISTS `company_performance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `year` int(10) NOT NULL,
  `sales` int(10) NOT NULL,
  `expense` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `company_performance`
--

INSERT INTO `company_performance` (`id`, `year`, `sales`, `expense`) VALUES
(1, 2016, 1000, 400),
(2, 2015, 880, 600),
(3, 2014, 960, 400),
(4, 2013, 660, 1220),
(5, 2012, 1120, 700),
(6, 2011, 1500, 550);

Create a Model

We need to create a Model to create a function to get data to display in a chart, so we will create a data_model.php file into appliaction/model directory. Now, we are going to create a function to get data from company_performance table and return it in array.

<?php

class Data_model extends CI_Model {

    /**
     * @desc load  db
     */
    function __construct() {
        parent::__Construct();
        $this->db = $this->load->database('default', TRUE, TRUE);
    }

    /**
     * @desc: Get data from company_performance table
     * @return: Array()
     */
    function getdata(){
		$this->db->select('*');
		$query = $this->db->get('company_performance');
		return $query->result_array();

    }
}

Create Controller

We will create a controller to get data from model and load view. We will create a charts.php file in the application/controllers directory. In this controller we will create a function to load view and another function to get the data to call model.

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Charts extends CI_Controller {

	function __Construct() {
        parent::__Construct();

        $this->load->helper(array('form', 'url'));
        $this->load->model('data_model');
      
    }
    /**
     * @desc: This method is used to load view
     */
	public function index()
	{

		$this->load->view('linechart');
	}
    /**
     * @desc: This method is used to get data to call model and print it into Json
     * This method called by Ajax
     */
	function getdata(){
		$data  = $this->data_model->getdata();
		print_r(json_encode($data, true));
	}
}

Create a view

To display line chart, we need to create a linechart.php file into application/views directory. In this file, we will use Google API to display line chart. We will load google chart loader js file. and load line package to create Line chart.

Loading

The google.charts.load package name is "line"
google.charts.load("current", {packages: ["line"]});

The visualization’s class name is google.charts.Line.

var chart = new google.charts.Line(container);

We are going to create a drawChart() method in js script.

Ajax Call to Get Data

To get data(Json data) from database, we need to use ajax to send a request to get data from database. onsuccess of ajax call, we will initialize a object( Google visualization datatable) to create our data table out of JSON data loaded from server. To add legends to call addColumn() method. We need to pass data type and legends into addColumn() method.

drawChart() function called by callback google.charts.setOnLoadCallback().

<html>
    <head>
        <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
        <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.3/jquery.min.js"></script>
        <script type="text/javascript">
            // Load the Visualization API and the line package.
            google.charts.load('current', {'packages':['line']});
            // Set a callback to run when the Google Visualization API is loaded.
            google.charts.setOnLoadCallback(drawChart);
            
            function drawChart() {
              $.ajax({
                type: 'POST',
                url: 'http://localhost/charts/charts/getdata',
                success: function (data1) {
                  var data = new google.visualization.DataTable();
                  // Add legends with data type
                  data.addColumn('string', 'Year');
                  data.addColumn('number', 'Sales');
                  data.addColumn('number', 'Expense');
                }
              });
            }
       </script>
    </head>
</html>

Add Data Values

Data got from PHP script as in json hence parse it into Json in js. To add data values into Line Chart, we need to pass data values in addRow() method.

<html>
    <head>
        <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
        <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.3/jquery.min.js"></script>
        <script type="text/javascript">
            // Load the Visualization API and the line package.
            google.charts.load('current', {'packages':['line']});
            // Set a callback to run when the Google Visualization API is loaded.
            google.charts.setOnLoadCallback(drawChart);
            
            function drawChart() {
              $.ajax({
                type: 'POST',
                url: 'http://localhost/charts/charts/getdata',
                success: function (data1) {
                  var data = new google.visualization.DataTable();
                  // Add legends with data type
                 data.addColumn('string', 'Year');
                 data.addColumn('number', 'Sales');
                 data.addColumn('number', 'Expense');
                 //Parse data into Json
                 var jsonData = $.parseJSON(data1);
                 for (var i = 0; i < jsonData.length; i++) {
                   data.addRow([jsonData[i].year, parseInt(jsonData[i].sales), parseInt(jsonData[i].expense)]);
                 }
               }
            });
          }
       </script>
    </head>
</html>

Finally create Line Chart

We will create a variable to store title, legends, subtitle, width, height. Pass the div id where Line Chart to be display into new google.charts.Line() object. Now finally to create Line chart, call draw() method with data table object and option variable.

<html>
<head>
  <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
  <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.3/jquery.min.js"></script>
    <script type="text/javascript">
      // Load the Visualization API and the line package.
      google.charts.load('current', {'packages':['line']});
      // Set a callback to run when the Google Visualization API is loaded.
      google.charts.setOnLoadCallback(drawChart);
 
    function drawChart() {
 
        $.ajax({
        type: 'POST',
        url: 'http://localhost/charts/charts/getdata',
         
        success: function (data1) {
        // Create our data table out of JSON data loaded from server.
        var data = new google.visualization.DataTable();
 
      data.addColumn('string', 'Year');
      data.addColumn('number', 'Sales');
      data.addColumn('number', 'Expense');
       
      var jsonData = $.parseJSON(data1);
     
      for (var i = 0; i < jsonData.length; i++) {
            data.addRow([jsonData[i].year, parseInt(jsonData[i].sales), parseInt(jsonData[i].expense)]);
      }
      var options = {
        chart: {
          title: 'Company Performance',
          subtitle: 'Show Sales and Expense of Company'
        },
        width: 900,
        height: 500,
        axes: {
          x: {
            0: {side: 'bottom'} 
          }
        }
        
      };
      var chart = new google.charts.Line(document.getElementById('line_chart'));
      chart.draw(data, options);
       }
     });
    }
  </script>
</head>
<body>
 
  <div id="line_chart"></div>
</body>
</html>

linechart_php

Define Color

We can set color of line in the Line chart by using colors: [‘green’, ‘red’] in variable option

var options = {
var options = {
        chart: {
          title: 'Company Performance',
          subtitle: 'Show Sales and Expense of Company'
        },
        width: 900,
        height: 500,
        axes: {
          x: {
            0: {side: 'bottom'}
          }
        },
        colors: ['green', 'red']
         
};

linechart_color



  • black bunny

    can u email the code example or share it on github? Thank you for this tutorial

  • Mallikarjun B

    Thanks Abhay Anand, this is very informative. Do you have any refernce to create this multi line chart by fetching data directly from mysql db, i don’t want use JSON. Something by using mysqli_fetch_array() ?.

    Also, kindly share source code of this blog. Thanks a lot.

    • Hi Mallikarjun,
      What is the issues to use json to create multi-series line chart. If you do not want to call data from json then you need to load data directly, where I have populate data from json.

      • Mallikarjun B

        Hi Abhay – i have multiple charts created by directly calling from mysql with mysqli_fetch_array(), I am trying to get multiline chart also with the same.

        Is it possible to share source code of this blog ?.

        Thanks alot, appreaciate your help.

        -Mallikarjun

        • Hi Mallikarjun ,
          As you send me your table, Use below code

          // Load the Visualization API and the line package.
          google.charts.load(‘current’, {‘packages’:[‘line’]});
          // Set a callback to run when the Google Visualization API is loaded.
          google.charts.setOnLoadCallback(drawChart);

          function drawChart() {
          // Create our data table out of JSON data loaded from server.
          var data = new google.visualization.DataTable();
          data.addColumn(‘number’, ‘sprint’);
          data.addColumn(‘number’, ’90th Percentile’);
          data.addColumn(‘number’, ‘Average Response Time’);

          data.addRow([parseInt(), parseInt(), parseInt()]);

          var options = {
          chart: {
          title: ‘Company Performance’,
          subtitle: ‘Show Sales and Expense of Company’
          },
          width: 900,
          height: 500,
          axes: {
          x: {
          0: {side: ‘bottom’}
          }
          }

          };
          var chart = new google.charts.Line(document.getElementById(‘line_chart’));
          chart.draw(data, options);
          }

          • Mallikarjun B

            Thanks a lot Abhay Anand for taking your time and helping. Appreciate your help.

  • sighing in

    i want to use my values from database in multi-line chart how can i put them

    • In this tutorial, i am using ajax to get data from database and also i explained in this tutorial how to put values in the chart.
      Please read full tutorial

  • Mallikarjun B

    Hello Anand,

    I’m trying to put two gauges side by side in same div with below code, but it is not loading.

    I referred this : https://developers.google.com/chart/interactive/docs/gallery/gauge.

    Is there anything wrong with my code, not sure why it is not loading. If I comment out for one gauge, other one will load but not both together. In source code I see query returning data and passing to arrayDataTable.

    Thanks in advance !!
    ——————————————————————————————————————————————

    function drawChart2() {

    var data = google.visualization.arrayToDataTable([
    [‘ percentage’, ‘sprint’],
    [‘percentage’, ‘date’],

    ]);

    var options = {
    redFrom: 0, redTo: 70,
    yellowFrom:70, yellowTo: 80,
    greenFrom:80, greenTo: 100,
    minorTicks: 0
    };
    var chart = new google.visualization.Gauge(document.getElementById(‘chart_div’));
    chart.draw(data, options);
    }

  • Nurul Handayani

    Hi Abhay, help me i have trouble.. Why i get error “Uncaught Invalid JSON”?

    • Hi Nurul, your response is not valid json thats why you got error