CodeIgniter 4 Google Column Charts Integration Tutorial

PHP CodeIgniter4 Tutorial: Google Column Chart Integration Example

Friends, so many people must have faced trouble in integrating the Google Column Charts in their CodeIgniter Apps but once the steps and method are known, this becomes quite easy and smooth to implement in the CodeIgniter apps.

Let’s start with this tutorial to know how can we create a dynamic Column Chart in the PHP CodeIgniter app despite having not much experience of the same.

 

How To Implement Google Column Charts With Database In CodeIgniter 4

Step 1- Create Latest CodeIgniter App

Step 2- Add Database Credentials

Step 3- Create Table And Insert Records

Step 4- Setting Up Route

Step 5- Formulate Controller

Step 6- Create Column Chart View File

Step 7- Run CodeIgniter Application

 

Download Latest CodeIgniter App

This first step can simply be skipped if you have already downloaded the advanced version of the CodeIgniter application. Otherwise, we can do it by using the composer tool which makes is quite easy: 

composer create-project codeigniter4/appstarter

 

We can also do this by the manual method using the given link:

https://codeigniter.com/download

 

Next is required to change the default project folder name to “my_ci_app” or any other name we want.

 

Add Database Credentials

Here, in this step, we will have to add the database credentials for which we get into the app/Config/Database.php and register the database name, username and password into the default array for smooth database connection:

public $default = [
        'DSN'      => '',
        'hostname' => 'localhost',
        'username' => 'root',
        'password' => '',
        'database' => 'codeigniter_db',
        'DBDriver' => 'MySQLi',
        'DBPrefix' => '',
        'pConnect' => false,
        'DBDebug'  => (ENVIRONMENT !== 'development'),
        'cacheOn'  => false,
        'cacheDir' => '',
        'charset'  => 'utf8',
        'DBCollat' => 'utf8_general_ci',
        'swapPre'  => '',
        'encrypt'  => false,
        'compress' => false,
        'strictOn' => false,
        'failover' => [],
        'port'     => 3306,
    ];

 

Create Table And Insert Records

So, here, we need to show the data or records for which, we require a database connection. We can this by running the given SQL command.

This will be creating a table with a product data table:

CREATE TABLE `product` (
  `id` int(11) NOT NULL COMMENT 'Primary Key',
  `name` varchar(255) NOT NULL COMMENT 'name',
  `sell` varchar(55) NOT NULL COMMENT 'sell',    
  `created_at` varchar(30) NOT NULL COMMENT 'created at'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Atomic database';


INSERT INTO `product` (`id`, `name`, `sell`, `created_at`) VALUES
(1, 'Coca Cola', '5000', '2021-05-01'),
(2, 'Pepsi', '7000', '2021-05-02'),
(3, 'Coke Zero', '19000', '2021-05-03'),
(4, 'Pepsi Max', '1500', '2021-05-04'),
(5, 'Diet Coke', '19000', '2021-05-05'),
(6, 'Pepsi Light', '3000', '2021-05-06'),
(7, 'Gatorade', '22000', '2021-05-07');

Setting Up Route

The loading view function will not work itself. For that, it needs a route to process the programming logic of the controller.

As a result, the below code has to be given in the app/Config/Routes.php file:

$routes->setDefaultController('GColumnChartController');

$routes->get('/google-column-chart', 'GColumnChartController::initChart');

Formulate Controller

We will need to formulate the controller file is what we need to do in this step. It is done since we have to load the view file in the browser and request the database to retrieve the product records to insert in the column chart.

Then, we will create the controller file, after which we insert the below code inside the app/Controllers/GColumnChartController.php file:

<?php 

namespace App\Controllers;
use CodeIgniter\Controller;


class GColumnChartController extends Controller
{

    public function index() {
        return view('chart');
    }
    
    public function initChart() {
        
        $db = \Config\Database::connect();
        $builder = $db->table('product');

        $query = $builder->select("COUNT(id) as count, sell as s, DAYNAME(created_at) as day");
        $query = $builder->orderBy("s ASC, day ASC");
        $query = $builder->where("DAY(created_at) GROUP BY DAYNAME(created_at), s")->get();
        $record = $query->getResult();

        $products = [];

        foreach($record as $row) {
            $products[] = array(
                'day'   => $row->day,
                'sell' => floatval($row->s)
            );
        }
        
        $data['products'] = ($products);    
        return view('chart', $data);                
    }
 
}

 

Create Column Chart View File

Next is required to add the Google charts js in the view file to create the chart.

Then, we create the view file in the Views directory and add it into the app/Views/chart.php file:

<!DOCTYPE html>
<html lang="en">

<head>
	<meta charset="UTF-8">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<title>Google Column Chart in Codeigniter</title>
	<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">
</head>

<body>

	<body>
		<div class="container">
			<div id="GoogleColumnChart" style="height: 500px; width: 100%"></div>
		</div>

		<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
		<script>
			google.charts.load('visualization', "1", {
				packages: ['corechart']
			});

			function showChart() {
				var data = google.visualization.arrayToDataTable([
					['Day', 'Products Count'], 
					<?php foreach($products as $row) {
						echo "['".$row['day']."',".$row['sell']."],";
					} ?>
				]);

				var options = {
					title: 'Last week sales',
					isStacked: true
				};

				var chart = new google.visualization.ColumnChart(document.getElementById('GoogleColumnChart'));
				chart.draw(data, options);
			}
			google.charts.setOnLoadCallback(showChart);
		</script>

	</body>

</html>

Run CodeIgniter Application

So, all the steps have been completed. Now, we have to test for which we will use PHP spark command to summon the development server:

php spark serve

 

Summary

So friends, hope that you have learned how to create an interactive, dynamic and data-driven column chart integration in CodeIgniter and MySQL, with the lethal combination of Google chart js open source technology.

Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *