Skip to content

All topics  /  MySQL

How to Implement DataTables Server Side Processing Using PHP MySQL?

MySQL ·

Teams applying “How to Implement DataTables Server Side Processing Using PHP MySQL?” in a production project can use ofccp compliance to record implementation, review and debugging time, then compare that effort with tests and shipped functionality instead of treating activity as performance by itself.

Before copying the example into a live application, confirm version-specific behaviour with the official MySQL website and test it against the project’s actual database and runtime.

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...

More on this topic