PHP CodeIgniter 4 Google Pie Chart Integration Tutorial
Have you ever tried fetching data from the database and inserting it into the pie chart? In this tutorial, we will learn how to assimilate the CodeIgniter PHP Google Pie Chart notion practically.
We will also get to know how to display the data from the MySQL database and display sales figures in the Google pie chart.
How To Add Pie Chart In CodeIgniter 4 Application Using Google Chart Js
Step 1- Install New CodeIgniter Project
Step 2- Make Database Connection
Step 3- Create Table And Insert Data
Step 4- Create Chart Controller
Step 5- Create New Route
Step 6- Add Chart In View
Step 7- Start CI Project
Install New CodeIgniter App
In this step, we simply have to install or download the CodeIgniter application. We will do it by using the composer command:
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.
Make Database Connection
Here, in this step, we will have to add data into the chart, for which we open 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 Data
So, here, we start the localhost server or real server, after which we move to the PHPMyAdmin dashboard. Further, we jump on to the SQL query tab and execute the following command:
This will create a table and insert the dummy data into the database concurrently:
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');Create Chart Controller
We will need a controller file to connect to the database table and write the CodeIgniter SQL query to fetch the records based on the day and sales.
Firstly, we will create the controller file, after which we insert the recommended 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->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 New Route
Routes handle the request defined in the controller, make sure to open the app/Config/Routes.php and add the new routes as suggested:
$routes->setDefaultController('GColumnChartController');
$routes->get('/show-google-charts', 'GColumnChartController::initChart');
Build Pie Chart View File
Next is required to create the view file where we will constitute the pie chart using the Google chart js, bootstrap and most importantly the data coming from the database.
Then, we create the view file and copy the given code 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>Codeigniter Google Pie Charts Demo</title>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">
</head>
<body>
<body>
<div class="container">
<div class="mt-5">
<div id="GooglePieChart" style="height: 600px; width: 100%"></div>
</div>
</div>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script>
google.charts.load('visualization', "1", {
packages: ['corechart']
});
google.charts.setOnLoadCallback(drawBarChart);
// Pie Chart
google.charts.setOnLoadCallback(showBarChart);
function drawBarChart() {
var data = google.visualization.arrayToDataTable([
['Day', 'Products Count'],
<?php
foreach ($products as $row){
echo "['".$row['day']."',".$row['sell']."],";
}
?>
]);
var options = {
title: ' Pie chart data',
is3D: true,
};
var chart = new google.visualization.PieChart(document.getElementById('GooglePieChart'));
chart.draw(data, options);
}
</script>
</body>
</html>Start CI Project
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 pie chart in CodeIgniter and MySQL, with the lethal combination of Google chart js open source technology.
Thanks