03-Apr-2023
.
Admin
Hi Dev,
This tutorial is focused on how to implement DataTables server-side processing using PHP MySQL. you'll learn how to implement DataTables server-side processing in PHP MySQL. We will use how to implement DataTables server-side processing using PHP and MySQL. if you have a question about how to implement DataTables server-side processing in PHP and MySQL then I will give a simple example with a solution.
Now, let's see the article on how to implement DataTables server-side processing in PHP and MySQL. it's a simple example of how to implement DataTables server-side processing using PHP and MySQL. you can understand the concept of how to implement DataTables server-side processing in PHP MySQL. if you have a question about how to implement DataTables server-side processing using PHP MySQL then I will give a simple example with a solution.
Step 1: Create Database And Table
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
<!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</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
<!-- 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
<?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 that 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 in JSON format
echo json_encode(
SSP::simple( $_GET, $dbDetails, $table, $primaryKey, $columns )
?>
I hope it could help you...
#PHP
