How to Create Google Charts with CodeIgniter and MySQL

Maangatech > Web Design > How to Create Google Charts with CodeIgniter and MySQL

chart

The use of graphics is very common in web applications, and the most commonly used ones are generated using the JavaScript language, combined with a server-side language to provide the data.

In this tutorial, you’ll learn how to integrate google’s graphics library, called Google Charts, with CodeIgniter and MySQL.

Requirements

  • Codeigniter Framework (I will use V 3 in this tutorial)
  • Code editor

1. Download and install CodeIgniter if you have not done it yet

  • Make sure you create the database and connect it with your Codigniter.
  • Make sure Database and url is loaded in autoload

2. Create table and populate it with Data

By using PHPMyAdmin create the table named Fruits and populate it with data by using the SQL codes below. Here we are creating the table that will hold data for our example.

-- 
-- Table structure for table `Fruits` 
-- 
 
CREATE TABLE IF NOT EXISTS `Fruits` ( 
  `id` int(10) NOT NULL AUTO_INCREMENT, 
  `fruits_name` varchar(200) DEFAULT NULL, 
  `quantity` int(10) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; 
 
-- 
-- Dumping data for table `Fruits` 
-- 
 
INSERT INTO `Fruits` (`id`, `fruits_name`, `quantity`) VALUES 
(1, 'Mango', 20), 
(2, 'Pineapple', 50), 
(3, 'Apple', 30), 
(4, 'Banana', 10), 
(5, 'Orange', 25);

3. In Codeigniter navigate to application/models directory to create model

Create the new model called Our_chart_model and add the code snippet below. This model will fetch data from the Database

<?php 
class Our_chart_model extends CI_Model 
{ 
    function __construct() 
    { 
        parent::__construct(); 
    } 
    //get fruts data 
    public function get_all_fruits() 
    { 
        return $this->db->get('Fruits')->result(); 
    } 
}

4. Navigate to application/controllers directory to create Controller

Knowing that you will need two methods in the controller, first method for loading the view page and the second method will be used to return the JSON data for the JavaScript to render the chart.

So, create the controller named Our_Chart and add the code snippet below. You can modify them according to your preference.

<?php 
 
if (!defined('BASEPATH')) exit('No direct script access allowed'); 
class Our_Chart extends CI_Controller 
 
    { 
    function __construct() 
        { 
        parent::__construct(); 
        $this->load->model('Our_chart_model'); 
 
        // $this->load->library('form_validation'); 
 
        $this->load->helper('string'); 
        } 
 
    public 
 
    function index() 
        { 
        $this->load->view('Chart_view'); 
        } 
 
    public 
 
    function getdata() 
        { 
        $data = $this->Our_chart_model->get_all_fruits(); 
 
        //         //data to json 
 
        $responce->cols[] = array( 
            "id" => "", 
            "label" => "Topping", 
            "pattern" => "", 
            "type" => "string" 
        ); 
        $responce->cols[] = array( 
            "id" => "", 
            "label" => "Total", 
            "pattern" => "", 
            "type" => "number" 
        ); 
        foreach($data as $cd) 
            { 
            $responce->rows[]["c"] = array( 
                array( 
                    "v" => "$cd->fruits_name", 
                    "f" => null 
                ) , 
                array( 
                    "v" => (int)$cd->quantity, 
                    "f" => null 
                ) 
            ); 
            } 
 
        echo json_encode($responce); 
        } 
    }

Data received from Our_chart_model will be used to create JSON data for google pie chart. In our example, we will add data to JSON variable by using foreach but you can pass other variables from the db to JSON without using foreach statement; this depends on the nature of your data.

5. Next create the view file

After creating the controller now create the view file named Chart_view and paste the following code snippet below.

<!DOCTYPE html> 
  <head> 
  <title>Google Chart and Codeigniter with MySQL</title> 
    <!--Load the AJAX API--> 
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> 
    <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script> 
    <script type="text/javascript"> 
     
    // Load the Visualization API and the piechart package. 
    google.charts.load('current', {'packages':['corechart']}); 
       
    // Set a callback to run when the Google Visualization API is loaded. 
    google.charts.setOnLoadCallback(drawChart); 
       
    function drawChart() { 
      var jsonData = $.ajax({ 
          url: "<?php echo base_url() . 'index.php/Our_Chart/getdata' ?>", 
          dataType: "json", 
          async: false 
          }).responseText; 
           
      // Create our data table out of JSON data loaded from server. 
      var data = new google.visualization.DataTable(jsonData); 
 
      // Instantiate and draw our chart, passing in some options. 
      var chart = new google.visualization.PieChart(document.getElementById('chart_div')); 
      chart.draw(data, {width: 900, height: 500}); 
    } 
 
    </script> 
<style> 
h1 { 
    text-align: center; 
} 
</style> 
  </head> 
 
  <body> 
    <!--Div that will hold the pie chart--> 
    <h1>Quantity of fruits we have in our store - Displayed by Google Chart and Codeigniter with MySQL</h1> 
    <div id="chart_div"></div> 
  </body> 
</html>

The div my-chart will be the container that will receive the chart, and the added scripts are the jQuery library, the Google Chart chart library, and the script that will render the chart when loading the page, respectively.

Now our example is ready, with all the necessary codes. This is just one of the many types of charts that the Google Charts library provides.  You may visit Google chart home page for more types of charts offered by Google.

SEE DEMO

Does this example work for you? Do you have the alternative way of achieving this task?

Related Post