PHP and MySQL DataTables Server-side Processing

PHP and MySQL DataTables Server-side Processing

jQuery datatables ajax + PHP + Mysql; Through this tutorial, you will learn how to implement datatables server side processing using jQuery + ajax + PHP + MySQL + Bootstrap.

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 Server Side Processing AJAX + PHP + MySQL

Follow the following steps and implement dataTables server-side processing with ajax PHP MySQL:

  • Step 1 – Create Database And Table
  • Step 2 – Create List HTML page
  • Step 3 – Include Datatable Libraries
  • Step 4 – Fetch data from Mysql DB using Ajax

Step 1 – Create Database And Table

First of all, open your PHPMyAdmin and execute the following MySQL query to create a database and table; as shown below:

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
 `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `gender` enum('Male','Female') COLLATE utf8_unicode_ci NOT NULL,
 `country` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Step 2 – Create List HTML page

Create index.php file, which is used to display data from MySQL database using datatable js; so add the following code into your index.php file; as shown below:

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>DataTables Server-side Processing using PHP with MySQL + Ajax</title>
<!-- DataTables CSS library -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css"/>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<!-- DataTables JS library -->
<script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
<style type="text/css">
.bs-example{
margin: 20px;
}
</style>
</head>
<body>
<div class="bs-example">
<div class="container">
<div class="row">
<div class="col-md-12">
<div class="page-header clearfix">
<h2 class="pull-left">Users List</h2>
</div>
<table id="usersListTable" class="display" style="width:100%">
<thead>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Email</th>
<th>Gender</th>
<th>Country</th>
<th>Created</th>
<th>Status</th>
</tr>
</thead>
<tfoot>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Email</th>
<th>Gender</th>
<th>Country</th>
<th>Created</th>
<th>Status</th>
</tr>
</tfoot>
</table>
</div>
</div>        
</div>
</div>
</body>
<script>
$(document).ready(function(){
$('#usersListTable').DataTable({
"processing": true,
"serverSide": true,
"ajax": "fetch.php"
});
});
</script>
</html>

Step 3 – Include Datatable Libraries

Include jQuery dataTable js libraries and bootstrap libraries into your index.php file; as shown below:

<!-- DataTables CSS library -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css"/>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<!-- DataTables JS library -->
<script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>

Step 4 – Fetch data from Mysql DB using Ajax

Create fetch.php file; which is used to fetch data from mysql database using ajax and display it into your index.php file; as shown below:

<?php 
// Database connection info 
$dbDetails = array( 
'host' => 'localhost', 
'user' => 'root', 
'pass' => '', 
'db'   => 'demo'
); 
// mysql db table to use 
$table = 'users'; 
// Table's primary key 
$primaryKey = 'id'; 
// Array of database columns which should be read and sent back to DataTables. 
// The `db` parameter represents the column name in the database.  
// The `dt` parameter represents the DataTables column identifier. 
$columns = array( 
array( 'db' => 'first_name', 'dt' => 0 ), 
array( 'db' => 'last_name',  'dt' => 1 ), 
array( 'db' => 'email',      'dt' => 2 ), 
array( 'db' => 'gender',     'dt' => 3 ), 
array( 'db' => 'country',    'dt' => 4 ), 
array( 
'db'        => 'created', 
'dt'        => 5, 
'formatter' => function( $d, $row ) { 
return date( 'jS M Y', strtotime($d)); 
} 
), 
array( 
'db'        => 'status', 
'dt'        => 6, 
'formatter' => function( $d, $row ) { 
return ($d == 1)?'Active':'Inactive'; 
} 
) 
); 
// Include SQL query processing class 
require 'ssp.class.php'; 
// Output data as json format 
echo json_encode( 
SSP::simple( $_GET, $dbDetails, $table, $primaryKey, $columns ) 

Conclusion

jQuery datatables ajax + PHP + Mysql; Through this tutorial, you have learned how to implement datatables server side processing using jQuery + ajax + PHP + MySQL + Bootstrap.

Leave a Reply

Your email address will not be published.