Displaying Large Data Sets in a DataTable with Server-Side Processing in PHP

The large data sets can be displayed using server-side processing. Scroller optimizes the display of the data in a scrolling viewport while the server handles the data processing. Server-side processing is necessary to handle the data effectively when working with a big dataset, such as 10,000,000 rows. Here we discuss fetching a large amount of data from the MySql Database table and displaying it in a Database without any loading.

Before we start, we need to include the CSS and JS files for displaying Datatables.

HTML Code

<table id="example" class="display">
        <thead>
            <tr>
                <th>ID</th>
                <th>First name</th>
                <th>Last name</th>
            </tr>
        </thead>
    </table>

JS Code

<script>

$(document).ready(function() {

$('#example).DataTable({

     'processing': true,

     'serverSide': true,

     'ajax': 'path/to/fetch.php',

});

});

</script>

On your HTML page, initialize the DataTable using JavaScript.

Update the `ajax` URL with the path to your PHP script(fetch.php).

Fetch.php

Include the necessary files and dependencies(https://github.com/DataTables/DataTablesSrc/blob/master/examples/server_side/scripts/ssp.class.php): require ‘path/to/ssp.class.php’;
Define your database connection details:

$dbHost = 'localhost';
$dbUser = 'your_username';
$dbPass = 'your_password';
$dbName = 'your_database';
$conn = mysqli_connect($server, $username, $password, $database);

if (!$conn) {
	die('Connection failed: ' . mysqli_connect_error());
}

Define your database table name and set the primary key.

$table = 'your_table_name';
$primaryKey = 'id';

Create an array of database table columns:

$columns = array(
	array('db' => 'id', 'dt' => 0),
	array('db' => 'fname', 'dt' => 1),
	array('db' => lname, 'dt' => 2),
	);

Generate the SSP response. The SSP library provides functions like ssp::simple or ssp::complex to handle basic or more complex queries, respectively.echo json_encode(SSP::simple($_GET, $conn, $table, $primaryKey, $columns));

Updated full code:-\

<?php
require 'path/to/ssp.class.php';

// Database connection
$server = 'localhost';
$username = 'your_username';
$password = 'your_password';
$database = 'your_database';
$conn = mysqli_connect($server, $username, $password, $database);


if (!$conn)
 {
	die('Connection failed: ' . mysqli_connect_error());
}

// SSP configuration
$table = 'your_table_name';
$primaryKey = 'id';
$columns = array(
	array('db' => 'id', 'dt' => 0),
array('db' => 'fname', 'dt' => 1),
array('db' => lname, 'dt' => 2),

);
// Process the DataTable request
echo json_encode(SSP::simple($_GET, $conn, $table, $primaryKey, $columns));
?>

In the above example replace ‘localhost’, ‘your_username’, ‘your_password’, and ‘your_database’ with your actual MySQL database connection details. And also update the $table with your own table and adjust the $columns array.

We have to add the additional features to the datatable such as “paging”: true, “lengthChange”: true, “scroller”: true, “deferRender”: true, “order”: [[0, ‘desc’]], “cache”: false and also adding the export properties.