Server Side Datatable with Sorting,Searching and Pagination Using CodeIgniter – I

This tutorial help to create jQuery datatable listing with Sorting, Searching and Pagination using CodeIgniter 3. I am using AJAX for Sorting, Searching and Pagination records from MySQL database. CodeIgniter (CI) is very popular light-weight PHP framework that helps to create a full-fledged web application.

You can read more about CI from Official CodeIgniter site.CodeIgniter (CI) is MVC based PHP framework so that you can easily use this article logic with any PHP MVC based framework like Cakephp, zend etc.

This is the step by step tutorial help to integrate jQuery Datatables with CodeIgniter. We gone through following steps in this CodeIgniter tutorial.

  1. Create Table and database connection with mysql
  2. CI Routing
  3. Datatables listing with Sorting,Searching and Pagination using AJAX

There are following files will participate in this CodeIgniter Tutorial

  • config/routes.php:This file will use to define routes url.
  • controllers/employee.php:This file will contains all action methods.
  • models/employee_m.php:This file will contains all methods that will handle database interaction.
  • views/employee.php:This is HTML view file to define layout of table listing.
  • config/database.php:This file will use to create database connection.

Create Database and Table

We will create database and table in MySQL database server, We will be create 'test' database and 'employee' table inside this database.You need to run below SQL script in MySQL database server query window.

Simple Example of Datatable Listing with MVC based Framework

CodeIgniter-datatable

Step 1: We need to define database host, database name, database username & database password in a database config file in codeigniter. The file is located at application/config/database.php.We need to add following parameters in this file.

Step 2: Added routing url in config/routes.php file to get listing on http://ci_test/employee.

Step 3: Created employee.php file /controllers folder and added below PHP code into this file.

We have get all employee records from model class on instantiation of this controller.We have defined ajax_list() method for ajax callback to get records from model based on datatable listing params like length, start and search string.

Step 4: Created employee_m.php model file into models/ folder and added below code into this file.

We have defined all methods which used for communicate MySQL database with CI to get all records from table, filter records and count records.

Please read Part II – jQuery Datatable with Sorting, Searching and Pagination tutorial for view integration with controller action method.

  • ola

    very useful example ,

    I have Error message “Illegal mix of collations for operation ‘like'”
    when search by Arabic Letter , How this fix this problem
    thanks

    • may be localize file is not there, apart from them you can help form this datatable plug-inhttps://datatables.net/plug-ins/i18n/Arabic

  • fadlireizandi

    Thanks, nice tutorial
    I have a problem with large data (400,000+ records), my application running very slow. What should i do for increase its speed and performance? Thanks

    • Need to load pagination data from server side instead of client side