CodeIgniter 4 Generate HTML Table Data

Introduction

The tutorial, CodeIgniter 4 Generate HTML Table will show you how to fetch data from MySQL database and generate HTML data using CodeIgniter 4 framework. You generally iterate through a list of records and put them into an HTML table for displaying to end users. But here I am going to show you how to use CodeIgniter 4 framework to put data into HTML tabular format.

The Table Class provides functions that enable you to auto-generate HTML tables from arrays or database result sets.

Related Post:

CodeIgniter 3 MySQL Generate HTML Table Data

Prerequisites

PHP 7.4.23, CodeIgniter 4.1.4, MySQL Server 8.0.26

Project Directory

It’s assumed that you have setup PHP and CodeIgniter in your system.

Now I will create a project root directory called codeigniter-html-table-generation.

Now move all the directories and files from CodeIgniter framework into the project root directory.

I may not mention the project root directory in subsequent sections, and I will assume that I am talking with respect to the project root directory.

MySQL table

First thing is to create a database table in MySQL database. In this example the database table will contain date wise sales information for products. Therefore, I will create a table called product in MySQL database. If you are using MySQL version less than 8 then you need to specify the size of the column value for int data type.

CREATE TABLE `product` (
	`id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
	`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
	`price` double COLLATE utf8mb4_unicode_ci NOT NULL,
	`sale_price` double COLLATE utf8mb4_unicode_ci NOT NULL,
	`sales_count` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL,
	`sale_date` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Run the above table creation script into the MySQL server and a table with the name product will be created. You may have multiple tables with normalization but here for example purpose the above table would be enough to show an idea how to generate HTML table from MySQL database using Codeigniter.

In order to test the application, I need some data in the above table. So, insert some dummy data into the products table.

insert into product(id,name,price,sale_price,sales_count,sale_date) values(1, 'Desktop','30000','35000','55','02-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(2, 'Desktop','30300','37030','43','03-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(3, 'Tablet','39010','48700','145','10-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(4, 'Phone','15000','17505','251','05-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(5, 'Phone','18000','22080','178','05-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(6, 'Tablet','30500','34100','58','05-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(7, 'Adapter','2000','2500','68','06-10-2021');
insert into product(id,name,price,sale_price,sales_count,sale_date) values(8, 'TV','45871','55894','165','07-10-2021');

Database Configuration

You need to setup database connection in order to fetch or write data to the table. The following configuration is done in the file app/Config/Database.php under the default group of database setting. Make sure you change or update the configuration according to yours. You can also setup your database using the .env file.

The main properties I have shown below, and you can change according to your values:

...
'username' => 'root',
'password' => 'root',
'database' => 'roytuts',
...
'charset'  => 'utf8mb4',
'DBCollat' => 'utf8mb4_unicode_ci',
...

Model Class

The model class is responsible for interacting with database and performing required activities as requested by clients.

The following code is written into the file app/Models/ProductModel.php file:

<?php

namespace App\Models;
use CodeIgniter\Model;

class ProductModel extends Model {
	
	protected $product = 'product';
	
	function get_product_list() {        
		$query = $this->db->table($this->product)->get();
        
		return $query->getResult();
    }
	
}

In the above model class, I have defined a function get_product_list() that just returns all records from the table.

Controller Class

The controller class is the entry point for the web application and a controller class handles request and response coming from and going back to clients.

The controller class performs the business logic for the application. The controller class is also responsible for validating, sanitizing, filtering the malformed request data before the data can be processed further for the application requirements.

The code for controller class is written into a file app/Controllers/ProductController.php.

<?php

namespace App\Controllers;

use App\Models\ProductModel;

use App\Libraries\PdfLibrary;

class ProductController extends BaseController {
	
    public function index()	{
		//$model = new ProductModel();
		
		//$data['salesinfo'] = $model->get_product_list();
		
		$table = new \CodeIgniter\View\Table();
		
		$template = array(
			'table_open' => '<table border="1" cellpadding="2" cellspacing="1">'
		);

		$table->setTemplate($template);

		$table->setHeading('Product Id', 'Price', 'Sale Price', 'Sales Count', 'Sale Date');
		
		$model = new ProductModel();
		
		$salesinfo = $model->get_product_list();
			
		foreach ($salesinfo as $sf):
			$table->addRow($sf->id, $sf->price, $sf->sale_price, $sf->sales_count, $sf->sale_date);
		endforeach;
		
		$html = $table->generate();
		
		$data['table'] = $html;
		
		return view('product', $data);
	}
	
}

In the above controller class, I have a default function index() and I have commented two lines of code and about these two lines I will explain later.

Now coming to the rest of the uncommented code. First, I have loaded Table class to create an instance from CodeIgniter 4 API. Next I have built template, heading for the table followed by fetching data from MySQL and iterating for adding row to the HTML table. Finally I have used generate() function to generate the HTML table which will just be echoed to display on UI (User Interface).

Now coming to the commented two lines of code, these two lines of code just fetched the data from MySQL database and builds the array which will be loaded in the view by using the following function:

return view('product', $data);

Finally you have to iterate through the list of data and generate HTML table in the view instead of controller function. The same thing you will see that I have commented HTML table generation part in view file because I already have generated in the controller function.

View

The view file which will display the data or UI (User Interface) to the end users. The app/Views/product.php displays all sales information for products in HTML tabular format.

<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>Generate HTML Table Data From MySQL Database Using Codeigniter 4</title>
	<style>
		table {
			font-family: arial, sans-serif;
			border-collapse: collapse;
			width:
			100%;
		}
		
		td, th {
			border: 1px solid #dddddd;
			text-align: left;
			padding: 8px;
		}
		
		tr:nth-child(even) {
			background-color: #dddddd;
		}
	</style>
</head>
<body>
	<div style="margin: auto;width: 600px">
		<h3>Sales Information</h3>

		<?php
			echo $table;
		
			/*$table = new \CodeIgniter\View\Table();
			
			$table->setHeading('Product Id', 'Price', 'Sale Price', 'Sales Count', 'Sale Date');
			
			foreach ($salesinfo as $sf):
				$table->addRow($sf->id, $sf->price, $sf->sale_price, $sf->sales_count, $sf->sale_date);
			endforeach;
			
			echo $table->generate();*/
		?>
		
	</div>
</body>
</html>

In the above view file, notice I have commented HTML table generation part because I have generated HTML table in the controller class’s function.

Route Configuration

You also need to configure route to point to your own controller file instead of the default controller that comes with the framework.

Search for the line $routes->setDefaultController(‘Home’); and replace it by $routes->setDefaultController('ProductController');.

Search for the line $routes->get('/', 'Home::index'); and replace it by your controller name, for this example, $routes->get('/', ‘ProductController::index');.

These route configurations are done on the file app/Config/Routes.php.

Deploying the Application

I am not going to use any external server but CLI command to run the application. Make sure you start the MySQL database server before you start your application. If you want to use external server to run your application, you can use, for example, Apache HTTP Server. Execute the following command on your project root directory to run your application.

php spark serve

Your application will be running on localhost and port 8080.

Testing HTML Table Generation Application

When you hit the UTR http://localhost:8080 in the browser, you will see the following page:

Hope you got an idea hot to use CodeIgniter 4 and MySQL to generate HTML table data.

Source Code

Download

Roy Tutorials