In this tutorial I am going to show you how to sort MySQL table data in ascending or descending order to display them on HTML table on user interface (UI) using Codeigniter 4. I had also shown similar example using CodeIgniter 3. So when a user wants to see the data on UI either in ascending or descending order then the user needs to click on the table column header to sort the data on HTML table.
I have created href link on the HTML table’s column header to make it clickable for sorting the table data either in descending or ascending order.
There are number 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.
Prerequisites
Apache 2.4, PHP 7.4.3, Codeigniter 4.0.4, MySQL 8.0.17
Project Directory
It’s assumed that you have setup Apache, PHP and CodeIgniter in Windows system.
Now I will create a project root directory called codeigniter-4-table-data-sort-asc-desc the Apache server’s htdocs folder.
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
For this example I am going to create and insert some sample data into the table item under roytuts database.
CREATE TABLE `item` (
`id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
`name` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
`desc` text COLLATE utf8mb4_unicode_ci,
`price` double unsigned COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
insert into `item`(`id`,`name`,`desc`,`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);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
You need a model class to perform database activities, for example, for implementing CRUD operations. The below model class is written into app/Models/ItemModel.php file.
In CodeIgniter you can choose two ways of modeling your data – using CodeIgniter’s model and using entity class. I am using CodeIgniter’s model to create my own model that extends CodeIgniter’s model class. They come out of the box with helper methods for much of the standard ways you would need to interact with a database table, including finding records, updating records, deleting records, and more.
I am creating my own function in the model class to sort the table data in ascending or descending order that comes as an input from the end user.
Using the standard query() method I am executing my own query and finally return the result using the getResult() method in CodeIgniter 4.
<?php
namespace App\Models;
use CodeIgniter\Model;
class ItemModel extends Model {
protected $table = 'item';
function get_item_list($sort_by, $sort_order) {
$sort_order = ($sort_order == 'DESC') ? 'DESC' : 'ASC';
$sort_columns = array('name', 'desc', 'price');
$sort_by = (in_array($sort_by, $sort_columns)) ? '`' . $sort_by . '`' : '`name`';
$sql = 'SELECT `id`, `name`, `desc`, `price` FROM ' . $this->table .
' ORDER BY ' . $sort_by . ' ' . $sort_order;
$query = $this->db->query($sql);
return $query->getResult();
}
}Controller
The following controller (app/Controllers/Item.php) defines the functions for handling user’s request/response and calling the appropriate views and interact with database table to perform the required activities.
<?php namespace App\Controllers;
use App\Models\ItemModel;
class Item extends BaseController {
public function index() {
return redirect()->to(site_url('item/item_list'));
}
function item_list($sort_by = 'name', $sort_order = 'ASC') {
$model = new ItemModel();
$data['item_list'] = $model->get_item_list($sort_by, $sort_order);
$data['sort_by'] = $sort_by;
$data['sort_order'] = $sort_order;
return view('items', $data);
}
}In the above controller I have defined two functions. The index() function is the default route which I want to use for this functionality. I have defined another function list_item() where I have specified the sort order and sort by. From the index() function I have redirected to the list_item() because I don’t want to show any sorting order when user lands on home page. Therefore the default sorting order is ascending and sorted by name of the item when the home page gets displayed.
View File
You need to display your data onto a view file. Therefore you will need to create a view file called items.php under app/Views folder.
Look at the view file carefully. You have only hyperlink to each column header of the HTML table for sorting purpose.
I have attached css class 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 you 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.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Sort Table Data in Ascending or Descending Order in CodeIgniter 4 and MySQL 8</title>
<meta name="description" content="The small framework with powerful features">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="shortcut icon" type="image/png" href="/favicon.ico"/>
<link rel="stylesheet" type="text/css" href="/css/table.css"/>
<!-- STYLES -->
<style {csp-style-nonce}>
* {
transition: background-color 300ms ease, color 300ms ease;
}
*:focus {
background-color: rgba(221, 72, 20, .2);
outline: none;
}
html, body {
color: rgba(33, 37, 41, 1);
font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji";
font-size: 16px;
margin: 0;
padding: 0;
-webkit-font-smoothing: antialiased;
-moz-osx-font-smoothing: grayscale;
text-rendering: optimizeLegibility;
}
footer {
background-color: rgba(221, 72, 20, .8);
text-align: center;
}
footer .environment {
color: rgba(255, 255, 255, 1);
padding: 2rem 1.75rem;
}
footer .copyrights {
background-color: rgba(62, 62, 62, 1);
color: rgba(200, 200, 200, 1);
padding: .25rem 1.75rem;
}
</style>
</head>
<body>
<div>
<h1>Sort Table Data in Ascending or Descending Order in CodeIgniter 4 and MySQL 8</h1>
<div id="body">
<?php
if ($item_list) {
?>
<table class="datatable">
<thead>
<tr>
<th <?php echo($sort_by == 'name' ? 'class="sort_'.$sort_order.'"' : ''); ?>>
<?php
echo anchor("item/item_list/name/" .
(($sort_order == 'ASC' && $sort_by == 'name') ? 'DESC' : 'ASC'), 'Name');
?>
</th>
<th <?php echo($sort_by == 'desc' ? 'class="sort_'.$sort_order.'"' : ''); ?>>
<?php
echo anchor("item/item_list/desc/" .
(($sort_order == 'ASC' && $sort_by == 'desc') ? 'DESC' : 'ASC'), 'Description');
?>
</th>
<th <?php echo($sort_by == 'price' ? 'class="sort_'.$sort_order.'"' : ''); ?>>
<?php
echo anchor("item/item_list/price/" .
(($sort_order == 'ASC' && $sort_by == '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->name; ?>
</td>
<td>
<?php echo $item->desc; ?>
</td>
<td>
<?php echo $item->price; ?>
</td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php
} else {
echo '<div style="color:red;"><p>No Record Found!</p></div>';
}
?>
</div>
</div>
<!-- FOOTER: DEBUG INFO -->
<footer>
<div class="environment">
<p>Page rendered in {elapsed_time} seconds</p>
<p>Environment: <?= ENVIRONMENT ?></p>
</div>
</footer>
</body>
</html>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('Item');.
Search for the line $routes->get('/', 'Home::index'); and replace it by your controller name, for this example, $routes->get('/', 'Item::index');.
These route configurations are done on the file app/Config/Routes.php.
Style and Image
I have applied some basic styles to the HTML table with sort order as well. I have also put background image for table header. Anything you put under public folder will be accessible directly.
So I am going to put the table.css file under public/css folder and the header image head.gif under public/img folder.
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/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: "▼";
}Testing 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. Execute the following command on your project root directory to run your application.
php spark serveYour application will be running on localhost and port 8080.
Home Page
Accessing URL http://localhost:8080/ will redirect you to http://localhost:8080/index.php/item/item_list and you will see the following page.
Now if you want to sort by Description then you can click on Description column header.
That’s all about sorting table data in Codeigniter 4.