DataTables is a jQuery library used to show the entire list of records in an HTML table. It combines some features like key search, pagination list, data sorting, filter and etc. In DataTables we can limit the number of records to be presented per page.
DataTables gives both client-side and server-side processing. In this post, we are going to utilize the database results by using DataTables server-side processing. A PHP file used to calls requested columns and returns array of results. This resultant array is encoded to the JSON format which will send the response to the DataTables AJAX script.
DataTable Defining Code
The following code uses to define HTML code for the DataTable. This code has needed CSS and JavaScript library includes a top of the page. The table HTML tag has an id attribute which is used to define and set the table as a DataTable.
|
1 2 3 4 5 |
<!-- DataTable CSS --> <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css"/> <!-- DataTable Script --> <script type="text/javascript" src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script> |
and HTML Table details
|
1 2 3 4 5 6 7 8 9 10 |
<table id="post_list" class="dataTable" width="100%" cellspacing="0"> <thead> <tr> <th>S.No.</th> <th>Post Name</th> <th>Description</th> </tr> </thead> </table> |
DataTable Initialize using jQuery script
The following code shows the jQuery DataTable Initialize by setting the needed DataTable property key features. In this code, the serverSide property is set as true and the server side file path is specified for the ajax property key. These keys are used to set the server side processing for the DataTable.
|
1 2 3 4 5 6 7 8 9 10 11 |
$('#post_list').dataTable({ "bProcessing": true, "serverSide": true, "ajax":{ url :"post_list.php", type: "POST", error: function(){ $("#post_list_processing").css("display","none"); } } }); |
Server-Side Processing script
The following PHP code is provided the data set details as JSON format. Here the data will get from MySql table with help of SELECT with ORDER and LIMIT keys. It contains column name and respective values as an array.
|
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 41 42 43 44 45 46 47 48 49 50 51 52 53 |
<?php require_once("../connections/mysqli_connect.php"); $params = $columns = $totalRecords = $data = array(); $params = $_REQUEST; $columns = array( 0 => 'post_id', 1 => 'post_title', 2 => 'post_desc' ); $where_condition = $sqlTot = $sqlRec = ""; if( !empty($params['search']['value']) ) { $where_condition .= " WHERE "; $where_condition .= " ( post_title LIKE '%".$params['search']['value']."%' "; $where_condition .= " OR post_desc LIKE '%".$params['search']['value']."%' )"; } $sql_query = " SELECT * FROM li_ajax_post_load "; $sqlTot .= $sql_query; $sqlRec .= $sql_query; if(isset($where_condition) && $where_condition != '') { $sqlTot .= $where_condition; $sqlRec .= $where_condition; } $sqlRec .= " ORDER BY ". $columns[$params['order'][0]['column']]." ".$params['order'][0]['dir']." LIMIT ".$params['start']." ,".$params['length']." "; $queryTot = mysqli_query($con, $sqlTot) or die("Database Error:". mysqli_error($con)); $totalRecords = mysqli_num_rows($queryTot); $queryRecords = mysqli_query($con, $sqlRec) or die("Error to Get the Post details."); while( $row = mysqli_fetch_row($queryRecords) ) { $data[] = $row; } $json_data = array( "draw" => intval( $params['draw'] ), "recordsTotal" => intval( $totalRecords ), "recordsFiltered" => intval($totalRecords), "data" => $data ); echo json_encode($json_data); ?> |
