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