Introduction
This tutorial shows how to sort table data in ascending or descending order in Codeigniter. To sort a particular column data in HTML table you have to click on the column header. I have created href link on the HTML table’s column header to make it clickable for sorting either descending or ascending order.
There are no. of client side ajax based jQuery or JavaScript library or plugins available in the market for sorting HTML table data. But here I will show you how you can achieve the the similar sorting functionality without using JavaScript or jQuery library.
Related Post:
Prerequisites
Apache 2.4 (Optional), PHP 7.3.5 – 7.4.27, Codeigniter 3.1.10 – 3.1.11
Project Directory
Create a root project directory called codeIgniter-3.1.10-sort-table-data as per your choice in the system. Then extract your Codeigniter zip folder into the root project directory.
In subsequent sections I may not mention the project root directory’s name. So you should assume that I am telling the file or directory name with respect to the project root directory.
MySQL table
I need to store data to or retrieve data from a persistence storage. Therefore, I need a persistence storage like file or database or any other source.
So let’s create a table in MySQL database server for this purpose.
CREATE TABLE `items` (
`item_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`item_name` varchar(45) COLLATE latin1_general_ci NOT NULL,
`item_desc` text COLLATE latin1_general_ci,
`item_price` double unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;For MySQL version 8.x, use the following structure:
CREATE TABLE `items` (
`item_id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
`item_name` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
`item_desc` text COLLATE utf8mb4_unicode_ci,
`item_price` double COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
PRIMARY KEY (`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=UTF8MB4_UNICODE_CI;Inserting Data
I am not performing any CRUD (Create, Read, Update, Delete) operations here, so, I need some data to test our application. Therefore, I will dump some data into the MySQL table.
insert into `items`(`item_id`,`item_name`,`item_desc`,`item_price`)
values (1,'CD','CD is a compact disk',100),
(2,'DVD','DVD is larger than CD in size',150),
(3,'ABC','ABC test description',24),
(4,'XYZ','XYZ test description',25.32),
(5,'CD Player','CD player is used to play CD',30.02);Configuring AutoLoad
You need to load database library and url helper to use uniformly throughout the application without loading repeatedly.
Make the following changes in application/config/autoload.php file.
$autoload['libraries'] = array('database');
$autoload['helper'] = array('url');Configuring Database
I am using MySQL database, so, I need to configure our MySQL database with database URL, name and credential for establishing connection.
'hostname' => 'localhost',
'username' => 'root',
'password' => 'root',
'database' => 'roytuts',
'dbdriver' => 'mysqli',For MySQL version 8.x, you can change the charset in addition to the above config:
'char_set' => 'utf8mb4',
'dbcollat' => 'utf8mb4_unicode_ci',View File
I need to display our data onto a view file. Therefore, I will create a view file called items.php under application/views folder.
Look at the view file carefully. I have created hyperlink only to each column header of the HTML table for sorting purpose.
I have css class attached to each header to identify whether this is a current sorted header and by what order.
Also I am checking whether the sort order is ascending or descending. By default the sort order is ascending.
Inside foreach loop I have assigned row class either as odd_col or as even_col and accordingly changes the background.
I also display a message No record found! if no record is found in the table.
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?><!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Table data sorting in asc or desc in Codeigniter</title>
<link rel="stylesheet" type="text/css" href="<?=base_url()?>assets/css/style.css"/>
</head>
<body>
<div>
<h1>Table data sorting in asc or desc example in Codeigniter</h1>
<div id="body">
<?php
if ($item_list) {
?>
<table class="datatable">
<thead>
<tr>
<th <?php echo($sort_by == 'item_name' ? 'class="sort_'.$sort_order.'"' : ''); ?>>
<?php
echo anchor("items/item_list/item_name/" .
(($sort_order == 'ASC' && $sort_by == 'item_name') ? 'DESC' : 'ASC'), 'Name');
?>
</th>
<th <?php echo($sort_by == 'item_desc' ? 'class="sort_'.$sort_order.'"' : ''); ?>>
<?php
echo anchor("items/item_list/item_desc/" .
(($sort_order == 'ASC' && $sort_by == 'item_desc') ? 'DESC' : 'ASC'), 'Description');
?>
</th>
<th <?php echo($sort_by == 'item_price' ? 'class="sort_'.$sort_order.'"' : ''); ?>>
<?php
echo anchor("items/item_list/item_price/" .
(($sort_order == 'ASC' && $sort_by == 'item_price') ? 'DESC' : 'ASC'), 'Price');
?>
</th>
</tr>
</thead>
<tbody>
<?php
$i = 0;
foreach ($item_list as $item) {
$col_class = ($i % 2 == 0 ? 'odd_col' : 'even_col');
$i++;
?>
<tr class="<?php echo $col_class; ?>">
<td>
<?php echo $item->item_name; ?>
</td>
<td>
<?php echo $item->item_desc; ?>
</td>
<td>
<?php echo $item->item_price; ?>
</td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php
} else {
echo '<div style="color:red;"><p>No Record Found!</p></div>';
}
?>
</div>
</div>
</body>
</html>Applying Style
I have included one css file in the <head/> section to apply some basic style for the table.
Create a css file called style.css under assets/css folder. The assets folder should be created under project root directory.
I have also included background image in the HTML table header. So create a folder img under assets folder and put the background image head.gif under it. The background image can be downloaded later attached at the bottom of this tutorial.
The css source code is given below.
table.datatable {
width:100%;
border: none;
background:#fff;
}
table.datatable td.table_foot {
border: none;
background: #fff;
text-align: center;
}
table.datatable tr.odd_col {
background: none;
}
table.datatable tr.even_col {
background: #ddd;
}
table.datatable td {
font-size:10pt;
padding:5px 10px;
border-bottom:1px solid #ddd;
text-align: left;
}
table.datatable th {
text-align: left;
font-size: 8pt;
padding: 10px 10px 7px;
text-transform: uppercase;
color: #fff;
background:url('../img/table/head.gif') left -5px repeat-x;
font-family: sans-serif;
}
table.datatable th a{
color: #fff;
}
table.datatable th.sort_ASC:after {
content: "▲";
}
table.datatable th.sort_DESC:after {
content: "▼";
}Controller Class
I need to handle request/response from end users. I also need to fetch data from model class and load data onto view.
Therefore, I will create below controller class in a file called Items.php under application/controllers folder with below source code.
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
/**
* Author: https://roytuts.com
*/
class Items extends CI_Controller {
function __construct() {
parent::__construct();
$this->load->model('item_model', 'item');
}
public function index() {
redirect('items/item_list');
}
function item_list($sort_by = 'item_name', $sort_order = 'ASC') {
$results = $this->item->get_item_list($sort_by, $sort_order);
$data['item_list'] = $results['item_list'];
$data['sort_by'] = $sort_by;
$data['sort_order'] = $sort_order;
$this->load->view('items', $data);
}
}
/* End of file items.php */
/* Location: ./application/controllers/Items.php */In the above controller class, by default, the function item_list() sorts the column data based on item_name in ascending order.
Model Class
I am performing database operations for our required functionalities and for this purpose, I need to create a model class in a file Item_Model.php under application/models folder.
<?php
if (!defined('BASEPATH'))
exit('No direct script access allowed');
/**
* Author: https://roytuts.com
*/
class Item_Model extends CI_Model {
private $items = 'items';
function __construct() {
}
function get_item_list($sort_by, $sort_order) {
$sort_order = ($sort_order == 'DESC') ? 'DESC' : 'ASC';
$sort_columns = array('item_name', 'item_desc', 'item_price');
$sort_by = (in_array($sort_by, $sort_columns)) ? $sort_by : 'item_name';
$sql = 'SELECT item_id, item_name, item_desc, item_price
FROM ' . $this->items . ' ORDER BY ' . $sort_by . ' ' . $sort_order;
$query = $this->db->query($sql);
$result['item_list'] = $query->result();
return $result;
}
}
/* End of file item_model.php */
/* Location: ./application/models/Item_Model.php */In the above model, I check for sort order and the default is ASC. I put the columns which will be sorted, in an array. I check the the column name for sort by, default value is item_name. Then I run the query.
Configuring Route
I need to let Codeigniter framework what is the default controller, so I need to update the below line in application/config/routes.php file.
$route['default_controller'] = 'items';Background Image
Download the attached below image which you need for table header.
Testing Ascending or Descending Sorting
Make sure your Apache 2.4 HTTP server (if you are using) is running on default port 80.
Home Page
Access the URL http://localhost in the browser, you will see below output.
Notice the default sort order is ascending as highlighted in the below image.
Sort – Descending Order
When you click twice on DESCRIPTION column to sort in descending order.
