Datatable Pagination, Sorting and Search - Server Side (PHP/MySQl) Using Ajax

The Datatable 1.10.9 has been released now, Datatable is most popular grid plugin in web development. The Main benefit of Datatable is open source and light weighted jquery plugin. You can use this plugin with other CSS frameworks like bootstrap and foundation. There are a lot of datatable plugins are available which useful to improve UI experience.

I got huge response from reader on previous datatable tutorial which was DataTables Example – Server-side Processing with PHP.I have used json data in code instead of fetch data from the MySQL database, So I have got many requests to share code with php, MySQL using ajax.

Also Checkout other tutorial of Datatable,

There are following files are participate in this Datatble tutorial:

Index.php - This file will responsible to create html and instance datatable using jquery code.
response.php - This file responsible to create database connection string and convert records into json string and returns data to Ajax method as response.
connection.php - This file responsible to create mysql database connection.

Datatable Example with PHP,MySql server side Using Ajax

We will create simple PHP application to fecth data from server using PHP.The php is using mysql to store and fetch data.

The jQuery datatable help to display records into html page with pagination sorting and searching feature.

How To Create MySQL Database Connection Using mysql_connect

We will Create db_connect.php file into root of the project path, Now we will put the below connection code in this file.

$dbhost = 'localhost';  
$dbuser = 'root';  
$dbpass = "";  
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');  
$dbname = 'test';  
$connection = mysql_select_db($dbname);

$dbhost = 'localhost';

$dbuser = 'root';

$dbpass = "";

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'test';

$connection = mysql_select_db($dbname);

Step 1: we will include jquery datatable and jquery library.

	<script type="text/javascript" src="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.js"></script>

	<script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js"></script>

Step 2: Create HTML layout in your web page (index.php).

<div class="container">
<div class="">
<h1>Data Table</h1>
<div class="">
<table id="employee_grid" class="display" width="100%" cellspacing="0">
<thead>
<tr>
<th>Empid</th>
<th>Name</th>
<th>Salary</th>
<th>Age</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Empid</th>
<th>Name</th>
<th>Salary</th>
<th>Age</th>
</tr>
</tfoot>
</table>
</div>
</div>
</div>

<h1>Data Table</h1>

<thead>

<tr>

<th>Empid</th>

<th>Salary</th>

</tr>

</thead>

<tfoot>

<tr>

<th>Empid</th>

<th>Salary</th>

</tr>

</tfoot>

</table>

</div>

Step 3: Instantiate datatable object on table(index.php).

$( document ).ready(function() {
	$('#employee_grid').DataTable({
		 "bProcessing": true,
         "serverSide": true,
         "ajax":{
            url :"response.php", // json datasource
            type: "post",  // type of method  , by default would be get
            error: function(){  // error handling code
              $("#employee_grid_processing").css("display","none");
            }
          }
        });   
});

$( document ).ready(function() {

$('#employee_grid').DataTable({

"bProcessing": true,

"serverSide": true,

"ajax":{

url :"response.php", // json datasource

type: "post", // type of method , by default would be get

error: function(){ // error handling code

$("#employee_grid_processing").css("display","none");

}

});

Above code – We have applied datatable on employee_grid table.The serverSide have the rest url path which will use to get data from backend server.

Step 4: Include connection file into top of the response.php file.

	//include connection file 
	include_once("db_connect.php");

1 2	//include connection file include_once("db_connect.php");

Step 5: Using parameters to create sql.

$params[‘order’] : This array contains order information of clicked column
$params[‘search’] : This array contains serach value information datatable
$params[‘start’] : start limit of data
$params[‘length’] : end limit of data

$sql = "SELECT * FROM `employee` ORDER BY id  asc LIMIT ".$params['start']." ,".$params['length']." ";

1	$sql = "SELECT * FROM `employee` ORDER BY id asc LIMIT ".$params['start']." ,".$params['length']." ";

Step 6: Send json data to the index.php file.

	$json_data = array(
			"draw"            => intval( $params['draw'] ),   
			"recordsTotal"    => intval( $totalRecords ),  
			"recordsFiltered" => intval($totalRecords),
			"data"            => $data   // total data array
			);

$json_data = array(

"draw" => intval( $params['draw'] ),

"recordsTotal" => intval( $totalRecords ),

"recordsFiltered" => intval($totalRecords),

"data" => $data // total data array

);

You can download source code and Demo from below link.

Live Demo

Download Source Code

Datatable Pagination, Sorting and Search – Server Side (PHP/MySQl) Using Ajax

There are following files are participate in this Datatble tutorial:

Datatable Example with PHP,MySql server side Using Ajax

How To Create MySQL Database Connection Using mysql_connect

Sponsors