Bar or Column Chart using Google Chart in Codeigniter

Now a days display of raw data on Graph or chart is very common this is because Chart or Graph is a graphical representation of data. So, If we are going to see data line by line then it is very difficult to understand what this data tell us. But suppose we have visualize that data on Graph or chart then we can understand what is the output of this. In short Chart or Graph is the small graphical representation of large data. So, it is very important in web development also for display data on Chart or graph. So, Now in this post we are going to discuss topic like How can make dynamic Bar chart or Column chart in Codeigniter framework by using Google Chart Library with Ajax.

Here dynamic chart means we will fetch dynamic data from mysql data and display on Bar chart or Column chart on every Ajax request. So, We will create dynamic column chart from Mysql Database data by using Google chart library in Codeigniter framework with Ajax. We all know display data in Chart format is a basic need of any web application in which admin panel is required. In Admin panel admin has always required chart in his admin panel for get quick data comparison like profit, sales, new buyer or subscriber etc. by using Chart. By using chart admin can get quick comparison of data with filter like month year etc with chart or graph.

For learn this How to Create Dynamic Bar chart or Column in Codeigniter framework by using Google Chart Library with Ajax, here we will create Bar or Column chart for display profit data of year in month-wise format. So, Admin can get the quick profit data of each year in month-wise in single selection of year. Then he can can particular year profit data will be load in chart month wise column. When admin has select year from drop down box, then Ajax request has been trigger which will fetch profit data from mysql database in JSON format and send back to Ajax request, which will called Google Chart Library Function, and in that function it will convert data into Bar or Column chart and display on web page without refresh of web page. So Admin can view any year profit data on web page in Chart format without refresh of web page. Because here we have use Ajax request for fetch data. Below you can find step by step process for creating Bar or Column Chart in Codeigniter framework using Ajax with Google Chart Library.

• Make Database connection in Codeigniter

• Define Base Url in Codeingiter

• Create Controllers in Codeigniter

• Create Models in Codeigniter

• Create Views in Codeigniter

• Check Output in Browser

View Demo

Make Database connection in Codeigniter

In Codeigniter Application first we want to make table in Mysql database and then after make database connection in Codeigniter. So, run following SQL script in your Mysql database and make table in your database.

--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `chart_data`
--

CREATE TABLE `chart_data` (
  `id` int(11) NOT NULL,
  `year` varchar(10) NOT NULL,
  `month` varchar(50) NOT NULL,
  `profit` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `chart_data`
--

INSERT INTO `chart_data` (`id`, `year`, `month`, `profit`) VALUES
(1, '2017', 'January', '50000'),
(2, '2017', 'February', '45000'),
(3, '2017', 'March', '60000'),
(4, '2017', 'April', '52000'),
(5, '2017', 'May', '67000'),
(6, '2017', 'June', '74000'),
(7, '2017', 'July', '71000'),
(8, '2017', 'August', '76000'),
(9, '2017', 'September', '80000'),
(10, '2017', 'October', '86000'),
(11, '2017', 'November', '88000'),
(12, '2017', 'December', '76000'),
(13, '2018', 'January', '92000'),
(14, '2018', 'February', '96000'),
(15, '2018', 'March', '105000'),
(16, '2018', 'April', '112000'),
(17, '2018', 'May', '120000'),
(18, '2018', 'June', '128000'),
(19, '2018', 'July', '116000'),
(20, '2018', 'August', '112000'),
(21, '2018', 'September', '129000'),
(22, '2018', 'October', '139000'),
(23, '2018', 'November', '140000'),
(24, '2018', 'December', '146000'),
(25, '2019', 'January', '151000'),
(26, '2019', 'February', '146000'),
(27, '2019', 'March', '160000'),
(28, '2019', 'April', '164000'),
(29, '2019', 'May', '185000'),
(30, '2019', 'June', '176000');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `chart_data`
--
ALTER TABLE `chart_data`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `chart_data`
--
ALTER TABLE `chart_data`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

After this, We have to go Codeigniter framework working folder and open application/config/database.php and under this file, we have to define follow mysql database configuration for make database connection.

<?php

$active_group = 'default';
$query_builder = TRUE;

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

?>

Define Base Url in Codeingiter

After making Mysql Database connection in Codeigniter Application. Now we want to define base url of this Codeigniter application. For this we have to open application/config/config.php file and under this we have to define base url of this Codeigniter Application.

<?php

......

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

.......

?>

Create Controllers in Codeigniter

Codeigniter framework is based on MVC style framework. So, here we want to make controller for handle http request. In Codeigniter controllers file has been stored under application/controllers folder. In this tutorial we have make Dynamic_chart.php controller file. In this class we have make following method.

index() - This is the root method of this controller class. In this method we have get the unique year list for fill year dropdown list at view file and this method has been load view file in browser.

fetch_data() - This method has received ajax request for fetch particular year profit data from Mysql Table. It will received ajax for particular year data and send year data to model class, and modal class send back profit data to this controller method and it will convert data into json format and send back to Ajax requst. So this simple process of this method for received year data and send that year profit data to ajax request.

application/controllers/Dynamic_chart.php

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

class Dynamic_chart extends CI_Controller {

 public function __construct()
 {
  parent::__construct();
  $this->load->model('dynamic_chart_model');
 }

 function index()
 {
  $data['year_list'] = $this->dynamic_chart_model->fetch_year();
  $this->load->view('dynamic_chart', $data);
 }

 function fetch_data()
 {
  if($this->input->post('year'))
  {
   $chart_data = $this->dynamic_chart_model->fetch_chart_data($this->input->post('year'));
   
   foreach($chart_data->result_array() as $row)
   {
    $output[] = array(
     'month'  => $row["month"],
     'profit' => floatval($row["profit"])
    );
   }
   echo json_encode($output);
  }
 }
 
}

?>

Create Models in Codeigniter

Models class mainly used for Database operation in Codeigniter framework. Here we have store models class in application/models/Dynamic_chart_model.php this folder. In this class we have make following method.

fetch_year() - This method has been used for get unique year data.

fetch_chart_data($year) - This method has been used for get particular year profit data. Year value has been get from this method argument and based on that year argument, it has fetch that year profit data.

application/models/Dynamic_chart_model.php

<?php

class Dynamic_chart_model extends CI_Model
{
 function fetch_year()
 {
  $this->db->select('year');
  $this->db->from('chart_data');
  $this->db->group_by('year');
  $this->db->order_by('year', 'DESC');
  return $this->db->get();
 }

 function fetch_chart_data($year)
 {
  $this->db->where('year', $year);
  $this->db->order_by('year', 'ASC');
  return $this->db->get('chart_data');
 }
}

?>

Create Views in Codeigniter

Views file has been display HTML output in browser. Here views file has been store under application/views folder and under this we have make dynamic_chart.php file. In this file we have used jQuery, Bootstrap and Google chart library. In this file you can find javascript and Ajax code for populate data Bar or Column chart by using Google chart library. Below you can find complete source code of this file.

application/views/dynamic_chart.php

<html>
<head>
    <title>Dynamic Column Chart in Codeigniter using Ajax</title>
    
 <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
</head>
<body>
 <div class="container">
  <br />
  <h3 align="center">Dynamic Column Chart in Codeigniter using Ajax</h3>
  <br />
  <div class="panel panel-default">
            <div class="panel-heading">
                <div class="row">
                    <div class="col-md-9">
                        <h3 class="panel-title">Month Wise Profit Data</h3>
                    </div>
                    <div class="col-md-3">
                        <select name="year" id="year" class="form-control">
                            <option value="">Select Year</option>
                        <?php
                        foreach($year_list->result_array() as $row)
                        {
                            echo '<option value="'.$row["year"].'">'.$row["year"].'</option>';
                        }
                        ?>

                        </select>
                    </div>
                </div>
            </div>
            <div class="panel-body">
                <div id="chart_area" style="width: 1000px; height: 620px;"></div>
            </div>
        </div>
 </div>
</body>
</html>

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">

google.charts.load('current', {packages:['corechart', 'bar']});
google.charts.setOnLoadCallback();

function load_monthwise_data(year, title)
{
    var temp_title = title + ' ' + year;
    $.ajax({
        url:"<?php echo base_url(); ?>dynamic_chart/fetch_data",
        method:"POST",
        data:{year:year},
        dataType:"JSON",
        success:function(data)
        {
            drawMonthwiseChart(data, temp_title);
        }
    })
}

function drawMonthwiseChart(chart_data, chart_main_title)
{
    var jsonData = chart_data;
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Month');
    data.addColumn('number', 'Profit');

    $.each(jsonData, function(i, jsonData){
        var month = jsonData.month;
        var profit = parseFloat($.trim(jsonData.profit));
        data.addRows([[month, profit]]);
    });

    var options = {
        title:chart_main_title,
        hAxis: {
            title: "Months"
        },
        vAxis: {
            title: 'Profit'
        },
        chartArea:{width:'80%',height:'85%'}
    }

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

    chart.draw(data, options);
}

</script>

<script>
    
$(document).ready(function(){
    $('#year').change(function(){
        var year = $(this).val();
        if(year != '')
        {
            load_monthwise_data(year, 'Month Wise Profit Data For');
        }
    });
});

</script>

Check Output in Browser

Now our all code is ready and we want to check output of above code. For this we have to go browser and write our base_url() like http://localhost/tutorial/codeigniter/dynamic_chart and press enter. It will load output of above code in browser. For check dynamic column or bar chart you have to select year from select box. After selecting year from select box you can get profit data of that year in populated Column or Bar chart without refresh of web page. So this way you can display selected year profit data on web page in Column chart format without refresh of web page. So, this is complete process of populate Google Chart Library Column chart with Dynamic Data by using Ajax in Codeigniter Framework.