How to Implement DataTables Server Side Processing Using PHP MySQL?

Jan 25, 2023

.

Admin

How to Implement DataTables Server Side Processing Using PHP MySQL?

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