How to Create Chart using Google Chart in Codeigniter

Visualization of raw data from days in graphics or diagrams is now widespread. This is because graphs or charts are a graphical representation of data. So when we see data line by line, it is very difficult to understand what that data tells us. Assume that we have visualized this data in a chart or graph and can then understand what the result of it is. In summary, the chart or graph is the small graphical representation of large amounts of data. It is therefore very important in web development to display data in a diagram or graphic. In this post, we will now cover a topic such as creating a dynamic bar chart or a bar chart using Codeigniter using the Google Graphics Library with Ajax.

Here the dynamic diagram means that we retrieve dynamic data from MySQL data and display it in every Ajax request in the bar chart or bar chart. From the data in the MySQL database, we use the Google graphics library to create a dynamic bar chart using Ajax as part of Codeigniter. We all know that display data in graphic format is a basic requirement for any web application that requires the administration panel. In the administration area, the administrator always needed a diagram in his administration area to get a quick comparison of data such as profit, sales, new buyers or subscribers, etc. By using graphics, the administrator can get a quick comparison of data with filters such as month, year, etc.

For information on how to create a dynamic bar chart or column in the Codeigniter framework using the Google Graphics Library with Ajax, here we create a bar chart or bar chart that shows the year’s earnings data in monthly format. Therefore, the administrator can access the quick earnings data for each year per month in a selection of one year. You can then load the earnings data for a specific year into the table in the monthly column. When the administrator selected the year from the drop-down list, the Ajax request was activated, which retrieves results data from the MySQL database in JSON format and sends it to the Ajax request, which is known as the graphics library function, which converts to Google This function displays data in the bar or in the bar chart and displays it on the website without updating the website. Therefore, the administrator can display the earnings data of any year on the website in graphic format without updating the website. Because here we used Ajax's request to get data. Below is a step-by-step guide to creating bar or column charts in the Codeigniter framework using Ajax and the Google Graphics Library.

• Establish a database connection in Codeigniter
• Define the base URL in codeingiter
• Build drivers in Codeigniter
• Build models in Codeigniter
• Create views in Codeigniter
• Check the output in the browser

Make Database connection

In the Codeigniter application, we first want to create a table in the MySQL database and then establish the database connection in Codeigniter. Therefore, run the following SQL script in your MySQL database and create a 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;

Then we have to go to the working folder of the Codeigniter framework and open application / config / database.php and define the following mysql database configuration under this file to establish the 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 establishing the MySQL database connection in the Codeigniter application. Now we want to define the base URL of this codeigniter application. To do this, we have to open the application / config / config.php file and define the base URL of this codeigniter application under it.

<?php

......

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

.......

?>

Create Controllers in Codeigniter

The Codeigniter framework is based on the MVC style framework. So here we want to create a controller that processes the http request. In the Codeigniter driver file it was saved in the folder applications / drivers. In this tutorial we created the driver file Dynamic_chart.php. In this class we have to do the following method.

index (): This is the root method of this type of controller. With this method, we have the single year list for the drop down list of the year in which the view file was completed. This method is to load the view file into the browser.


fetch_data (): This method received an Ajax request to get earnings data for a specific year from Mysql Table. You receive Ajax for certain annual dates and send annual dates to the model class. The modal class sends result data to this controller method and converts the data to json format and sends it to Ajax's request. Then this simple operation of this method is received for the dates of the year and the earnings data of the year is sent to the Ajax application


application / controller / 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

Model class mainly used for the operation of databases within the framework of code signers. Here we have the business model class in application / models / Dynamic_chart_model.php this folder. In this class we have to do the following method.

fetch_year (): This method was used to get unique data for the year.


fetch_chart_data ($ year): This method was used to get earnings data for a specific year. The value of the year was determined from this argument of the method and, based on the argument of this year, the earnings data for this year were determined.


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

The HTML output was displayed in the browser in the view file. Here the views file was saved in the applications / views folder and below we created the dynamic_chart.php file. In this file we used jQuery, Bootstrap and the Google graphics library. In this file you will find Javascript and Ajax code for filling out the bar or column chart using the Google graphics library. Below is the full source code of this file.


application / views / dynamic_chart.php

<html>
<head>
    <title>How to Create Chart using Google Chart in Codeigniter</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">How to Create Chart using Google Chart in Codeigniter</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 all of our code is ready and we want to check the output of the previous code. To do this, we have to go to the browser and enter our base_url () as http: // localhost / tutorial / codeigniter / dynamic_chart and press Enter. The output of the previous code is loaded into the browser. To check the dynamic column or bar chart, you must select the year from the selection field. After you have selected the year from the selection field, you can call up the earnings data for this year in a filled column or a bar chart without updating the website. This allows you to view the earnings data for the selected year on the website in bar graph format without refreshing the website. Therefore, this is a complete process to complete the column chart of the Google graphics library with dynamic data that Ajax uses in the Codeigniter framework.