Learn Datatables with Codeigniter : The Ultimate Tutorial Part 2

In this tutorial, we will start learning some of the advanced concepts in the Datatables like server-side processing. This tutorial will be in continuation of part 1 if you have not read part 1 then I highly recommend you to go through the part 1 to learn some basics of the Datatable

Learn Datatables with Codeigniter : The Ultimate Tutorial Part 1

Server Side Processing in Datatable.

In some cases, your database may deal with thousands of rows and which may grow more further in future if you have good traffic website. Datatable lets you fetch particular rows at a time which are done via requesting the server hence you don’t have to render all the rows at a time to make use of Datatable in your project. Let’s make use of server-side processing in Datatable.

To use the server side processing aspect of Datatable we need to use a static class which will help us to communicate with our server easily. Go to this link to copy the code and save it as ssp.php in the third_party folder of the Codeigniter application.

Get the ssp.php code here

After saving the ssp.class file in the folder, put the following line of code at the top of the class declaration in the main.php

include APPPATH . 'third_party/ssp.php';

First of all the let’s remove the PHP code which renders the data from database from main_view.php

main_view.php

<!doctype html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">

    <!-- Datatables -->
    <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/v/bs4/jq-3.3.1/jszip-2.5.0/dt-1.10.18/b-1.5.4/b-flash-1.5.4/b-html5-1.5.4/b-print-1.5.4/datatables.min.css"/>
 
    <script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js"></script>
    <script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
    <script type="text/javascript" src="//cdn.datatables.net/v/bs4/jq-3.3.1/jszip-2.5.0/dt-1.10.18/b-1.5.4/b-flash-1.5.4/b-html5-1.5.4/b-print-1.5.4/datatables.min.js"></script>

    <link rel="stylesheet" href="<?= base_url('assets/css/')?>style.css">

    <title>Datatables Demo</title>
  </head>
  <body style="background: #c9c3c3">
   
    <div class="container">
        <div class="row justify-content-center">
        
            <div class="col-10 mb-5">
               <div class="card mt-3">
                   <div class="card-body">
                       <h5 class="card-title text-center">Datatables Demo</h5>
                       <table id="my-table" class="table">
                    <thead>
                        <tr>
                            <th>#ID</th>
                            <th style="min-width:150px">Name</th>
                            <th>Email</th>
                            <th style="min-width:120px">Contact</th>
                            <th>Birthdate</th>
                            <th style="min-width:150px">Address</th>
                            
                        </tr>
                    </thead>
                    
                </table>
                   </div>
               </div>
            </div>
        </div>
    </div>


    <!-- Optional JavaScript -->
    <!-- jQuery first, then Popper.js, then Bootstrap JS -->
    <script src="//maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
    <script src="<?= base_url('assets/js/index.js')?>"></script>
  
  </body>
</html>

Now let’s create a method in our Codeigniter controller to handle the AJAX request for Datatables.

Main.php

<?php 
        
defined('BASEPATH') OR exit('No direct script access allowed');

include APPPATH . 'third_party/ssp.php';

class Main extends CI_Controller {

    public function index()
    {
        $this->load->model('User_model');
        
        $data['users'] = $this->User_model->get();

        $this->load->view('main_view', $data);
        
    }

    public function load_users()
    {

        // Table name
        $table = 'users';

        // Primary key of table
        $primaryKey = 'user_id';

        /**
         * the db key here represents the database column name
         * and the dt key represents the HTML rendered column 
         * Datatable will automatically map all the fetched columns
         * from datatbase to HTML table based on this array, so
         * carefully map each datatabase column to HTML table
         * column
         */

        $columns = array(
        array( 'db' => 'user_id',     'dt' => 0 ),
        array( 'db' => 'name',        'dt' => 1 ),
        array( 'db' => 'email',       'dt' => 2 ),
        array( 'db' => 'contact',     'dt' => 3 ),
        array( 'db' => 'birthdate',   'dt' => 4 ),
        array( 'db' => 'address',     'dt' => 5 ),
        
        );


        // Database connection details
        $sql_details = array(
            'user' => 'root',
            'pass' => '',
            'db'   => 'fake_db',
            'host' => 'localhost'
        );

        /**
         * Perform the SQL queries needed for an server-side processing requested,
         * utilising the helper functions of this class, limit(), order() and
         * filter() among others. The returned array is ready to be encoded as JSON
         * in response to an SSP request, or can be modified if needed before
         * sending back to the client.
         *
         *  @param  array $request Data sent to server by DataTables
         *  @param  array|PDO $conn PDO connection resource or connection parameters array
         *  @param  string $table SQL table to query
         *  @param  string $primaryKey Primary key of the table
         *  @param  array $columns Column information array
         *  @return array Server-side processing response array
         */

        echo json_encode(
            SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns)
        );
    }
        
}
        
        

Let’s modify the index.js file to incorporate the server-side processing in the client side,

index.js

$(document).ready(function() {
    $('#my-table').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "//localhost/cidatatable/main/load_users" ,
        dom: '<"toolbar"><f<"mb-3"B>ltip>',
        buttons: [
            'pdf', 'print', 'excel', 'copy', 'csv',
       ],
        "scrollY": 250,
        "scrollX": true
    });


    const dropdown = `<select class="form-control" style="width: 32% !important">
                        <option>Option #1</option>
                        <option>Option #2</option>
                        <option>Option #3</option>
                    </select>`;

    $("div.toolbar").html(dropdown);

} );

Using advanced SQL queries like JOIN, ORDER etc.

You can use the advanced SQL queries with the ssp.class to get the custom result. To use the custom queries with ssp.class, open the file and replace all the `$table` with $table  and save it. This is a kind of a hack we are going to use to execute custom SQL queries with the ssp.class. The way it works is that when we will pass the custom SQL query to the SSP::simple() the query will start behaving like the subquery in SQL then it will start to fetch the data from that subquery.

Lets’ create another table named employment which will store the employment details of all the users we have in our database table users ,

CREATE TABLE `employment` (
  `emp_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `job_title` varchar(50) NOT NULL,
  `salary` int(12) NOT NULL
)

Insert all the employment details of the users you have created in the users table, So that will fetch job title and salary details from the employment table of users using the SQL JOIN query.

First of all, let’s add two more columns to our HTML table in the main_view.php.

<thead>
   <tr>
      <th>#ID</th>
      <th style="min-width:150px">Name</th>
      <th>Email</th>
      <th style="min-width:120px">Contact</th>
      <th>Birthdate</th>
      <th style="min-width:150px">Address</th>
      <th>Job Title</th>
      <th>Salary</th>
   </tr>
</thead>

Now modify the load_users()  method in the controller to use the custom SQL query.

public function load_users()
{
        $table = "
        (
            SELECT
                users.*,
                employment.job_title,
                employment.salary
            FROM
                users
            INNER JOIN employment WHERE users.user_id = employment.user_id
        )temp";
        
        // Primary key of table
        $primaryKey = 'user_id';

        
        $columns = array(
        array( 'db' => 'user_id',     'dt' => 0 ),
        array( 'db' => 'name',        'dt' => 1 ),
        array( 'db' => 'email',       'dt' => 2 ),
        array( 'db' => 'contact',     'dt' => 3 ),
        array( 'db' => 'birthdate',   'dt' => 4 ),
        array( 'db' => 'address',     'dt' => 5 ),
        array( 'db' => 'job_title',   'dt' => 6 ),
        array( 'db' => 'salary',      'dt' => 7 ),
        );

        $sql_details = array(
            'user' => 'root',
            'pass' => '',
            'db'   => 'fake_db',
            'host' => 'localhost'
        );

        echo json_encode(
            SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns)
        );
}

Customize the columns in Datatable.

If you want to customize some columns values based on some logic you can do that in Datatable very easily. Datatable provides a property call columnDefs whichs takes an array of JSON objects to specify how to modify a column in runtime.

You can even generate new columns for different buttons like delete, edit, view etc which comes very handy while developing any project which can have CRUD operations. Let’s add a delete button to delete any records from our database and We will also modify our salary column to prepend a $ sign with it.

index.js

$(document).ready(function() {
    $('#my-table').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "//localhost/cidatatable/main/load_users" ,
        dom: '<"toolbar"><f<"mb-3"B>ltip>',
        buttons: [
            'pdf', 'print', 'excel', 'copy', 'csv',
       ],
       columnDefs: [
           {
               targets: [-1],
               data: null,
               render: function ( data, type, row, meta ) {
                return `<a href='//localhost/cidatatable/main/delete/${row[0]}' class='btn btn-sm btn-danger'>X</a>`
               }
           },
           {
            targets: [7],
            render: function ( data, type, row, meta ) {
             return "$" + row[7]; // to access the data of column 7
            }
        },
           
        
       ],
        "scrollY": 250,
        "scrollX": true
    });


    const dropdown = `<select class="form-control" style="width: 32% !important">
                        <option>Option #1</option>
                        <option>Option #2</option>
                        <option>Option #3</option>
                    </select>`;

    $("div.toolbar").html(dropdown);

} );

Here is the explanation,

  • targets – Specify which array of columns we want to target to modify. In the first object, we have passed -1 which means we want to target the last column. You can pass multiple columns like this, [1, 2, 3]
  • data – The data to pass to render function which can be used to modify the column value. In the first object, we have passed null to data as we do not want to insert any data instead we want to create a new column.
  • render: function ( data, type, row, meta ) –render key takes a function which will be executed each time a row is rendered in the DOM. Parameters of functions,
    data – The data for the cell which we need to render.
    type – The type call data requested. This is used for DataTables’ orthogonal data support
    row – The full data source for the row.
    meta –  An object that contains additional information about the cell being requested.

Now you can modify any columns at runtime however you want to in Datatable. I am not going to implement the delete method in the controller that is left for an exercise for you. You can find full source code below.

Main.php

<?php 
        
defined('BASEPATH') OR exit('No direct script access allowed');

include APPPATH . 'third_party/ssp.php';

class Main extends CI_Controller {

    public function index()
    {
        $this->load->model('User_model');
        
        $data['users'] = $this->User_model->get();

        $this->load->view('main_view', $data);
        
    }

    public function load_users()
    {
        $table = "
        (
            SELECT
                users.*,
                employment.job_title,
                employment.salary
            FROM
                users
            INNER JOIN employment WHERE users.user_id = employment.user_id
        )temp";
        
        // Primary key of table
        $primaryKey = 'user_id';

        
        $columns = array(
        array( 'db' => 'user_id',     'dt' => 0 ),
        array( 'db' => 'name',        'dt' => 1 ),
        array( 'db' => 'email',       'dt' => 2 ),
        array( 'db' => 'contact',     'dt' => 3 ),
        array( 'db' => 'birthdate',   'dt' => 4 ),
        array( 'db' => 'address',     'dt' => 5 ),
        array( 'db' => 'job_title',   'dt' => 6 ),
        array( 'db' => 'salary',      'dt' => 7 ),
        );

        $sql_details = array(
            'user' => 'root',
            'pass' => '',
            'db'   => 'fake_db',
            'host' => 'localhost'
        );

        echo json_encode(
            SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns)
        );
    }
    
}
        
        

main_view.php

<!doctype html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">

    <!-- Datatables -->
    <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/v/bs4/jq-3.3.1/jszip-2.5.0/dt-1.10.18/b-1.5.4/b-flash-1.5.4/b-html5-1.5.4/b-print-1.5.4/datatables.min.css"/>
 
    <script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.min.js"></script>
    <script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
    <script type="text/javascript" src="//cdn.datatables.net/v/bs4/jq-3.3.1/jszip-2.5.0/dt-1.10.18/b-1.5.4/b-flash-1.5.4/b-html5-1.5.4/b-print-1.5.4/datatables.min.js"></script>

    <link rel="stylesheet" href="<?= base_url('assets/css/')?>style.css">

    <title>Datatables Demo</title>
  </head>
  <body style="background: #c9c3c3">
   
    <div class="container">
        <div class="row justify-content-center">
        
            <div class="col-10 mb-5">
               <div class="card mt-3">
                   <div class="card-body">
                       <h5 class="card-title text-center">Datatables Demo</h5>
                       <table id="my-table" class="table">
                    <thead>
                        <tr>
                            <th>#ID</th>
                            <th style="min-width:150px">Name</th>
                            <th>Email</th>
                            <th style="min-width:120px">Contact</th>
                            <th>Birthdate</th>
                            <th style="min-width:150px">Address</th>
                            <th>Job Title</th>
                            <th>Salary</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                    
                </table>
                   </div>
               </div>
            </div>
        </div>
    </div>


    <!-- Optional JavaScript -->
    <!-- jQuery first, then Popper.js, then Bootstrap JS -->
    <script src="//maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
    <script src="<?= base_url('assets/js/index.js')?>"></script>
  
  </body>
</html>

index.js

$(document).ready(function() {
    $('#my-table').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "//localhost/cidatatable/main/load_users" ,
        dom: '<"toolbar"><f<"mb-3"B>ltip>',
        buttons: [
            'pdf', 'print', 'excel', 'copy', 'csv',
       ],
       columnDefs: [
           {
               targets: [-1],
               data: null,
               render: function ( data, type, row, meta ) {                   
                return `<a href='//localhost/cidatatable/main/delete/${row[0]}' class='btn btn-sm btn-danger'>X</a>`
               }
           },
           {
            targets: [7],
            render: function ( data, type, row, meta ) {
             return "$" + row[7]; // to access the data of the column 7
            }
        },
           
        
       ],
        "scrollY": 250,
        "scrollX": true
    });


    const dropdown = `<select class="form-control" style="width: 32% !important">
                        <option>Option #1</option>
                        <option>Option #2</option>
                        <option>Option #3</option>
                    </select>`;

    $("div.toolbar").html(dropdown);


} );
Datatable final output

Datatable final output

 

Download Complete Project Files With Database

If you face any problems of have any suggestions feel free to write it in the comment box…

Happy Coding…:)

 

Ropali Munshi
Ropali Munshi
Ropali Munshi is fullstack PHP Developer. He is passionate developer who loves to learn and expirement with new programming languages , libraries and frameworks. Nowdays he is more into the JavaScript realm.

8 Comments

  1. loen says:

    Source code project can’t be download. services unavailable.
    please give a mirror download like google drive one drive or other..

    thanks

  2. khoirul umam says:

    Hello Ropali Munshi, thanks for the ultimate tutorial.
    I’m already tested it and everything working well by replacing`$table` with $table to user sub queries,
    but one my question, how to enable searching on joined column,

    for example when I search on salary or job _title, the result not found..

    thanks 😀

    • Ropali Munshi says:

      That is taken care of by the Datatable automatically. Anything you type in the search input Datatable will try to match that field in the subquery only. Still if you face any problem do let me know.

Leave a Reply

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