Tuesday, 10 May 2016

jQuery DataTables plugin examples

DataTables is a plug-in for the jQuery Javascript library. It adds interaction capabilities to a single HTML table. These capabilities include pagination, instant-search, sorting, row grouping etc. In this tutorial, I share my experiences on this plugin.


Basic usage

DataTables supports 3 basic data sources:
1) DOM (or HTML markup)
2) Ajax (HTML or JSON response)
3) Server-side processing

 Let's say we have the following html table:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
<table id=”test”>
<thead>
<tr>
<th>column1</th>
<th>column2</th>
</tr>
</thead>
<tbody>
<tr>
<td>data11</td>
<td>data12</td>
</tr>
<tr>
<td>data21</td>
<td>data22</td>
</tr>
</tbody>
</table>

In order to add instant search, pagination and column sorting capabilities we first have to include the jquery.min.js, jquery.dataTables.min.js (along with the corresponding css) libraries. Then we use the dataTables plugin by adding a call to the document.ready function:

1
2
3
$(document).ready(function(){
   $(‘#test’).DataTable();
});

Basic options

You can read the full list of options that DataTables supports in the following link https://www.datatables.net/reference/option/. Some of the basic options which I have personally used are listed in the following table:

Option
Type
Description
Usage
searching
boolean
Enables or disables table searching
searching:true
paging
boolean
Enables or disables table paging
paging:false
sorting
boolean
Enables or disables table columns’ sorting
sorting:false
stateSave
boolean
Controls whether the table state (current page, current search term, current sorting) remains constant on page reload
stateSave:true
pageLength
integer
The number of rows for a single table page
pageLength:10
ajax
url
If an ajax source is being used, the corresponding url is specified by this option
ajax:’loadTable.php’
order
2d-array
Specifies the initial sorting order of the table (if the sorting feature is being used)
order:[[0,’asc’]]
dom
string
Define the table control elements position (for example search field on the top, pages control on the bottom etc.)
'<"top"lf>prt <"bottom"pi><"clear">'
columns
array of objects
Specifies options for every table column (for example the data, the type, the css class of the column or if the column is searchable and sortable)
columns:[{type:’date-uk’}]
columnDefs
array of objects
Along with the columns options, it defines options for the table columns.
"columnDefs": [ {
      "targets": 0,
      "searchable": false
    } ]

Full example with PHP and DOM data source


1) The database
Contact(Id, Surname, Firstname, Company, Phone, Mobile, Email)

 2) The php page
“Forgive me for the missing error checking on the part of code that interacts with the database but I want to focus on the DataTables usage.”

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<html>
<head>
<script type="text/javascript" src="jquery.min.js"></script>
<script type="text/javascript" src="jquery.dataTables.min.js"></script>
<link type="text/css" href="jquery.dataTables.min.css"/>
<script type="text/javascript">
$(document).ready(function(){
$('#contacts').DataTable({
                dom: '<"top"lf>prt<"bottom"pi><"clear">',
                order:[[1,'asc']]
});
});
</script>
</head>
<body>
<h2>View contacts</h2>
<table id="contacts">
<thead>
<tr>
   <th>ID</th>
   <th>Surname</th>
   <th>Firstname</th>
   <th>Company</th>
   <th>Mobile</th>
   <th>Email</th>
</tr>
</thead>
<tbody>
<?php
$conn = mysqli_connect("localhost","my_user","my_password","my_db");
$contacts = mysqli_query($conn, "SELECT * FROM Contact;");
while($contact = mysqli_fetch_assoc($contacts)) {
 echo "<tr><td>".$contact['Id']."</td><td>".$contact['Surname']."</td><td>".$contact['Firstname']."</td><td>".$contact['Company']."</td><td>".$contact['Mobile']."</td><td>".$contact['Email']."</td></tr>";
}
?>

Full example with PHP and ajax data source


1) The database
Contact(Id, Surname, Firstname, Company, Phone, Mobile, Email)

2) The php page

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
<html>
<head>
<script type="text/javascript" src="jquery.min.js"></script>
<script type="text/javascript" src="jquery.dataTables.min.js"></script>
<link type="text/css" href="jquery.dataTables.min.css"/>
<script type="text/javascript">
$(document).ready(function(){
$('#contacts').DataTable({
                ajax:'getContacts.php',
                deferRender:true,
                columns:[
                {data:'id'},
                {data:'surname'},
                {data:'firstname'},
                {data:'company'},
                {data:'mobile'},
                {data:'email'}
],
                dom: '<"top"lf>prt<"bottom"pi><"clear">',
                order:[[1,'asc']]
});
});
</script>
</head>
<body>
<h2>View contacts</h2>
<table id="contacts">
<thead>
<tr>
   <th>ID</th>
   <th>Surname</th>
   <th>Firstname</th>
   <th>Company</th>
   <th>Mobile</th>
   <th>Email</th>
</tr>
</thead>
</table>
</body>
</html>

3) The ajax script

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
<?php
$conn = mysqli_connect("localhost","my_user","my_password","my_db");
$results = mysqli_query($conn, "SELECT * FROM Contact;");
$contacts = array(array());
$c = 0;
while($row = mysqli_fetch_assoc($results)) {
   $contacts[$c]['DT_RowId'] = $row['Id'];
   $contacts[$c]['id'] = $row['Id'];
   $contacts[$c]['surname'] = $row['Surname'];
   $contacts[$c]['firstname'] = $row['Firstname'];
   $contacts[$c]['company'] = $row['Company'];
   $contacts[$c]['mobile'] = $row['Mobile'];
   $contacts[$c]['email'] = $row['Email'];
   $c++;
}
?>

Final thoughts

I hope to come back with more examples from the jQuery DataTables plugin. Specifically, my intention is to share my thoughts for server-side processing, column filtering, custom sorting functions (e.g. sort a set of images according to their CSS class), row grouping and speed issues.
Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)