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

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




barchart

Introduction

A bar chart or bar graph is a chart that presents grouped data with rectangular bars with lengths proportional to the values that they represent. The bars can be plotted vertically or horizontally. A vertical bar chart is sometimes called a column bar chart.

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

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

Create Database

We need to get data from database to create dynamic chart, hence we will create Database. After successfully created database, we need to 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=1 ;

--
-- 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 Bar 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('barchart');
	}
    /**
     * @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 Bar chart, we need to create a barchart.php file into application/views directory. In this file, we will use Google API to create Bar chart. We will load google chart loader js file. and load bar package to create Bar Chart.

Create Bar Chart

To create Bar Chart, we will pass package name bar in the google.charts.load() object. It will help to display Bar Chart.

Loading

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

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

var chart = new google.charts.Bar(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':['bar']});
            // 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 Bar 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':['bar']});
            // 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 Bar Chart

We will create a variable to store title, legends, subtitle, width, height. Pass the div id where Bar Chart to be display into new google.charts.Bar() object. Now finally to create Bar 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':['bar']});
      // 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: 'top'}
          }
        }
        
      };
      var chart = new google.charts.Bar(document.getElementById('bar_chart'));
      chart.draw(data, options);
       }
     });
    }
  </script>
</head>
<body>
 
  <div id="bar_chart"></div>
</body>
</html>

barchart

Define Color

We can set custom color of column by using colors: [‘green’, ‘red’] in the variable option.

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_

Horizontal Bar Chart

If we want to create horizontal Bar Chart then we need to define bars: ‘horizontal’ in ‘option’ variable in js script as a given below:

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

horizontal_barchart



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