Learn Datatable with Codeigniter : The Ultimate Tutorial Part 1

datatables tutotrial part 1

Every web-based application collects many data from a user whether it is Business Application or any general web-based application. You need to display the data in the tabular format for better readability of that data. The modern application gives many features to the user to manipulate the data in the tabular format. It requires pagination, sorting, searching like many other features for better usability of the web app.

If you have ever tried to implement these features, you know that it is a complete headache. You don’t want to reinvent the wheel every time to build any web-based application. Now the datatable come into the picture, It solves most your problem related to the tabular representation of the data. Datatable provide some of the greatest and powerful features like pagination, searching, sorting, exporting data to various forms and many more out of the box. Datatable provides many powerful API to customize your need.

This tutorial will be divided into two parts. The first part will explain you some basic concepts and in the second part we will talk about some advanced concepts.

You can download the entire project files at the end of part 2.

Features of Datatable

  1. Pagination – Datatables provide out of the box pagination support without any configurations.
  2. Instant search – Provides extensive search capabilities.
  3. Multi-column ordering – Datatables supports the multi-column sorting.
  4. Multiple Data Sources – You can easily display data using datatable from JSON file, DOM, Server-side processing.
  5. Easily theme-able – You can easily customize the look and feel of the datatable using CSS properties.
  6. Wide variety of extensions – Datatables provides many extensions you suite your need.
  7. Mobile friendly – Tables adapt to the viewport size.
  8. Fully internationalisable – Easily translate DataTables into multiple languages.
  9. Free open source software – Datatables comes under the MIT license.

Installation of Datatable

I will be going to use the datatable with Bootstrap 4 CSS framework for better look and feel of the table. Datatables also requires jQuery to work properly. You can visit the Datatables site to get the necessary files or you can use the CDN directly provided by the datatable. When you visit the above link you will have to select what you want to include with Datatable like Bootstrap CSS, Button plugins, jQuery files etc. In the below image you can see what I have selected for this tutorial, After selecting all the necessary files you can find all the CDN at the bottom of the page.

Datatables CDN selection

Datatables CDN selection

Setup the Codeigniter Project.

Now It’s time set up the Codeigniter project. You can either use the Composer to create new Codeigniter project or you can manually download the setup files from their official website.

Setup the database.

I have created a database named fake_db which will have two tables one for users and second for their employment details. Create users tables first,

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `contact` varchar(15) NOT NULL,
  `birthdate` varchar(10) NOT NULL,
  `address` text NOT NULL
)

Employement tables,

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
)

After creating two tables insert more than 10 records so that can see the pagination in action.

Creating Controllers, Models and Views In Codeigniter.

I have created a controller named Main.php in the controllers directory,

Main.php

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

class Main extends CI_Controller {

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

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

Create a model in models directory named User_model.php.

User_model.php

<?php 

defined('BASEPATH') OR exit('No direct script access allowed');
                        
class User_model extends CI_Model {
        
    protected $table = "users";

    public function get(){

        return $this->db->get($this->table)->result();
                                    
    }
                                    
}
                        
/* End of file User.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>


    <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>
                    <tbody>
                        <?php if(!empty($users)): ?>
                            <?php foreach($users as $user): ?>
                            <tr>
                                <th><?= $user->user_id?></th>
                                <th><?= $user->name?></th>
                                <th><?= $user->email?></th>
                                <th><?= $user->contact?></th>
                                <th><?= $user->birthdate?></th>
                                <th><?= $user->address?></th>
                            </tr>
                            <?php endforeach ;?>
                        <?php endif;?>
                    </tbody>
                    
                </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>

Initialize the Datatables.

I will keep all the JS code in the separate file as you can see in main_view.php I have included an external JavaScript file which will hold the Datatables related code.

index.js

$(document).ready(function() {
    $('#my-table').DataTable();

} );

The one the best thing about the Datatables is that you don’t have to configure anything to make Datatables work. In the above code, you can see that I have just initialized the Datatables and the Datatables itself does the heavy lifting after that. Now our tables look like this,

Datatables Default Bootstrap 4 Look

Datatables Default Bootstrap 4 Look

Now you can perform a search based on any column, Datatables by default handles the column sorting, provides the default pagination.

Vertical And Horizontal Scroll In Datatables.

Now our table does not look that good because you have to scroll to the bottom to see the last row and pagination options. Now we can customize this behavior of Datatable to add scrolling to the table only so that we don’t have to scroll down to go to the next page.

$(document).ready(function() {
    $('#my-table').DataTable({
        "scrollY": 300,
        "scrollX": true
    });
} );

As you can see I have passed a JSON object to Datatable method which tells the Datatable to add scrolling to our table. scrollY property specifies that we want to limit the table view-port to 300px vertically so that we don’t have to scroll the entire page and scrollX takes a Boolean value, here true value means add a scrollbar to X-axis dynamically if the width of the table increases, Look at the below image for better understanding,

Datatables Horizontal and Vertical Scroll

Datatables Horizontal and Vertical Scroll

 

Adding Export Buttons To The Datatables.

Sometimes we have provided some additional features with the table like print, export to excel, export to CSV etc. The beautiful things about the Datatables are that It can handle this kind of requirement with ease. You just have to specify which options of features you want to include in the table. Currently, Datatable gives to following export options be the default,

  1. PDF – Export the entire table contents into a .pdf file.
  2. PRINT – Print the entire table contents in a tabular format.
  3. EXCEL – Export the entire table contents into .xlxs file.
  4. CSV – Export the entire table contents into .csv file.
  5. COPY – Let’s you copy the entire table contents into the clipboard.

Let’s code this into our Datatable,

$(document).ready(function() {
    $('#my-table').DataTable({
        dom: 'Bltip',
        buttons: [
            'pdf', 'print', 'excel', 'copy', 'csv', 
       ],
        "scrollY": 300,
        "scrollX": true
    });

} );

Now if you refresh the page, you will not see any changes and buttons won’t be there because here comes the DOM positioning into the picture.

DOM Positioning.

DOM positioning in the Datatable simply means how you want to arrange the different elements on DOM, According to the Datatable official website,

When customising DataTables for your own usage, you might find that the default position of the feature elements (filter input etc) is not quite to your liking. To address this issue DataTables takes inspiration from the CSS 3 Advanced Layout Module and provides the dom initialisation parameter which can be set to indicate where you wish particular features to appear in the DOM. You can also specify div wrapping containers (with an id and / or class) to provide complete layout flexibility. Each HTML control element presented by DataTables is denoted by a single character in the dom option. For example the l option is used for the Length changing input option.

Source: //datatables.net/examples/basic_init/dom.html

The built-in options available are:

  1. l – Length changing.
  2. f – Filtering input.
  3. t – The Table!
  4. i – Information
  5. p – Pagination
  6. r – pRocessing
  7. < and > – div elements
  8. <“#id” and > – div with an id
  9. <“class” and > – div with a class
  10. <“#id.class” and > – div with an id and class

Now let’s use DOM positioning in our code to make our buttons visible

$(document).ready(function() {
    $('#my-table').DataTable({
        dom: '<f<"mb-3"B>ltip>',
        buttons: [
            'pdf', 'print', 'excel', 'copy', 'csv',
       ],
        "scrollY": 300,
        "scrollX": true
    });
} );

Now our Datatable looks like this,

Datatable default export buttons

Datatable default export buttons

Custom Toolbar In Datatable.

As I told you in the beginning that Datatable is highly customizable as you can add your own buttons and links into a custom toolbar widget. We will use the DOM positioning to place our custom toolbar in the DOM. Change the dom  property value of Datatable to this,

dom: '<"toolbar"><f<"mb-3"B>ltip>'

Here I have added a div with the class toolbar which we will use to add some other HTML elements. Now let’s add a drop-down element into the toolbar div.

$(document).ready(function() {
    $('#my-table').DataTable({
        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);

} );

In this way, you can add some custom elements in your DOM. Now our Datatable looks like this,

Datatble Custom Toolbar

Datatble Custom Toolbar

Now you have learned how to use Datatable in your project and how to customize the behavior according to your need. In the second part, you will learn some advanced concepts in Datatable like server-side processing, column rendering etc.

If you face any issue or have any suggestions then write it in the comment box…

Happy Coding……:)

Learn Datatables with Codeigniter : The Ultimate Tutorial Part 2

 

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.

Leave a Reply

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