In this tutorial, you will learn how to create dynamic pie charts using Google Charts JS and display month-wise and year-wise data from MySQL in CodeIgniter 4 pie chart.
Let’s get started to create dynamic pie chart and display data in it from MySQL database using google chart js:
Step 1: Setup Codeigniter 4 Project
In this step, you will download the latest version of Codeigniter 4, Go to this link https://codeigniter.com/download Download Codeigniter 4 fresh new setup and unzip the setup in your local system xampp/htdocs/ . And change the download folder name “demo”
Step 2: Configure CodeIgniter Project
Next, you will set some basic configuration on the app/config/app.php file, so let’s go to application/config/config.php and open this file on text editor.
Set Base URL like this
public $baseURL = 'http://localhost:8080'; To public $baseURL = 'http://localhost/demo/';
Step 3: Add Record in Database
Insert some records to the database using the following SQL query:
CREATE TABLE users (
id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
name varchar(100) NOT NULL COMMENT 'Name',
email varchar(255) NOT NULL COMMENT 'Email Address',
contact_no varchar(50) NOT NULL COMMENT 'Contact No',
created_at varchar(20) NOT NULL COMMENT 'Created date',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=1;
INSERT INTO users (id, name, email, contact_no, created_at) VALUES
(1, 'Team', '[email protected]', '9000000001', '2019-01-01'),
(2, 'Admin', '[email protected]', '9000000002', '2019-02-01'),
(3, 'User', '[email protected]', '9000000003', '2018-03-01'),
(4, 'Editor', '[email protected]', '9000000004', '2019-04-01'),
(5, 'Writer', '[email protected]', '9000000005', '2017-05-01'),
(6, 'Contact', '[email protected]', '9000000006', '2019-06-01'),
(7, 'Manager', '[email protected]', '9000000007', '2019-07-01'),
(8, 'John', '[email protected]', '9000000055', '2016-08-01'),
(9, 'Merry', '[email protected]', '9000000088', '2019-09-01'),
(10, 'Keliv', '[email protected]', '9000550088', '2019-10-01'),
(11, 'Herry', '[email protected]', '9050550088', '2019-11-01'),
(12, 'Mark', '[email protected]', '9050550998', '2019-12-01');
Step 4: Setup Database Credentials
In this step, you need to connect our project to the database. you need to go app/Config/Database.php and open database.php file in text editor. After opening the file in a text editor, you need to set up database credentials in this file like below.
public $default = [
'DSN' => '',
'hostname' => 'localhost',
'username' => 'root',
'password' => '',
'database' => 'demo',
'DBDriver' => 'MySQLi',
'DBPrefix' => '',
'pConnect' => false,
'DBDebug' => (ENVIRONMENT !== 'production'),
'cacheOn' => false,
'cacheDir' => '',
'charset' => 'utf8',
'DBCollat' => 'utf8_general_ci',
'swapPre' => '',
'encrypt' => false,
'compress' => false,
'strictOn' => false,
'failover' => [],
'port' => 3306,
];
Step 5: Create a Controller File and Methods
Go to app/Controllers folder and create a controller name GooglePie.php, and then add the following method into it to pass data in pie chart view:
<?php namespace App\Controllers;
use CodeIgniter\Controller;
use CodeIgniter\HTTP\RequestInterface;
class GooglePie extends Controller
{
public function index() {
$db = \Config\Database::connect();
$builder = $db->table('users');
$query = $builder->query("SELECT COUNT(id) as count,MONTHNAME(created_at) as month_name FROM users WHERE YEAR(created_at) = '" . date('Y') . "'
GROUP BY YEAR(created_at),MONTH(created_at)");
$query1 = $builder->query("SELECT COUNT(id) as count,YEAR(created_at) as year FROM users
GROUP BY YEAR(created_at)");
$data['month_wise'] = $query->getResult();
$data['year_wise'] = $query1->getResult();
return view('home',$data);
}
}
Step 6: Create Pie Chart View
In this step, you need to create one view files name home.php and update the following code into your file:
<!Doctype html>
<html>
<head>
<title>Codeigniter 4 Google Pie Chart Month and Year Wise Tutorial Tutsmake.com</title>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('visualization', "1", {
packages: ['corechart']
});
</script>
</head>
<body>
<div class="row">
<div class="col-md-12">
<div id="month_pie" style="width: 900px; height: 500px; margin: 0 auto"></div>
<div id="year_pie" style="width: 900px; height: 500px; margin: 0 auto"></div>
</div>
</div>
</body>
</html>
Add the following script in pie chart view file to display data into it:
<script language="JavaScript">
// Draw the pie chart for registered users month wise
google.charts.setOnLoadCallback(monthWiseChart);
// Draw the pie chart for registered users year wise
google.charts.setOnLoadCallback(yearWiseChart);
//for month wise
function monthWiseChart() {
/* Define the chart to be drawn.*/
var data = google.visualization.arrayToDataTable([
['Month', 'Users Count'],
<?php
foreach ($month_wise as $row){
echo "['".$row->month_name."',".$row->count."],";
}
?>
]);
var options = {
title: 'Month Wise Registered Users Of Current Year <?php echo date("Y")?>',
is3D: true,
};
/* Instantiate and draw the chart.*/
var chart = new google.visualization.PieChart(document.getElementById('month_pie'));
chart.draw(data, options);
}
// for year wise
function yearWiseChart() {
/* Define the chart to be drawn.*/
var data = google.visualization.arrayToDataTable([
['Year', 'Users Count'],
<?php
foreach ($year_wise as $row){
echo "['".$row->year."',".$row->count."],";
}
?>
]);
var options = {
title: 'Year Wise Registered Users Pie Chart',
is3D: true,
};
/* Instantiate and draw the chart.*/
var chart = new google.visualization.PieChart(document.getElementById('year_pie'));
chart.draw(data, options);
}
</script>
Step 7: Define Routes
Create a route in app/Config/Routes.php file that handle pie chart views request:
$routes->get('/', 'GooglePie::index');
Step 8: Test This Project
In this step, open your terminal and execute the following command to start development sever:
php spark serve
Then, Go to the browser and hit below the URL:
http://localhost:8080
Conclusion
Codeigniter 4 google pie chart example. In this tutorial, you have learned how to create google pie chart with codeigniter 4 and mysql
Recommended Codeigniter Tutorials
If you have any questions or thoughts to share, use the comment form below to reach us.
