Custom search functionality for data tables for server-side processing with PHP and MySQL

Introduction

To add a custom search functionality to Data Tables for server-side processing with PHP and MySQL, you can modify the SQL query dynamically based on the search criteria provided by the user. Here's an example of how you can achieve this:

Note: Please see the following previous article to find out how to create a database and CRUD function: CRUD Operation in PHP using MySQL

How to create a custom search functionality with PHP and MySQL?

01. Create the following PHP file (Ex: kisorjansearch.php)

Step 01. Include the necessary libraries in your PHP file (My file: kisorjansearch.php). Make sure you have the jQuery library and the Data Tables library properly linked. You can use CDN links or download the libraries and include them locally. For example,

<html>
  <head>
    <title> Custom Search Function </Title>
    <H1> Custom Search Function </H1>
  </head>
  <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.2/css/jquery.dataTables.min.css">
  <script src="https://cdn.datatables.net/1.11.2/js/jquery.dataTables.min.js"></script>
</html>

Step 02: Set up the HTML table structure where you want to display the Data Table. Assign an ID to the table, such as "myTable," for easier referencing in JavaScript. Add a search input field for the custom search functionality.

<html>
  <head>
    <title> Custom Search Function </Title>
  </head>
  <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.2/css/jquery.dataTables.min.css">
  <script src="https://cdn.datatables.net/1.11.2/js/jquery.dataTables.min.js"></script>
  <body>
    <br>
    <input type="text" id="customSearch" placeholder="Enter your search query">
    <br>
    <br>
    <table id="myTable" class="display" style="width:100%" data-searching="false" data-info="false" data-paging="false">
      <thead>
        <tr>
          <th>ID</th>
          <th>Name</th>
          <th>Age</th>
          <th>Email</th>
          <!-- Add more table headers as needed -->
        </tr>
      </thead>
    </table>
  </body>
</html>

Step 03: Add JavaScript code to initialize the Data Table and handle the custom search functionality.

<html>
  <head>
    <title> Custom Search Function </Title>
  </head>
  <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.2/css/jquery.dataTables.min.css">
  <script src="https://cdn.datatables.net/1.11.2/js/jquery.dataTables.min.js"></script>
  <body>
    <br>
    <input type="text" id="customSearch" placeholder="Enter your search query">
    <br>
    <br>
    <table id="myTable" class="display" style="width:100%" data-searching="false" data-info="false" data-paging="false">
      <thead>
        <tr>
          <th>ID</th>
          <th>Name</th>
          <th>Age</th>
          <th>Email</th>
          <!-- Add more table headers as needed -->
        </tr>
      </thead>
    </table>
    <script>
      $(document).ready(function() {
        var table = $('#myTable').DataTable({
          "processing": true,
          "serverSide": true,
          "ajax": {
            "url": "search.php",
            "type": "POST",
            "data": function(d) {
              d.customSearch = $('#customSearch').val();
            }
          }
        });
        // Capture custom search input change event
        $('#customSearch').on('input', function() {
          table.draw();
        });
      });
    </script>
  </body>
</html>

02. Create the following PHP file (Ex: search.php)

Create a PHP file named "search.php" that handles the server-side processing and constructs the SQL query based on the custom search input.

<?php
// Establish a database connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "student";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check the connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// Add custom search condition if provided
if (isset($_POST['customSearch'])) {
  $customSearch = $_POST['customSearch'];
  $sql="SELECT *FROM students WHERE name LIKE '%$customSearch%' OR age LIKE '%$customSearch%' OR id LIKE '%$customSearch%' OR email LIKE '%$customSearch%' ";
}

// Execute the SQL query and fetch the results
$result = $conn->query($sql);

// Prepare the response data for DataTables
$response = array(
  "draw" => intval($_POST['draw']),
  "recordsTotal" => 0,
  "recordsFiltered" => 0,
  "data" => array()
);

if ($result->num_rows > 0) {
  $response['recordsTotal'] = $result->num_rows;
  $response['recordsFiltered'] = $result->num_rows;

  while ($row = $result->fetch_assoc()) {
    // Prepare the row data
    $rowData = array(
      $row['id'],
      $row['name'],
      $row['age'],
      $row['email']
      // Add more fields as needed
    );

    $response['data'][] = $rowData;
  }
}

// Close the database connection
$conn->close();

// Return the response data as JSON
echo json_encode($response);
?>

Make sure to replace "your_username", "your_password", "your_database", "your_table", and "column_name" with the appropriate values for your MySQL database. In the PHP file "search.php", the base SQL query is constructed to fetch all records from the specified table. If a custom search query is provided, the SQL query is modified to include the search condition.
The SQL query is executed, and the results are fetched. The response data is prepared in the format expected by Data Tables, including the total records count and the filtered records count. Finally, the response data is returned as JSON to the Data Table, which will handle the display of the table.
Ensure that you have the necessary CSS styling and server-side processing script configured for Data Tables to handle the server-side processing properly.

Conclusion

This example demonstrates the basic implementation of custom search functionality with server-side processing in Data Tables using PHP and MySQL. You can modify and expand upon it to fit your specific table structure, search requirements, and result display. if you have any doubts you can reach me anytime. Happy Learning.