Skip to main content

jQuery Datatables Server Side Processing with PHP & MySQL

Updated on: coderszine team PHP, Tutorial

In our previous tutorial, we have explained how to implement Implement jQuery Bootgrid with PHP & MySQL. In this tutorial, we will explain how to implement jQuery Datatables with PHP and MySQL.

Datatables is a jQuery grid plugin that convert simple HTML tables into advance data grid with features such as Pagination, instant search, export table data and multi-column ordering etc. The Datatables can be implement easily with both Bootstrap and jQuery UI.

We will cover this tutorial in easy steps with live demo and link to download complete source code of live demo.

Also, read:

So let’s start to develop demo to process jQuery Datatables at server side with PHP and MySQL. The file structure for the example is following.

  • index.php
  • load_data.php
  • datatables.js
Steps1: Create Database Table and Insert Data

As we will display data in Datatables from MySQL database tables, so first we will create table developers using below table create query.

CREATE TABLE `developers` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `skills` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `gender` varchar(255) NOT NULL,
  `designation` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `image` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will also insert some records using below insert statement.

INSERT INTO `developers` (`id`, `name`, `skills`, `address`, `gender`, `designation`, `age`, `image`) VALUES
(1, 'Smith', 'Java', 'Newyork', 'Male', 'Software Engineer', 34, 'image_1.jpg'),
(2, 'David', 'PHP', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(3, 'Rhodes', 'jQuery', 'New Jersy', 'Male', 'Web Developer', 30, 'image_2.jpg'),
(4, 'Sara', 'JavaScript', 'Delhi', 'Female', 'Web Developer', 25, 'image_2.jpg'),
(5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Female', 'Programmer', 35, 'image_2.jpg'),
(6, 'Steve', 'Angular', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(7, 'Cook', 'MySQL', 'Paris', 'Male', 'Web Developer', 26, 'image_2.jpg'),
(8, 'Root', 'HTML', 'Paris', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(9, 'William', 'jQuery', 'Sydney', 'Male', 'Web Developer', 23, 'image_2.jpg'),
(10, 'Nathan', 'PHP', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'),
(11, 'Shri', 'PHP', 'Delhi', 'Male', 'Web Developer', 38, 'image_2.jpg'),
(12, 'Jay', 'PHP', 'Delhi, India', 'Male', 'Web Developer', 30, 'image_3.jpg');
Steps3: Include Bootstrap, jQuery and Datatables files

As we have handled this Datatables exmaple with Bootstrap, so we will include Bootstrap, jQuery and Datatables files.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.12/css/jquery.dataTables.css">  
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script type="text/javascript" charset="utf8" src="//cdn.datatables.net/1.10.12/js/jquery.dataTables.js"></script>
<script type="text/javascript" src="script/datatables.js"></script>
Steps3: Create Table HTML

Now we will create Table HTML in index.php with table id developers to use with Datatables to make Ajax request to render data.

<div class="container">
	<h2>jQuery Datatables Server Side Processing with PHP and MySQL</h2>		
		<table id="developers" class="display" width="100%" cellspacing="0">
        <thead>
            <tr>
                <th>Id</th>
                <th>Name</th>
				<th>Age</th>
				<th>Gender</th>
				<th>Address</th>
				<th>Designation</th>
                <th>Skills</th>               
            </tr>
        </thead>       
    </table>	
</div>
Steps4: Make Ajax Request To Server Side

Now in Datatables.js, we will make Ajax request to server side script load_data.php to get data in JSON format to render in Datatables.

jQuery( document ).ready(function() {
	var table = jQuery('#developers').dataTable({
			 "bProcessing": true,
			 "sAjaxSource": "load_data.php",
			  "bPaginate":true,
			  "sPaginationType":"full_numbers",
			  "iDisplayLength": 5,
			 "aoColumns": [
					{ mData: 'id' } ,
					{ mData: 'name' },
					{ mData: 'age' },
					{ mData: 'gender' },
					{ mData: 'address' },
					{ mData: 'designation' },
					{ mData: 'skills' }
			]
	});   
});
Steps5: Get Data from MySQL Database Table

Now in load_data.php, we will get developers data from table developers and return as JSON.

<?php
$sql_query = "SELECT id, name, gender, skills, address, designation, age FROM developers LIMIT 20";
$resultset = mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));
$developers_record = array();
while( $developer = mysqli_fetch_assoc($resultset) ) {
	$developers_record[] = $developer;
}
$developer_data = array(
	"sEcho" => 1,
"iTotalRecords" => count($developers_record),
"iTotalDisplayRecords" => count($developers_record),
  "aaData"=>$developers_record);

echo json_encode($developer_data);
?>

You may also like:

You can view the live demo from the Demo link and can download the source from the Download link below.
Demo Download

6 thoughts on “jQuery Datatables Server Side Processing with PHP & MySQL”

  3. How to use render to get detail information about each of the table? The link should lead to another page with detail information about that particular row?

  5. I am not getting this, please provide more details about your requirement. thanks!

Comments are closed.