PHP Codeigniter 4 Google Column Charts Tutorial Example

A comprehensive Codeigniter 4 google column chart example. In this step-by-step guide, we will teach you how to add google charts in the Codeigniter application with mysql database.

Furthermore, we will explain how to get day-wise users’ data from the database and insert it into the google chart, and display information appropriately in a google column chart.

A bar chart or bar graph is a chart or graph used to show categorical data with square bars; data can be represented using heights or lengths proportional to the values they signify.

In this Codeigniter column chart tutorial, we will show how to implement a vertical google column chart in Codeigniter similarly display column chart with dynamic data by getting the data from the database.

How to Integrate Google Column Chats in Codeigniter 4 App

  • Step 1: Download Codeigniter Project
  • Step 2: Update Database Details
  • Step 3: Create Database and Table
  • Step 4: Make Controller File
  • Step 5: Register Route
  • Step 6: Create View File
  • Step 7: Run App in Browser

Download Codeigniter Project

Another method to download the app is by using the Composer command.

composer create-project codeigniter4/appstarter

The following url will land you on the official site, where you can download the latest version of the Codeigniter app.

https://www.codeigniter.com/download

Update Database Details

In order to connect the app to the database, you have to update the database credentials into the app/Config/Database.php file.

public $default = [
        'DSN'      => '',
        'hostname' => 'localhost',
        'username' => 'db_username',
        'password' => 'db_password',
        'database' => 'db_name',
        '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 Database and Table

In this step, you have to run the provided command to create a new database, table at the same time insert the data into the database.

CREATE TABLE `users` (
  `id` int(11) NOT NULL COMMENT 'Primary Key',
  `name` varchar(255) NOT NULL COMMENT 'name',
  `sales` varchar(55) NOT NULL COMMENT 'sales',    
  `created_at` varchar(30) NOT NULL COMMENT 'created at'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='user db';

INSERT INTO `users` (`id`, `name`, `sales`, `created_at`) VALUES
(1, 'Marie Merritt', '2000', '2020-01-02'),
(2, 'Shoshana Jones', '3000', '2020-02-03'),
(3, 'Rickey Peed', '1000', '2020-03-02'),
(4, 'Sean Walker', '2500', '2020-04-05'),
(5, 'Patrick Frazier', '6000', '2020-05-04'),
(6, 'Neil Chevalier', '27000', '2020-06-07'),
(7, 'James Jackson', '1600', '2020-07-05'),
(8, 'Kayla George', '1200', '2020-08-06');

Make Controller File

In this section, you have to create the controller file and insert the functions to load the chart data from the database and display it into the chart using the SQL query.

Update code in app/Controllers/GraphController.php file.

<?php 

namespace App\Controllers;
use CodeIgniter\Controller;


class GraphController extends Controller
{
    public function index() {
        return view('index');
    }
    
    public function showGraph() {
        
        $db = \Config\Database::connect();
        $builder = $db->table('users');

        $query = $builder->select("COUNT(id) as count, sales as s, MONTHNAME(created_at) as month");
        $query = $builder->orderBy("s ASC, month ASC");
        $query = $builder->where("MONTH(created_at) GROUP BY MONTHNAME(created_at), s")->get();
        $record = $query->getResult();

        $usersData = [];

        foreach($record as $row) {
            $usersData[] = array(
                'month'   => $row->month,
                'sales' => floatval($row->s)
            );
        }
        
        $data['usersData'] = ($usersData);    
        return view('index', $data);                
    }
}

Register Route

In this section, you have to open the app/Config/Routes.php and in this file, you have to register a new route on this url; you can view the google column chart in the Codeigniter app.

/*
 * --------------------------------------------------------------------
 * Route Definitions
 * --------------------------------------------------------------------
*/

$routes->get('/display-column-chart', 'GraphController::showGraph');

Create View File

In this section, we will create index.php file inside the app/Views folder and then you have to add the provided code into the app/Views/index.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 Column Chart MySQL Database Example</title>

	<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css">
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
</head>

<body>

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

		
		<script>
			google.charts.load('visualization', "1", {
				packages: ['corechart']
			});

			function initGraph() {
				var data = google.visualization.arrayToDataTable([
					['Day', 'Users count'], 
					<?php foreach($usersData as $row) {
						echo "['".$row['month']."',".$row['sales']."],";
					} ?>
				]);

				var options = {
					title: 'Sales by month',
					isStacked: true
				};

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

	</body>

</html>

Run App in Browser

In the final section, you need to copy the provided command and run the Codeigniter server.

After the server started, make sure to type the given url on the browser and hit enter to view the column graph.

php spark serve
http://localhost:8080/display-column-chart

PHP Codeigniter 4 Google Column Charts Tutorial Example

Summary

In this tutorial, we demonstrated how to implement Google Column Charts in Codeigniter 4 app and dynamically fetch users’ data day-wise from MySQL using SQL query in the Codeigniter app.