{"id":2907,"date":"2015-11-11T17:51:51","date_gmt":"2015-11-12T00:51:51","guid":{"rendered":"http:\/\/behstant.com\/blog\/?p=2907"},"modified":"2016-05-02T11:58:15","modified_gmt":"2016-05-02T17:58:15","slug":"jquery-datatables-implementation","status":"publish","type":"post","link":"http:\/\/behstant.com\/blog\/jquery\/jquery-datatables-implementation\/","title":{"rendered":"jQuery Datatables Implementation"},"content":{"rendered":"

\"jQuery<\/a><\/p>\n

The jQuery Plugin Datatable will help you to keep those huge database results into a better and organize table. I have spoke of the jQuery Datatables plugin into this entry<\/a>, so I will no use much time in the introduction of the plugin. I will focus and how you can get you database results into that Datatable.<\/p>\n

For this Example I will be using a database, the employee table\u00a0that I have been using in previous tutorials, so if you don’t remember the file structure of the table I present it again.<\/p>\n

<\/p>\n

This is the structure of the table (if you download<\/a> the code you will get a full explanation of the table):<\/p>\n

--\r\nCREATE TABLE `employee` (\r\n  `employee_id` int(11) NOT NULL AUTO_INCREMENT,\r\n  `name` varchar(85) NOT NULL,\r\n  `email` varchar(45) NOT NULL,\r\n  `telephone` varchar(20) NOT NULL,\r\n  PRIMARY KEY (`employee_id`)\r\n) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8\r\n-- \r\n<\/pre>\n
Once you create the table fill it with the require\u00a0information, I suggest you to add many records so that you can use the Datatable Pagination. As I mentioned before if you download<\/a> the code you will have all the information in a sql script.<\/p>\n
DOWNLOADING JQUERY DATATABLES<\/h3>\n
You can take the code from these two sources:<\/p>\n
1) Download the code from the offcial page<\/a> or<\/p>\n
\"Download<\/a><\/p>\n
2) At the end of the post you can download<\/a> all the code with the Database included. Also all the inserts\u00a0of the table employee are included.<\/p>\n
THE MARKUP<\/h3>\n
We have to setup a place where our information is going to appear so basically we only need to make our table where the data will be display. This is the basic html that we need.<\/p>\n
<table id="resultTable">\r\n \t<thead>\r\n \t\t<tr>\r\n \t\t\t<th>Employee id<\/th>\r\n \t\t\t<th>Name<\/th>\r\n \t\t\t<th>Email<\/th>\r\n \t\t\t<th>Telephone<\/th>\r\n \t\t<\/tr>\r\n \t<\/thead>\r\n \t<tbody>\r\n \t\t<tr>\r\n \t\t\t<td>1<\/td>\r\n \t\t\t<td>Dennis Ritchie<\/td>\r\n \t\t\t<td>dritchie@bell.com<\/td>\r\n \t\t\t<td>555-154-8745<\/td>\r\n \t\t<\/tr>\r\n \t<\/tbody>\r\n <\/table>\r\n\r\n<\/pre>\n
ADDING THE RESOURCES<\/h3>\n
Now that you have setup the code for the table we have\u00a0to\u00a0add the resources previously\u00a0downloaded. To complete\u00a0this, add the following code in your header or before the closing body tag.<\/p>\n
<link rel="stylesheet" href="css\/datatables.min.css"\/>\r\n<script src="js\/jquery-1.11.3.js"><\/script>\r\n<script src="js\/bootstrap.js"><\/script>\r\n<script src="js\/datatables.min.js"><\/script>\r\n<script src="js\/app.js"><\/script>\r\n\r\n<\/pre>\n
Notice that I have added an extra script named app.js<\/span><\/strong>, this is where all our Javascript will be written.<\/p>\n
RUNNING JQUERY DATATABLES.<\/h3>\n
Applying the jQuery datatables plugin is very easy, just like any other jQuery plugin you have to initialize it. Remember that in the HTML table we setup the class id\u00a0resultTable<\/span><\/strong>,<\/span> so we are going to use that as our selector.<\/p>\n
app.js<\/span><\/strong><\/p>\n
\/*\r\n\tScript to handle the Datatables Plugin.\r\n\t@author Reedyseth\r\n\t@since 10-Nov-2015\r\n*\/\r\n$(document).ready(function() {\r\n\t$('#resultTable').DataTable();\r\n});<\/pre>\n
RESULT<\/h3>\n
Until this point with that small portion of code\u00a0you should be able to see the jQuery datatable in action, of course this is where things get interesting and complex and therefore I will make more tutorials to target the must common situation.<\/p>\n
\"Datatables<\/a>
Datatables in Action<\/figcaption><\/figure>\n
MANY RECORDS<\/h3>\n
In the beginning \u00a0of this post I mentioned that using jQuery datatables is for the purpose of controlling many records in our application, if we don’t do that then we will end up with a table with huge scrolling. Add many records into the employee table using you database editor or manually using the MySQL console, on this point section we are not going to write the records into hour html table, instead we are going to retrieve all this information from our database.<\/p>\n
I have created some other tutoriales to fetch information with Ajax so you could take a look at\u00a0Ajax usage to search a Record with PHP, MySQL and jQuery<\/a>\u00a0or this other tutorial\u00a0Find a Record with Ajax and jQuery UI Autocomplete, PHP, MySQL and Json \u2013 Part 1<\/a>.<\/p>\n
This is the code to retrieve the information with jQuery Ajax, the server side code I leave it to you for the implementation or you can purchase the code<\/a> that includes the design that you see on the images as well as all the complete database structure and with all the records.<\/strong><\/p>\n
\/*\r\n\tScript to handle the Datatables Plugin.\r\n\t@author Reedyseth\r\n\t@since 10-Nov-2015\r\n*\/\r\n$(document).ready(function() {\r\n\r\n\t$.ajax({\r\n\t\turl: "php\/retrieveEmployees.php",\r\n\t\tdataType: "json",\r\n\t\tsuccess: function(response){\r\n\t\t\tvar tbody = "";\r\n\t\t\tfor (var i = 0; i < response.length - 1; i++) {\r\n\t\t\t\ttbody += "<tr>" +\r\n\t\t\t\t\t\t\t"<td><a href='#'>"+ response[i].employee_id +"<\/a><\/td>"+\r\n\t\t\t\t\t\t\t"<td>"+ response[i].name +"<\/td>"+\r\n\t\t\t\t\t\t\t"<td>"+ response[i].email +"<\/td>"+\r\n\t\t\t\t\t\t\t"<td>"+ response[i].telephone +"<\/td>"+\r\n\t\t\t\t\t\t "<\/tr>";\r\n\t\t\t};\r\n\r\n\t\t\t$('.resultTable tbody').html( tbody );\r\n\r\n\t\t\t$('#resultTable').DataTable();\r\n\t\t}\r\n\t});\r\n});\r\n\/\/<\/pre>\n
RESULT<\/h3>\n
\"004-datatables-database-ex\"<\/a><\/p>\n
The table above\u00a0has many records and is\u00a0using\u00a0the jQuery Datatables in Action, there are 5 elements that are given by default:<\/p>\n
    \n
  1. Dropdown to select the number of records to be displayed.<\/li>\n
  2. You can search information on the table itselft by using this input area.<\/li>\n
  3. You can sort the records<\/li>\n
  4. A Legend that displays the number of records been displayed as well as the total.<\/li>\n
  5. A Paginator where you can jump to any page that you desire.<\/li>\n<\/ol>\n
    Implementing the plugin Datatables is not a complex thing, customizing the plugin make the things interesting. On future post I will be showing how to add or remove the elements of the Datatables or also adding filters\/custom input areas on the headers of the table so that you can search by column and not globally, also I will be showing who to load information on the Datatables dynamically using Ajax, this is to avoid a heavy request to the server when there are many records on the database.<\/p>\n
    FINAL RAW CODE<\/h3>\n
    <!doctype html>\r\n<html>\r\n    <head>\r\n        <title>jQuery Datatables<\/title>\r\n        <link rel="stylesheet" href="css\/datatables.min.css"\/>\r\n    <\/head>\r\n    <body>\r\n\r\n    \t<table id="resultTable" class="table table-striped resultTable">\r\n\t        <thead>\r\n\t            <tr>\r\n\t                <th>Employee ID<\/th>\r\n\t                <th>Name<\/th>\r\n\t                <th>Email<\/th>\r\n\t                <th>Telephone<\/th>\r\n\t            <\/tr>\r\n\t        <\/thead>\r\n\t        <tbody>\r\n\t        \t<tr>\r\n\t        \t    <td><a href="#">1<\/a><\/td>\r\n\t        \t    <td>Dennis Ritchie<\/td>\r\n\t        \t    <td>dritchie@bell.com<\/td>\r\n\t        \t    <td>555-154-8745<\/td>\r\n\t        \t<\/tr>\r\n\t        \t<tr>\r\n\t        \t    <td><a href="#">2<\/a><\/td>\r\n\t        \t    <td>Ken Thompson<\/td>\r\n\t        \t    <td>kthompson@bell.com<\/td>\r\n\t        \t    <td>555-154-1234<\/td>\r\n\t        \t<\/tr>\r\n\t        <\/tbody>\r\n\t    <\/table>\r\n\t\t<script src="js\/jquery-1.11.3.js"><\/script>\r\n        <script src="js\/datatables.min.js"><\/script>\r\n        <script>\r\n        \t$(document).ready(function() {\r\n        \t\t$('#resultTable').DataTable();\r\n        \t});\r\n        <\/script>\r\n    <\/body>\r\n<\/html><\/pre>\n
    DOWNLOAD THE FULL CODE<\/h1>\n
    You can always purchase the code, this will support the developer and also pay for the hosting and domain services. This is what you get with the purchase:<\/p>\n