DataTables is a jQuery library that allows developers to show the entire list of records in an HTML table, It combines some features like key search, pagination list, data sorting, filter, etc.
In this tutorial, you will learn how to use jQuery dataTables to server-side processing of data in PHP with jquery, ajax, MySQL and Bootstrap.
DataTables Server Side Processing in PHP with Ajax, MySQL
Here are steps implement dataTables server-side processing with ajax PHP and 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 )
Note that;- The SSP class handles the database related operations. It contains some helper functions to build SQL queries for DataTables server-side processing. You can download from this url :- https://github.com/tutsmake/Datatables-Server-side-Processing/blob/main/ssp.class.php.
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.
Recommended PHP Tutorials
If you have any questions or thoughts to share, use the comment form below to reach us.
