Implement Google Column Chart With PHP Codeigniter

torque-programming 0 Comments Google Charts | Display Month Wise Data With PHP Codeigniter 6 min read

This tutorial will teach you how to use Codeigniter and PHP to create a Google column chart. You will learn how to retrieve data from a MySQL database for each month and display it on the chart. The data will show the number of users who registered in each month. At the end of the tutorial, you can check out a demo to see how it works.

In this 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

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

Download Codeigniter Latest

In this next step, we’re going to download the newest version of Codeigniter. To do this, go to the website Download Codeigniter and get the most up-to-date setup. After you’ve downloaded it, unzip the setup in your local system’s xampp/htdocs/ folder. You can also rename your project folder to whatever you’d like.

Basic Configurations

Now it’s time to do some basic configuration settings on the config.php file. To do this, we’ll navigate to the application/config/config.php file and open it up in a text editor.

set base url like this:-

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

Create Database With Table

For this next step, we’ll need to create a database with the name “demo” To do this, we’ll open up PHPMyAdmin and create the database with that name. Once it’s successfully created, we can use the following SQL query to create a table in the 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', 'info@test.com', '9000000001', '2023-01-01'),
  (2, 'Admin', 'admin@test.com', '9000000002', '2023-02-01'),
  (3, 'User', 'user@test.com', '9000000003', '2023-03-01'),
  (4, 'Editor', 'editor@test.com', '9000000004', '2023-04-01'),
  (5, 'Writer', 'writer@test.com', '9000000005', '2023-05-01'),
  (6, 'Contact', 'contact@test.com', '9000000006', '2023-06-01'),
  (7, 'Manager', 'manager@test.com', '9000000007', '2023-07-01'),
  (8, 'John', 'john@test.com', '9000000055', '2023-08-01'),
  (9, 'Merry', 'merry@test.com', '9000000088', '2023-09-01'),
  (10, 'Keliv', 'kelvin@test.com', '9000550088', '2023-10-01'),
  (11, 'Herry', 'herry@test.com', '9050550088', '2023-11-01'),
  (12, 'Mark', 'mark@test.com', '9050550998', '2023-12-01');

Setup Database Credentials

In the following step, we’ll connect our CRUD app project to the database. To do this, we’ll go to the

application/database.php

file and set up our database credentials. In this file, we’ll add our database name, username, and password.

$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

To continue, we’ll need to create a new controller in the application/controllers/ directory called “Chart.php.” In this controller. 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

Next, we need to create a file called “google_column_chart.php”. To do this, go to the “application/views/” folder and create a new file with this name. Then, copy and paste the following HTML code into this file to create the column chart.

<!DOCTYPE html>
<html>
    <head>
        <title>Google Column Chart Codeigniter Tutorial - Torque Programming</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

We will hit the below url in browser and run our created project.