Reading Time: 10 minutes

8,576 Views

Nowadays every web application have their own huge data about application, users, sessions, etc. When we use simple table to handle or to show data just think about the page load issue. It will not render at any cost even if we do any best server configuration.

To handle such type of large data we have multiple options available. In frameworks, we have their own default Behavior and if not we will go for DataTable.

DataTable is the one of the best tool to display data in organized and structured way. This is all we can use by the help of some jquery plugin files. It has several features like of handling data in bulk, sorting, searching, pagination, server side, export buttons etc.

Inside this article we will see complete jQuery DataTable tutorial.

Basic Use of jQuery DataTable ?

Creating a simple file with some dummy data.

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Basic Use of jQuery DataTable</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>

<div class="container">
  <h2>Basic Use of jQuery DataTable</h2>
  <table id="tbl-employees">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Ashish Kumar</td>
                <td>PHP</td>
                <td>Office ABC</td>
                <td>100000</td>
            </tr>
            <tr>
                <td>Vijay Kumar</td>
                <td>Wordpress</td>
                <td>Office PQR</td>
                <td>80000</td>
            </tr>
            <tr>
                <td>Dhananjay Negi</td>
                <td>Node Js</td>
                <td>Office XYZ</td>
                <td>150000</td>
            </tr>
     </tbody>
  </table>
</div>

  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>

  <script>

    $(document).ready(function() {
        $('#tbl-employees').DataTable();
    });
  </script>

</body>
</html>

When we use $(‘#tbl-employees’).DataTable(); by default it supports all features sorting, searching, pagination etc.

Disable Sorting in jQuery DataTable

There are several ways to disable sorting in datatable.

$(document).ready(function() {
  $('#tbl-employees').DataTable({
    "aoColumnDefs": [
      { "bSortable": false, "aTargets": [ 0, 1, 2, 3 ] }
    ]
  });
});

0, 1, 2, 3 These are column indexes. 0 is for Name, 1 is for Position etc. If we apply sorting for a specific column just remove that index value from array.

$(document).ready(function() {
  $('#tbl-employees').DataTable({
    "ordering": false
    });
});

$(document).ready(function() {
  $('#tbl-employees').DataTable({
    "columnDefs": [
      { "orderable": false, "targets": [0,1,2,3] }
    ]
  });
});

$(document).ready(function() {
  $('#tbl-employees').DataTable({ "bSort" : false });
});

Successfully we have seen that there are several options we have to disable sort options in datatable.

Disable Searching in jQuery DataTable

There are several ways to hide searching in datatable. It will completely hide the search box from layout.

$(document).ready(function() {
    $('#tbl-employees').DataTable({
      searching: false
    });
});

$(document).ready(function() {
  $('#tbl-employees').DataTable({
    bFilter: false
    });
});

Method to disable searching for specific column values

$(document).ready(function() {
  $('#tbl-employees').DataTable({
    "aoColumnDefs": [
      { "bSearchable": false, "aTargets": [ 0, 1, 2, 3 ] }
    ]
  });
});

Inside this, search bar will appear but when we search for any value of column with index value 0, 1, 2, 3 it will not work. Because we have done searchable false for all those.

$(document).ready(function() {
  $('#tbl-employees').DataTable({
    columnDefs: [
      { searchable: false, targets: [0, 2, 3] },
    ]
  });
});

Disable searching option for columns with index 0, 2, 3 and it is working for 1 index value.

Set Custom Pagination Length DataTable

By default datatable has 10 rows per page, but in case if we want to show more than that either we can select from the page length dropdown or we can create our own.

$(document).ready(function() {
  $('#tbl-employees').DataTable({
    lengthMenu: [[ 100, 250, -1], [ 100, 250, "All"]], // page length options
  });
});

Hide Page Length jQuery DataTable

To hide the page length dropdown from datatable layout. These are the following methods.

$(document).ready(function() {
  $('#tbl-employees').DataTable({
    "bLengthChange" : false,
  });
});

$(document).ready(function() {
  $('#tbl-employees').DataTable({
    "paging" : false,
  });
});

$(document).ready(function() {
  $('#tbl-employees').DataTable({
    "bPaginate" : false,
  });
});

$(document).ready(function() {
  $('#tbl-employees').DataTable({
    "lengthChange" : false,
  });
});

Fix Scroll Y length jQuery DataTable

If we are displaying suppose 100 of rows per page, then the window scroll size will be bit larger. So to make the things in better way, let’s put the scroll in Y axis of dataTable so that window size don’t have any scroll due to datatable rows.

$(document).ready(function() {
  $('#tbl-employees').DataTable({
    lengthMenu: [[ 100, 250, -1], [ 100, 250, "All"]], // page length options
    scrollY: "400px",
  });
});

Export buttons of jQuery DataTable

Datatable have several advance level features. Export buttons is one of them. We have several options provided to export datatable rows into CSV, Copy to Clipboard, PDF, Excel etc.

We need to download & setup some jquery library files and a CSS file for export buttons.

 # CSS

  <link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.6.4/css/buttons.dataTables.min.css">

 # JS 

  <script src="https://cdn.datatables.net/buttons/1.6.4/js/dataTables.buttons.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
  <script src="https://cdn.datatables.net/buttons/1.6.4/js/buttons.html5.min.js"></script>
    
  # Code
    
  <script>
   $(document).ready(function() {
        $('#tbl-employees').DataTable( {
            dom: 'Bfrtip',
            buttons: [
                'copyHtml5',
                'excelHtml5',
                'csvHtml5',
		        'print',
                'pdfHtml5'
            ]
        } );
    });
  </script>

When we display export buttons, page length dropdown will be automatically gets hidden. To display page length as well we need to add one more option into buttons[] array.

buttons: [
  'copyHtml5',
  'excelHtml5',
  'csvHtml5',
  'pdfHtml5',
  'print',
  'pageLength' // for page length
]

Display Column visibility jQuery DataTable

We have an option to control the visible option to view columns or not.

# JS
<script src="https://cdn.datatables.net/buttons/1.6.4/js/buttons.colVis.min.js"></script>

# Code
buttons: [
  'colvis',
]

# JS
<script src="https://cdn.datatables.net/buttons/1.6.4/js/buttons.colVis.min.js"></script>

# Code
buttons: [
  'columnsToggle',
]

When we export datatable rows it includes all body data. In some cases we want to export the footer as well with sum of numeric values. Let’s see how to export footer as well.

Export Footer to Reports

Here we will see, if we have footer at report then we will export as well in the report as well. In the footer we will show sum of overall column count.

<table id="tbl-employees">
  <table id="tbl-employees">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Ashish Kumar</td>
                <td>PHP</td>
                <td>Office ABC</td>
                <td>100000</td>
            </tr>
            <tr>
                <td>Vijay Kumar</td>
                <td>Wordpress</td>
                <td>Office PQR</td>
                <td>80000</td>
            </tr>
            <tr>
                <td>Dhananjay Negi</td>
                <td>Node Js</td>
                <td>Office XYZ</td>
                <td>150000</td>
            </tr>
     </tbody>
     <tfoot>
        <tr>
          <th></th>
          <th>Total</th>
          <th></th>
          <th></th>
        </tr>
     </tfoot>
</table>
  
# JS CODE
  
$(document).ready(function() {
  $('#tbl-employees').DataTable( {
    dom: 'Bfrtip',
    buttons: [
      'copyHtml5',
      'excelHtml5',
      'csvHtml5',
      'pdfHtml5',
      'pageLength'
    ],
    footerCallback: function(row, data, start, end, display) {
      var api = this.api(),
      data;
      // Remove the formatting to get integer data for summation
      var intVal = function(i) {
        return typeof i == "string"
          ? 1
          : typeof i == "number"
            ? i
            : 0;
      };

      // Total over all pages
      total = api
        .column(3)
        .data()
        .reduce(function(a, b) {
          return intVal(a) + intVal(b);
        }, 0);

      // Total over this page
      pageTotal = api
        .column(3, { page: "current" })
        .data()
        .reduce(function(a, b) {
          return intVal(a) + intVal(b);
        }, 0);

      // Update footer
      $(api.column(3).footer()).html(pageTotal);
    }
  } );
});

We hope this article helped you to learn about complete details of jQuery Complete jQuery DataTable Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.