DataTables Server-side Processing with Custom Links and Buttons using PHP

DataTables JavaScript library is a quick and powerful way to build an HTML table with advanced features. It helps to make the HTML table interactive and user-friendly with a data list. We can easily add an HTML table on the web page to list the data in tabular format with search, filter, pagination, and sorting functionality in no time. DataTables server-side processing functionality is used to fetch the data from the database and render it in the HTML table dynamically.

If you want to use DataTables in the data list where CRUD functionality is integrated, Edit and Delete links are required to be added in the column of each row. In this tutorial, we will show you how to add hyperlink or button to each row in DataTables Server-side Processing with PHP and MySQL.

In the example code, we will fetch the records from the database and list them with custom button links in DataTables.

• Fetch and list data from the MySQL database using Datatables Server-side Processing.
• Add a new column (Action) and insert hyperlinks (Edit and Delete) to each row of the HTML table dynamically.
• Disable sorting of a specific column in Datatables where anchor links or buttons are added.

Create Database Table

To store the member’s information a table is required in the database. The following SQL creates a members table with some basic fields in the MySQL database.

CREATE TABLE `members` (
 `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;

Attach DataTables to HTML Table with Custom Links (index.html)

On page load, the DataTables class is initialized and dynamic data is listed in an HTML table with Action column and Add/Edit buttons.

DataTables and jQuery Library:
Include the jQuery and DataTables library files.

<!-- jQuery library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

<!-- DataTables CSS and JS library -->
<link rel="stylesheet" type="text/css" href="DataTables/datatables.min.css"/>
<script type="text/javascript" src="DataTables/datatables.min.js"></script>

HTML Table with Hyperlinks in each row:
Create an HTML table structure and specify the column names in <thead> and <tfoot>.

• Define a selector ID (dataList) in <table> to attach DataTables.
• Specify an additional column named “Action” where the Edit and Delete links/buttons will be added.

<table id="dataList" 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>
            <th>Action</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>
            <th>Action</th>
        </tr>
    </tfoot>
</table>

Attach DataTables to HTML Table:
Initialize the DataTables API class using the DataTable() method and configure the table object.

• To enable server-side processing,
  • Set the processing option to true.
  • Set the serverSide option to true.
• Specify the server-side script URL (getData.php) in the ajax option.
• Since the custom links are placed in the “Action” column, disable sorting for this column (index count is 7).
  • Use columnDefs option to disable sorting of a specific column.
  • Set orderable to false.
  • Specify column index count from left in targets.

<script>
$(document).ready(function(){
    $('#dataList').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "fetchData.php",

        "columnDefs": [
            { "orderable": false, "targets": 7 }
        ]
    });
});    
</script>

Server-side Script (fetchData.php)

The fetchData.php file is used to perform server-side processing with custom links and buttons.

• To make the SQL query building process easier, we will use the SSP class (ssp.class.php).
• The simple() function of the SSP class helps to fetch the member’s data from the database based on the mentioned columns using PHP and MySQL.
• The formatter parameter is used to modify the default format of the data returns for the column.
• We will define anchor tags (<a>) to add hyperlinks to the column of each row with dynamic ID (primary key ID of the DB data).

<?php 
 
// Database connection info 
$dbDetails = array( 
    'host' => 'localhost', 
    'user' => 'root', 
    'pass' => 'root', 
    'db'   => 'codexworld' 
); 
 
// DB table to use 
$table = 'members'; 
 
// 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'; 
        } 
    ), 
    array( 
        'db'        => 'id', 
        'dt'        => 7, 
        'formatter' => function( $d, $row ) { 
            return ' 
                <a href="edit.php?id='.$d.'">Edit</a>&nbsp; 
                <a href="delete.php?id='.$d.'">Delete</a> 
            '; 
        } 
    ) 
); 
 
// Include SQL query processing class 
require 'ssp.class.php'; 
 
// Output data as json format 
echo json_encode( 
    SSP::simple( $_GET, $dbDetails, $table, $primaryKey, $columns ) 
); 
 
?>

SSP Library

The SSP class handles the database-related operations. It contains some helper functions to build SQL queries for DataTables server-side processing with custom links or buttons. You can see the code of the SSP library here.

Note that: This library and all the required files are included in the source code, you don’t need to download them separately.

Datatables Server-side Processing with Custom Search and Filter

Conclusion

This example script is very useful for the data list with CRUD functionality using DataTables. Here, we add Edit and Delete links to the Action column of each row in DataTables, but you can add any type of links or buttons dynamically. The column data format can be customized in the server-side script using PHP. Use the configuration options to enhance the functionality of DataTables Server-side Processing with PHP.