Generate HTML Table From MySQL Database Using Codeigniter

Introduction

Here is an example showing you how to generate HTML table from MySQL database using Codeigniter. I will write here an example to create a table using CodeIgniter 3 framework from a multi-dimensional array. The CodeIgniter’s Table class provides functions that enable you to auto-generate HTML table data from arrays or database result sets.

In this example, I will create a web application to generate HTML table from MySQL database using Codeigniter. 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 3 framework to put data into HTML tabular format. I will create a table in MySQL database and retrieve the records from the table and display on a view in HTML tabular format.

Related Post:

codeigniter generate HTML table

Prerequisites

PHP 7.3.5 – 7.4.23, Apache HTTP Server 2.4 (Optional), CodeIgniter 3.1.9 – 3.1.11, MySQL Server 5.6 – 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;

For MySQL 5.x version, you can use the following structure:

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

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');

Now I will move to Codeigniter part and for this either you may use any editor like notepad or notepad++ or you may use any IDE to develop your application code.

If you want to use Netbeans IDE for working with Codeigniter then please read here how to configure Codeigniter, XAMPP with Netbeans.

Database Configuration

Now configure the database section in order to connect with MySQL database for performing database activities in the file application/config/database.php. Please replace below values with your own values.

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

$db['default']['username'] = 'root';
$db['default']['password'] = 'root';
$db['default']['database'] = 'roytuts';

Auto Load Configuration

Now auto-load database library because this is one time activity, and you may not want to load every time you want to query database. I have also auto-loaded table library in order to generate table from MySQL database using Codeigniter. Edit the appropriate sections in the file application/config/autoload.php.

$autoload['libraries'] = array('database', 'table');

Model Class

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

Now I will create model class in order to fetch data from database table called product. Create a php file product_model.php under application/models directory with below code.

<?php

if (!defined('BASEPATH'))
	exit('No direct script access allowed');
	
/**
* Description of Product_model
*
* @author https://roytuts.com
*/

class Product_model extends CI_Model {		
	
	private $product = 'product';

	function get_salesinfo() {
		$query = $this->db->get($this->product);

		if ($query->num_rows() > 0) {
			return $query->result();
		}
		
		return NULL;
	}
	
}

/* End of file product_model.php */
/* Location: ./application/models/product_model.php */

In the above model class, I have defined a function get_salesinfo() that just returns all records from the table and if there is no data we just return NULL.

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 controller class also communicates with the model class for sending data from database to end users. Create file product.php under application/controllers folder with the below code.

<?php

if (!defined('BASEPATH'))
	exit('No direct script access allowed');

/**
* Description of product
*
* @author https://roytuts.com
*/

class Product extends CI_Controller {
	
	function __construct() {
		parent::__construct();
		$this->load->model('product_model');
	}
	
	public function index() {
		$data['salesinfo'] = $this->product_model->get_salesinfo();
		$this->load->view('salesinfo', $data);
	}
	
}

/* End of file product.php */
/* Location: ./application/controllers/product.php */

In the above controller class, I have a default function index() and sending the fetched data from MySQL database to the view file where HTML table generation will take place using CodeIgniter API.

View

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

I will generate HTML table for MySQL data and Codeigniter provides a built-in functionality for generating such HTML table. So, I do not need to use any third party library for this.

<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>Generate HTML Table Data From MySQL Database Using Codeigniter 3</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			
			$this->table->set_heading('Product Id', 'Price', 'Sale Price', 'Sales Count', 'Sale Date');
			
			foreach ($salesinfo as $sf):
				$this->table->add_row($sf->id, $sf->price, $sf->sale_price, $sf->sales_count, $sf->sale_date);
			endforeach;
			
			echo $this->table->generate();
		?>
		
	</div>
</body>
</html>

In the above code I have first set the HTML table headings and then for each record I add the row, then finally I generate the HTML table.

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.

Now I have to point the Product controller class in application/config/routes.php file otherwise I won’t be able to see the generated table and I will get 404 page not found error.

$route['default_controller'] = 'product';

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 -S localhost:8000

Your application will be running on localhost and port 8000.

Testing HTML Table Generation Application

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

codeigniter 3 generate HTML table data

Hope you got an idea how to generate HTML table using CodeIgniter 3.

Source Code

Download

1 thought on “Generate HTML Table From MySQL Database Using Codeigniter

Leave a Reply

Your email address will not be published. Required fields are marked *