Codeigniter 4 Google Column Charts Integration Tutorial

Last updated on: by Editorial Team

PHP Codeigniter 4 Google Column Chart integration tutorial; Do you want to create a dynamic column chart in PHP Codeigniter and have no prior experience? All you need this friendly tutorial to get started with.

In this bit by bit guide, we will explain how to implement Google column charts in Codeigniter 4 application dynamically.

To add the dynamic column charts in Codeigniter, we will create a MySQL database with a table, pour some data into it, make the request through controller and route, fetch the records, and primarily price the products into and display the data in the column chart.

This example of Codeigniter google column charts will use the gstatic charts js to draw the column chart in the Codeigniter app. We will show you how to get the product’s sales price day wise and represent the sales on the Google column chart after retrieving the data from the database.

How to Implement Google Column Chats 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

Create Latest Codeigniter App

If you haven’t installed the app, head over to the given link and direct download the most advanced version of the Codeigniter app.

https://codeigniter.com/download

Don’t forget to update the default project folder name to “test” or anything you want.

The Composer tool makes the installation process super easy.

composer create-project codeigniter4/appstarter

Add Database Credentials

Get into the app/Config/Database.php, open the file, jump on to default property, and add database details like DB name, user name and password.

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

To show data in a chart requires a database connection; you can do it easily by running the given below SQL command.

The following command will create a table with product data table; later, we will sort the table values by sell by days in week.

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 we kept in the controller won’t work by itself. It requires a route to process the programming logic of the controller.

Consequently, add the given routes in the app/Config/Routes.php file.

$routes->setDefaultController('GColumnChartController');

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

Formulate Controller

Formulation of a controller is our next step. Why so? 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.

Create controller file and add the given below code in app/Controllers/GColumnChartController.php.

<?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

You have reached almost the final section of this guide; now, you have to add Google charts js in the view file to create the chart.

First, create the view file in the Views directory, then add code in 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/bootstrap@5.0.2/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>

We used the custom function showChart() to invoke the chart js function, also injected the foreach loop in this method to receive the data response from controller.

Run Codeigniter Application

Last instruction going towards running the Codeigniter app using the PHP spark command.

php spark serve

If you are developing this module locally, append the url in the browser and hit enter.

http://localhost:8080/google-column-chart

PHP Codeigniter 4 Google Column Chart Example

Conclusion

During this Codeigniter column chart tutorial, we discovered how to make a dynamic google column charts module in Codeigniter using MySQL. It is a simple and easy guide for beginners, still appropriate enough to be an expert in dealing with charts; we reckon you liked this tutorial.