Implement Google Column Chart With PHP Codeigniter

In this codeigniter google column chart tutorial, We would love to share with you how to implement google column chart with PHP codeigniter. How to fetch month wise data to from mysql for showing on chart.

In this Codeigniter tutorial, You will learn how to get month wise data from mysql database and display on the google column chart.

We will fetch monthly record from mysql database. Every month, How many users registered from our database. And the end of article we provide demo link for checkout.

Codeigniter Google Column Chart

Contents

• Download Codeigniter Latest
• Basic Configurations
• Create Database With Table
• Setup Database Credentials
• Make New Controller
• Create Views
• Start Development server
• Conclusion

Download Codeigniter Project

In this step we will download the latest version of Codeigniter, Go to this link Download Codeigniter download the fresh setup of codeigniter and unzip the setup in your local system xampp/htdocs/ . And change the download folder name “demo”

Basic Configurations

Next we will set the some basic configuration on config.php file, so let’s go to application/config/config.php and open this file on text editor.

Set Base URL like this

$config['base_url'] = 'http://localhost/demo/';

Create Database With Table

In this step, we need to create database name demo, so let’s open your phpmyadmin and create the database with the name demo . After successfully create a database, you can use the below sql query for creating a table in your database.

CREATE TABLE users (
    id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
    name varchar(100) NOT NULL COMMENT 'Name',
    email varchar(255) NOT NULL COMMENT 'Email Address',
    contact_no varchar(50) NOT NULL COMMENT 'Contact No',
    created_at varchar(20) NOT NULL COMMENT 'Created date',
    PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=1;

  INSERT INTO users (id, name, email, contact_no, created_at) VALUES
  (1, 'Team', '[email protected]', '9000000001', '2019-01-01'),
  (2, 'Admin', '[email protected]', '9000000002', '2019-02-01'),
  (3, 'User', '[email protected]', '9000000003', '2019-03-01'),
  (4, 'Editor', '[email protected]', '9000000004', '2019-04-01'),
  (5, 'Writer', '[email protected]', '9000000005', '2019-05-01'),
  (6, 'Contact', '[email protected]', '9000000006', '2019-06-01'),
  (7, 'Manager', '[email protected]', '9000000007', '2019-07-01'),
  (8, 'John', '[email protected]', '9000000055', '2019-08-01'),
  (9, 'Merry', '[email protected]', '9000000088', '2019-09-01'),
  (10, 'Keliv', '[email protected]', '9000550088', '2019-10-01'),
  (11, 'Herry', '[email protected]', '9050550088', '2019-11-01'),
  (12, 'Mark', '[email protected]', '9050550998', '2019-12-01');

Setup Database Credentials

In this step, We need to connect our project to database. we need to go application/config/ and open database.php file in text editor. After open the file in text editor, We need to setup database credential in this file like below.

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',
	'password' => '',
	'database' => 'demo',
	'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
);

Create Controller

Now we need to create a controller name Chart.php. In this controller we will create some method/function. We will build some of the methods like :

• Index() – This is used to fetch the column chart record from database.

<?php

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

class Chart extends CI_Controller {

    public function __construct() {
        parent::__construct();
		// load model
        $this->load->database();
        $this->load->helper(array('url','html','form'));
    }    

    public function index() {

      $query =  $this->db->query("SELECT COUNT(id) as count,MONTHNAME(created_at) as month_name FROM users WHERE YEAR(created_at) = '" . date('Y') . "'
      GROUP BY YEAR(created_at),MONTH(created_at)"); 
      
      $record = $query->result();

      $output = [];
      foreach($record as $row) {
            $output[] = array(
             'month_name'   => $row->month_name,
             'count'  => floatval($row->count)
            );
      }

      $data['output'] = ($output);

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

    }
    
}
?>

In this controller function, we fatch the record from database for creating a column chart. After we have get data from database, we will pass the data to view.

Create Views

Now we need to create google_column_chart.php, go to application/views/ folder and create google_column_chart.php file. Here put the below html code for creating a pie chart.

<!Doctype html>
<html>
<head>
    <title>Google Column Chart Codeigniter Tutorial</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="container" style="width: 550px; height: 400px; margin: 0 auto"></div>

</body>
</html>

Implement Javascript code

Finally we will implement javascript code for showing a data on google bar chart. Now we will put the code on script tag after the closing of body tag.

<script language="JavaScript">
    function drawChart() {

        /* Define the chart to be drawn.*/
        var data = google.visualization.arrayToDataTable([
            ['Month', 'Users Count'],
            <?php 
             foreach ($output as $row){
             echo "['".$row['month_name']."',".$row['count']."],";
             }
             ?>
        ]);
        var options = {
            title: 'Month Wise Registered Users Of Current Year <?php echo date("Y")?>',
            isStacked: true
        };
        /* Instantiate and draw the chart.*/
        var chart = new google.visualization.ColumnChart(document.getElementById('container'));
        chart.draw(data, options);
    }
    google.charts.setOnLoadCallback(drawChart);
</script>

Start Development server

For start development server, Go to the browser and hit below the url.

http://localhost/demo/chart

Conclusion

In this codeigniter google column chart tutorial, We have successfully fetch the record from month wise and display on the google column chart.

If you have any questions or thoughts to share, use the comment form below to reach us.