HTML table Listing, Searching and Sorting Using Codeigniter and Mysql – I

In this Codeigniter tutorial, I will discuss with you, how to fetch records from MySQL database and bind with HTML table with searching, sorting and pagination.We will add some configuration parameters with Codeigniter using config and auto-loader file.

These are very common features of any html table grid listing.You need to provide pagination,sorting and searching on listed records.I will go through following points into this Codeigniter 3 tutorial,

  • We will create database connection with mysql
  • Retrieve data from the database table and display it into a html table
  • Added pagination on HTML table Listing
  • Added sorting on HTML table Listing
  • Added searching on HTML table Listing

Codeigniter is MVC (Model–View–Controller) based PHP framework, So next in your mind What is MVC? MVC is design pattern that help to create separate layer of application.View will for render HTML,The Model is responsible for handling data related operation,Controller will use for handling communication between View and Model.

You can get basic CI configuration information from jQuery datatable Listing CI and template integration using Simple Layout Example in Codeigniter.

Codeigniter 3 Example of Listing, Pagination, Sorting and Searching

We will create controller, model and view file for listing and other features functionality. We will follow following steps to create pagination,sorting and searching on table listing using codeigniter.

Create Database and table

Now create test name database into mysql server and will be creating a table named employee into the 'test' database.

Create Database Connection

Now we have database and table so next step would be create database connection with Codeigniter. We will define database host, database name, database username and database password in a CI config file in codeigniter. The file is located at application/config/database.php.

HTML table listing in CI

We will create table listing using mysql table.We will create controller file that will handle action and render view.We will create model method to get results from mysql database.

Configured model with controller in Codeigniter

We will call model in constructor method in controller file home.php.This method will handle other dependency library as well,

I have added cross origin header in __construct method and added employee model class.

Step 1: Created home.php file in view folder and added HTML table listing View file in this file.

We will make default controller home using routes.php file.

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

Step 2: Created Index controller method to handle listing request.

Step 3: Create model file ’employee_m.php’ and added model method to fetch records from database.

I have used 'id' as parameters that help to get single employee record from database, otherwise we need to create two method one for all employee record and other for single record.

You can read CI searching and sorting features in next HTML table Listing – II tutorial.