How to Implement Google Column Chart using Codeigniter

September 5, 2019 XpertPhp Comments 0 Comment

In this article, we will explain to you how to Implement Google Column Chart using Codeigniter. today we will fetch data into MySQL, and plot a Column graph.

There are many types of charts provide by Google, like a bar chart, area chart, line chart, pie chart, etc. but here we use a Column chart.

In this article, we will plot a Column graph using Google API. so you can see below the following steps.

Overview

Step 1: Create a Database in table
Step 2: Connect to Database
Step 3: Create Controller
Step 4: Create View File

Step 1: Create a Database in table
In this step, we have to create a table in the database, so we will create a database using the below code.

CREATE TABLE `login_history` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `login_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;

CREATE TABLE `login_history` (

`id` int(11) NOT NULL,

`user_id` int(11) NOT NULL,

`login_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;

Step 2: Connect to Database
Go to the config folder and open database.php file some changes in this file like hostname, database username, database password, and database name.

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',
	'password' => '',
	'database' => 'enter here database name',
	'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'db_debug' => (ENVIRONMENT !== 'production'),
	'cache_on' => FALSE,
	'cachedir' => '',
	'char_set' => 'utf8',
	'dbcollat' => 'utf8_general_ci',
	'swap_pre' => '',
	'encrypt' => FALSE,
	'compress' => FALSE,
	'stricton' => FALSE,
	'failover' => array(),
	'save_queries' => TRUE
);

$db['default'] = array(

'dsn' => '',

'hostname' => 'localhost',

'username' => 'root',

'password' => '',

'database' => 'enter here database name',

'dbdriver' => 'mysqli',

'dbprefix' => '',

'pconnect' => FALSE,

'db_debug' => (ENVIRONMENT !== 'production'),

'cache_on' => FALSE,

'cachedir' => '',

'char_set' => 'utf8',

'dbcollat' => 'utf8_general_ci',

'swap_pre' => '',

'encrypt' => FALSE,

'compress' => FALSE,

'stricton' => FALSE,

'failover' => array(),

'save_queries' => TRUE

);

Step 3: Create Controller
In this step, we will create a Googlechart.php file in the “application/controller” directory and paste the below code in this controller. here we will fetch the last 10 months of data from the database and month-wise count login users. that data will be pass in a chart and creating a Column graph.
application/controller/Googlechart.php

<?php
 
defined('BASEPATH') OR exit('No direct script access allowed');
 
class Googlechart extends CI_Controller {
 
    public function __construct() {
        parent::__construct();
        // load model
        $this->load->database();
        $this->load->helper(array('url','html','form'));
    }       
    public function column_chart() {
       $query =  $this->db->query('SELECT DATE_FORMAT(login_date, "%Y-%m-%d") AS `date`, COUNT(`login_date`) as count FROM login_history WHERE `login_date` >= NOW() - INTERVAL 10 MONTH GROUP BY MONTH(`login_date`) ORDER BY `login_date` ASC'); 
      $records = $query->result_array();
      $data = [];
      $data['chart_data'] = $records;
      $this->load->view('column_chart',$data);
    }

}
?>

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Googlechart extends CI_Controller {

public function __construct() {

parent::__construct();

// load model

$this->load->database();

$this->load->helper(array('url','html','form'));

}

public function column_chart() {

$query = $this->db->query('SELECT DATE_FORMAT(login_date, "%Y-%m-%d") AS `date`, COUNT(`login_date`) as count FROM login_history WHERE `login_date` >= NOW() - INTERVAL 10 MONTH GROUP BY MONTH(`login_date`) ORDER BY `login_date` ASC');

$records = $query->result_array();

$data = [];

$data['chart_data'] = $records;

$this->load->view('column_chart',$data);

}

Step 4: Create View File
So finally, we will create the column_chart.php file in the “application/views/” directory.
application/views/column_chart.php

<!Doctype html>
<html>
<head>
  <title>How to Implement Google Column Chart using Codeigniter - XpertPhp</title>
  <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> 
  <script type="text/javascript">
      google.charts.load('visualization', "1", {
          packages: ['corechart']
      });
  </script>
</head>
<body>
<div id="column_chart" style="width: 900px; height: 500px; margin: 0 auto"></div>
<script language="JavaScript">
  // Draw the Column chart 
  google.charts.setOnLoadCallback(columnChart);   
  // for year wise
  function columnChart() {
        /* Define the chart to be drawn.*/
        var data = google.visualization.arrayToDataTable([
            ['Month', 'Users Count'],
            <?php 
             foreach ($chart_data as $row){
             echo "['".$row['date']."',".$row['count']."],";
             }
             ?>
        ]);
        var options = {
            title: 'Day Wise Count Login Users Of Current Month',
            isStacked: true
        };
        /* Instantiate and draw the chart.*/
        var chart = new google.visualization.ColumnChart(document.getElementById('column_chart'));
        chart.draw(data, options);
    }
</script>
</body>
</html>

<!Doctype html>

<html>

<head>

<title>How to Implement Google Column Chart using Codeigniter - XpertPhp</title>

google.charts.load('visualization', "1", {

packages: ['corechart']

});

</script>

</head>

<body>

// Draw the Column chart

google.charts.setOnLoadCallback(columnChart);

// for year wise

function columnChart() {

/* Define the chart to be drawn.*/

var data = google.visualization.arrayToDataTable([

['Month', 'Users Count'],

<?php

foreach ($chart_data as $row){

echo "['".$row['date']."',".$row['count']."],";

}

]);

var options = {

title: 'Day Wise Count Login Users Of Current Month',

isStacked: true

};

/* Instantiate and draw the chart.*/

var chart = new google.visualization.ColumnChart(document.getElementById('column_chart'));

chart.draw(data, options);

}

</script>

</body>

</html>

We think would you like this article, so you can click on the “Show Demo” button and you can see this demo article.

Show Demo

Please follow and like us:

Related posts: