loading

How to Add Custom Search to DataTables Serverside Processing with PHP

How to Add Custom Search to DataTables Serverside Processing with PHP

How to Add Custom Search to DataTables Serverside Processing with PHP

0 Sales

Free

The DataTables plugin makes it simple to show a dataset in table form on a web page. DataTables makes it simple to add filter, search, and pagination functions to an HTML table. Using DataTables data centre operations, you can dynamically get data from a database and display it in a table with sorting, search, and pagination capability.

In most cases, the DataTables' default search and filter options are employed. The DataTables API, on the other hand, allows you to apply custom search and filter input. In this article, we'll teach you how to use PHP and MySQL to create a custom search and filter to DataTables Server-side Processing.

In the sample code, we will get the members' information from the database and arrange them in DataTables using custom search and filter inputs.

- Using Datatables Server-side Processing, get and summarize information from the MySQL database.

- Datatables may be used to provide sorting and pagination functionality to an HTML table.

- Customized filter and search inputs can be added to Datatables.

 

Create Database Table

 

A table in the database is necessary to store the member's information. In the MySQL database, the given SQL generates a members record with some basic columns.

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 Search and Filter (index.html)

By using DataTables jQuery plugin, the dynamic data will be shown as an HTML table with custom searching and filter options on this web page.

DataTables JS and CSS Library:


Incorporate the jQuery and DataTables libraries.


<link rel="stylesheet" type="text/css" href="DataTables/datatables.min.css"/>


<script src="js/jquery.min.js">script>


<script type="text/javascript" src="DataTables/datatables.min.js">script>

 

HTML Table with Search and Filter Input:


Add an HTML table and add a selector (#memListTable) to this element to connect DataTables.

- To sort records by gender, add a search input box and a filter dropdown.

<div class="post-search-panel">
    <input type="text" id="searchInput" placeholder="Type keywords..." />
    <select id="sortBy">
        <option value="">Sort byoption>
        <option value="Male">Maleoption>
        <option value="Female">Femaleoption>
    select>
div>
<table id="memListTable" class="display" style="width:100%">
    <thead>
        <tr>
            <th>First nameth>
            <th>Last nameth>
            <th>Emailth>
            <th>Genderth>
            <th>Countryth>
            <th>Createdth>
            <th>Statusth>
        tr>
    thead>
    <tfoot>
        <tr>
            <th>First nameth>
            <th>Last nameth>
            <th>Emailth>
            <th>Genderth>
            <th>Countryth>
            <th>Createdth>
            <th>Statusth>
        tr>
    tfoot>
table>

 

Attach DataTables to HTML Table:

 

Using the DataTable() function, initialise the DataTables API class and customise the table object.

- Set the search option to false to disable the default search.

- To allow server-side processing.

     - Set the processing value to true

     - Change the serverSide parameter to true.

     - In the url option of the ajax object, provide the Address of the server-side code (getData.php).

- Use the extend() function to provide custom search and filtering.

     - Enter a name for the custom field and a value for the input.

 

<script>
// Initialize DataTables API object and configure table
var table = $('#memListTable').DataTable({
    "searching": false,
    "processing": true,
    "serverSide": true,
    "ajax": {
       "url": "getData.php",
       "data": function ( d ) {
         return $.extend( {}, d, {
           "search_keywords": $("#searchInput").val().toLowerCase(),
           "filter_option": $("#sortBy").val().toLowerCase()
         } );
       }
     }
});

$(document).ready(function(){
    // Redraw the table
    table.draw();
    
    // Redraw the table based on the custom input
    $('#searchInput,#sortBy').bind("keyup change", function(){
        table.draw();
    });
});
script>

 

 

Server-side Script (getData.php)

 

The server-side functionality with searching and filter is handled by the getData.php code. The SSP class will be used to ease the process of creating SQL queries easier (ssp.class.php).

- The SSP class's basic() function retrieves information from the server based on a built - in search query employing PHP and MySQL.

 

// 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' => ), 
    array( 'db' => 'last_name',  'dt' => ), 
    array( 'db' => 'email',      'dt' => ), 
    array( 'db' => 'gender',     'dt' => ), 
    array( 'db' => 'country',    'dt' => ), 
    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'; 
        } 
    ) 
); 
 
$searchFilter = array(); 
if(!empty($_GET['search_keywords'])){ 
    $searchFilter['search'] = array( 
        'first_name' => $_GET['search_keywords'], 
        'last_name' => $_GET['search_keywords'], 
        'email' => $_GET['search_keywords'], 
        'country' => $_GET['search_keywords'] 
    ); 
} 
if(!empty($_GET['filter_option'])){ 
    $searchFilter['filter'] = array( 
        'gender' => $_GET['filter_option'] 
    ); 
} 
 
// Include SQL query processing class 
require 'ssp.class.php'; 
 
// Output data as json format 
echo json_encode( 
    SSP::simple$_GET$dbDetails$table$primaryKey$columns$searchFilter ) 
);

 

SSP Library

The SSP class is in charge of database operations. It includes certain assistance methods for constructing SQL queries for DataTables server-side processing, including search and filter. The SSP library's code may be found here.

It should be noted that this library and all essential files are contained in the source code and do not need to be downloaded separately.

 

Conclusion

 

This example script demonstrates how to simply add custom search and filter features to the DataTables plugin. You may enable server-side processing DataTables and adding new input to the records that you search, filter, and sort. Furthermore, the HTML table and data list may be readily adjusted to meet your specific requirements. The DataTables API offers a number of configuration options for enhancing the table with server-side data from the database.

 

LICENSE OF USE

You can use it for personal or commercial projects. You can't resell it partially or in this form.

PRODUCT INFO

Create Date : Jan 21, 2022

Updated Date : Jan 23, 2022

Ratings

Comments : 0

Downloads : 0