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

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



The following two tabs change content below.
Abhay Anand has experience in the field of Software Development. He can program Android, PHP, JavaScript, Html, Css. He develops web and Android applications. You can reach him at anand.abhay1910@gmail.com