How to add Custom Filter in DataTable – AJAX and PHP

DataTables plugin brings enhanced functionality and interactivity to HTML tables, offering features like pagination, sorting, and searching across all columns. By default, DataTables provides a search control that allows users to find values across the entire table and presents a filter list for easy data filtering.

However, the beauty of DataTables lies in its customizability, empowering developers to create tailor-made search filters based on specific record requirements, such as date filtering or single field search.

In this tutorial, we will delve into the process of adding custom filters to DataTables and utilizing AJAX for seamless data retrieval. Let’s get started and elevate the power of DataTables with personalized filtering capabilities.

How to add Custom Filter in DataTable – AJAX and PHP


Table of Content

  1. Create a Table
  2. Create Database Connection file
  3. Download & Include DataTable library
  4. HTML Structure for DataTable Custom Filter
  5. PHP Code for Fetching DataTable Data and Applying Filters
  6. jQuery Initialization and Custom Filter Handling for DataTables
  7. Demo
  8. Conclusion

1. Create a Table

Create employee table and added some records.

CREATE TABLE `employee` (
    `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `emp_name` varchar(80) NOT NULL, 
    `salary` varchar(20) NOT NULL,
    `gender` varchar(10) NOT NULL,
    `city` varchar(80) NOT NULL,
    `email` varchar(80) NOT NULL
)

2. Create Database Connection file

Create a config.php for the database configuration.

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
  die("Connection failed: " . mysqli_connect_error());
}

3. Download & Include DataTable library

  • Download Datatables from the Official website.
  • Include datatables.min.css and datatables.min.js in <head> section and also include the jQuery Library.
  • You can also use CDN –
<!-- Datatable CSS -->
<link href='https://cdn.datatables.net/1.10.22/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>

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

<!-- Datatable JS -->
<script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>

4. HTML Structure for DataTable Custom Filter

Create two <table> elements:

  1. The first <table> element is used to add a DataTable custom filter element. It includes an input box for name searching and a <select> element for gender filtering.
  2. The second <table> is used to initialize the DataTable, setting up the structure for displaying the employee data with columns for Employee name, Email, Gender, Salary, and City.
<!-- Datatable CSS -->
<link href='https://cdn.datatables.net/1.10.22/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>

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

<!-- Datatable JS -->
<script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>

<!-- HTML -->
<div >
    <!-- DataTable Custom Filter -->
    <table>
        <tr>
            <td>
                <input type='text' id='searchByName' placeholder='Enter name'>
            </td>
            <td>
                <select id='searchByGender'>
                     <option value=''>-- Select Gender--</option>
                     <option value='male'>Male</option>
                     <option value='female'>Female</option>
                </select>
            </td>
        </tr>
    </table>

    <!-- Table -->
    <table id='empTable' class='display dataTable'>
        <thead>
            <tr>
                <th>Employee name</th>
                <th>Email</th>
                <th>Gender</th>
                <th>Salary</th>
                <th>City</th>
            </tr>
        </thead>

    </table>
</div>

5. PHP Code for Fetching DataTable Data and Applying Filters

This PHP code responsible for fetching data from the DataTable and applying various filters to the results.

Create ajaxfile.php file, which handles the AJAX requests from the DataTable on the client-side. Steps in the PHP Code:

Reading DataTables $_POST Values:

Obtain the necessary $_POST values from DataTables, such as draw, start, length, order, search, etc., and store them in variables.

Handling Custom POST Values:

Read additional custom POST values (searchByName and searchByGender) from the custom filter elements in the DataTable.

Preparing the Search Query:

Construct the search query based on the custom filter values.

  • If searchByName is not empty, add a search condition for the emp_name field.
  • If searchByGender is not empty, include a search condition for the gender field.
  • If searchValue (the global search) is not empty, search for the value in the emp_name, email, and city fields.

Counting Total Records:

Execute SQL queries to calculate the total number of records in the employee table, both with and without filtering, and store the counts in variables.

Fetching Records and Building Data Array:

Fetch records from the employee table based on the search query, sorting, and pagination parameters.
Use a loop to populate the $data array with associative arrays containing the key-value pairs corresponding to the columns defined in the dataTable() method in jQuery.

Initializing Response Array:

Create the $response array and assign values for keys such as draw, iTotalRecords, iTotalDisplayRecords, and aaData.

Returning Response in JSON Format:

Encode the $response array into JSON format and send it as the response to the DataTable.

<?php
include 'config.php';

## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value

## Custom Field value
$searchByName = $_POST['searchByName'];
$searchByGender = $_POST['searchByGender'];

## Search 
$searchQuery = " ";
if($searchByName != ''){
      $searchQuery .= " and (emp_name like '%".$searchByName."%' ) ";
}
if($searchByGender != ''){
      $searchQuery .= " and (gender='".$searchByGender."') ";
}
if($searchValue != ''){
      $searchQuery .= " and (emp_name like '%".$searchValue."%' or 
           email like '%".$searchValue."%' or 
           city like'%".$searchValue."%' ) ";
}

## Total number of records without filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];

## Total number of records with filtering
$sel = mysqli_query($con,"select count(*) as allcount from employee WHERE 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];

## Fetch records
$empQuery = "select * from employee WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$empRecords = mysqli_query($con, $empQuery);
$data = array();

while ($row = mysqli_fetch_assoc($empRecords)) {
     $data[] = array(
         "emp_name"=>$row['emp_name'],
         "email"=>$row['email'],
         "gender"=>$row['gender'],
         "salary"=>$row['salary'],
         "city"=>$row['city']
     );
}

## Response
$response = array(
     "draw" => intval($draw),
     "iTotalRecords" => $totalRecords,
     "iTotalDisplayRecords" => $totalRecordwithFilter,
     "aaData" => $data
);

echo json_encode($response);

6. jQuery Initialization and Custom Filter Handling for DataTables

This jQuery code responsible for initializing DataTables on the #empTable element and handling the custom filter functionality.

Steps in the jQuery Code:

Initializing DataTables:

Initialize the DataTable on the #empTable element and assign it to the dataTable variable.

  • Set the options for server-side processing with ‘processing’: true and ‘serverSide’: true.
  • Specify the server request method as POST using ‘serverMethod’: ‘post’.
  • Set the AJAX URL to ajaxfile.php to fetch data from the server.

Custom Filter Handling with AJAX:

  • Utilize the data option in the AJAX settings to read the values of the custom search fields – #searchByGender and #searchByName.
  • Read the values from these fields and append them to the data object, which will be sent as part of the AJAX request.

Defining DataTable Columns:

  • In the columns option, define the names of the fields that will be read from the successful callback response and displayed in the DataTable.

Handling Custom Filter Events:

  • Attach keyup and change events to the #searchByName and #searchByGender elements, respectively.
  • Call the dataTable.draw() method whenever these events trigger to redraw the DataTable and apply the custom filtering.
$(document).ready(function(){
    var dataTable = $('#empTable').DataTable({
         'processing': true,
         'serverSide': true,
         'serverMethod': 'post',
         //'searching': false, // Remove default Search Control
         'ajax': {
              'url':'ajaxfile.php',
              'data': function(data){
                    // Read values
                    var gender = $('#searchByGender').val();
                    var name = $('#searchByName').val();

                    // Append to data
                    data.searchByGender = gender;
                    data.searchByName = name;
              }
         },
         'columns': [
              { data: 'emp_name' }, 
              { data: 'email' },
              { data: 'gender' },
              { data: 'salary' },
              { data: 'city' },
         ]
    });

    $('#searchByName').keyup(function(){
         dataTable.draw();
    });

    $('#searchByGender').change(function(){
         dataTable.draw();
    });
});

7. Demo

View Demo


8. Conclusion

Utilize custom filters to enhance DataTables and improve data presentation. Remove the default search control with 'searching': false if desired. Consider using a single search button for efficiency.

Always call draw() to update the DataTable when events trigger. Create dynamic and user-friendly tables with DataTables to leave a lasting impression on users.

Happy coding!

If you found this tutorial helpful then don't forget to share.

42 thoughts on “How to add Custom Filter in DataTable – AJAX and PHP”

    • Add a new column the table and specify in DataTable columns option e.g. {data: ‘action’},. In the ajaxfile.php specify you action controls in the ‘action’ key in the $data[] Array.

      Reply
  1. Hi thanks for your article, is there any way to filter only rows with empty value for a specific column? Maybe when a checkbox is ticked

    Reply
  2. Hi,

    Nice tutorial. How I can do this if the data is divided in multiple rows in the database?

    Added imgur link with screenshot of the data divided in multiple rows.

    Reply
    • Hi David,
      Are you trying to make the following query –

      SELECT p.ID,
      p.post_title,
      MAX(CASE WHEN pm.meta_key = ‘__wpdm_acf_Contratos_DUNS’ then pm.meta_value ELSE NULL END) as __wpdm_acf_Contratos_DUNS,
      MAX(CASE WHEN pm.meta_key = ‘__wpdm_acf_Contratos_Ejecucion’ then pm.meta_value ELSE NULL END) as __wpdm_acf_Contratos_Ejecucion

      FROM wp_posts p LEFT JOIN wp_postmeta pm ON ( pm.post_id = p.ID)
      WHERE p.post_status=’publish’
      GROUP BY
      p.ID,p.post_title

      Reply
  3. I am very much appreciated for your great tutorial, is it possible to migrate the ‘ Delete Multiple Selected Records’ functions? many thanks

    Reply
  4. Hi guys can you help with Date Range filtering using ,ajax and date picker.

    $(‘#tblPOSOrdersDataList’).DataTable({
    “processing” : true,
    “serverSide” : true,
    “order” : [],
    “ajax” : {
    url: sHostRoot +”/Daaggregate”,
    type:”POST”,
    data:{
    FunctionDate:sPosOrderDateFrom, sPOsOrderDateTo
    }
    ,
    dataType: “json”,
    success: function (data) {
    alert(“shooos -” + data);
    (‘#tblPOSOrdersDataList’).DataTable().fnClearTable();
    $.each(data, function (index, order) {
    alert(“boom” + order.OrderNo);
    var row1 = [order.OrderNo, order.SalesPerson,order.FunctionDate,order.CollectionDate,
    order.ActualCollectionDate,order.CollectedBy,order.ReturnDate,order.ActualReturnDate,
    order.ReturnedBy,order.CompletedDate,order.CompletedBy,order.HireTotal];
    (‘#tblPOSOrdersDataList’).DataTable().fnAddData(row1,true);
    });
    },
    },

    “scrollY”: “290px”,
    // “dom”: ‘rt’,
    //”scrollY”: true,
    “scrollX”: true,
    “autoWidth”: true,
    “select.style”: ‘single’,
    “rowCallback”: function( row, data ) {

    },
    “data”: dataSet,
    pageResize: true,
    “columns”:
    [
    {
    // “title”: “Button”,
    “className”: ‘details-control’,
    “orderable”: false,
    “data”: null,
    “defaultContent”: “+”
    },
    {‘data’: ‘OrderNo’, “className”: “tblFormDetailsHeader”},
    //{‘data’: ‘orderId’, “className”: “tblFormDetailsHeader”},
    //{‘data’: ‘orderName’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘SalesPerson’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘FunctionDate’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘CollectionDate’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘ActualCollectionDate’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘CollectedBy’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘ReturnDate’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘ActualReturnDate’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘ReturnedBy’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘CompletedDate’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘CompletedBy’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘HireTotal’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘RefundableDeposit’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘Discount’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘OrderTotal’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘TotalPaid’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘BalanceDue’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘InvoiceText’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘OrderItems’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘PaymentHistory’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘Customer’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘Status’, “className”: “tblFormDetailsHeader”},
    {‘data’: ‘Created’, “className”: “tblFormDetailsHeader”}
    ]

    });

    Nothing happens.i got the data as Objects,
    But My dataTable does not Populate the data.

    Reply
  5. Thanks for taking the time, it was a breeze to adapt to my Codeigniter 3 development environment. In my environment the task is divide in the parts: 1) Datatables library compatible with CI, 2) Some controller under controllers/api/Datatables/[Object]/Lists that handles the Datatables / Database / Json enconding and 3) The javascript. Here are example for anybody that may be using CI.
    ./controllers/api/DataTables/Credits
    load->library(‘session’);
    $this->load->helper(‘url’);
    $this->load->library(‘Datatables’);
    $this->datatables->setDatabase(‘default’);
    }

    function index($object_name = NULL)
    {

    }

    function Lists()
    {

    // Filters
    $aFilters = array();
    $searchByName = $this->input->post(‘searchByNombreItem’);
    $searchByEstadoCredito = $this->input->post(‘searchByEstadoCredito’);

    if(null !== $searchByName && ” !== $searchByName) {
    array_push($aFilters,
    array(
    ‘fColumn’ => ‘nombre_item’,
    ‘fValue’ => strtolower($searchByName),
    ‘fType’ => ‘where’,
    ‘fEscape’ => TRUE,
    )
    );
    }

    if(null !== $searchByEstadoCredito && ” !== $searchByEstadoCredito) {
    array_push($aFilters,
    array(
    ‘fColumn’ => ‘estado_credito’,
    ‘fValue’ => strtolower($searchByEstadoCredito),
    ‘fType’ => ‘where’,
    ‘fEscape’ => TRUE,
    )
    );
    }

    $aTable = ‘ci_vw_creditos’;
    $aColumns = array(‘DT_RowId’,’id’,’codigo_identificacion’,’nombre_completo’,
    ‘proyecto_id’,’proyecto’,’lote_id’,’lote_codigo’,’factura_item_id’,’nombre_item’,
    ‘terminos_de_pago_id’,’termino_pago’,’estado_credito_id’,’estado_credito’,
    ‘fecha_creacion’,’fecha_cancelacion’,
    ‘monto’,’saldo’,’tasa_interes_anual’,’pago_mensual’,
    );
    $json = $this->datatables->get_json($aTable, $aColumns, $aFilters);
    echo $json;
    }
    }

    ./views/credits/index.php

    — Escoja un concepto —
    Lote
    Servicios Legales
    Servicio Mantenimiento
    Poliza compra terreno

    — Escoja un estado —
    Nuevo
    Activo
    Cancelado
    Congelado
    Incobrable

    Identificación
    Cliente
    Lote
    Concepto
    Estado
    Plazo
    Saldo
    Pago Mensual
    Acciones

    JavaScript (implement through a footer inclusion after loading the view)

    $(document).ready(function() {
    var dtCredits = $(“#credits”).DataTable({
    “ajax”: {
    “url”: ‘api/DataTables/Credits/Lists’,
    “dataSrc”: ‘data’,
    “type”: ‘POST’,
    “data”: function(data){
    var nombre_item = $(“#searchByNombreItem”).val();
    var estado_credito = $(“#searchByEstadoCredito”).val();

    data.searchByNombreItem = nombre_item;
    data.searchByEstadoCredito = estado_credito;
    }
    },
    “columns”: [
    {data: ‘codigo_identificacion’},
    {data: ‘nombre_completo’},
    {data: ‘lote_codigo’},
    {data: ‘nombre_item’},
    {data: ‘estado_credito’},
    {data: ‘termino_pago’},
    {data: ‘saldo’},
    {data: ‘pago_mensual’},
    /* Actions */ { mRender: function(data, type, row) {
    return ”
    +’ ‘
    +’ ‘
    +’ ‘;
    }
    },
    ],
    “columnDefs”: [
    {“targets”: [7,8], “orderable”: false},
    {“targets”: [0,1,2], “searchable”: true},
    {“targets”: [6], className: ‘dt-body-right’},
    {“targets”: [7], className: ‘dt-body-right’},
    {“targets”: [6], render: $.fn.dataTable.render.number(‘,’, ‘.’, 0)},
    {“targets”: [7], render: $.fn.dataTable.render.number(‘,’, ‘.’, 0)}
    ],
    “bJqueryUI”: true,
    “bLengthChange”: false,
    “iDisplayLength”: 10,
    “initComplete”: function(settings, json) {
    $(this).DataTable().buttons().container().appendTo($(“#action_buttons”));
    },
    “language”: {
    “url”: “//cdn.datatables.net/plug-ins/9dcbecd42ad/i18n/Spanish.json”,
    },
    “processing”: true,
    “serverSide”: true,
    “order”: [[0, “asc”]],
    });

    $(“#searchByNombreItem”).change(function(){
    dtCredits.draw();
    });

    $(“#searchByEstadoCredito”).change(function(){
    dtCredits.draw();
    });

    Reply
    • You can do that in an actions column ie:

      add an action column to your columns

      “columns”: [
      { data: “qID” },
      { data: “addedDate” },
      { data: “qty” },
      { data: “item” },
      { data: “description” },
      { data: “cost” },
      { data: “total” },
      { data: “action” }
      ]
      });

      Add actions in your php to the last item, I’ve set the first column as its value.

      $columns = array(
      0 => PREFIX.’quotes.qID’,
      1 => PREFIX.’quotes.addedDate’,
      2 => PREFIX.’quote_items.qty’,
      3 => PREFIX.’quote_items.item’,
      4 => PREFIX.’quote_items.description’,
      5 => PREFIX.’quote_items.cost’,
      6 => PREFIX.’quote_items.total’,
      7 => PREFIX.’quote_items.qID’
      );

      then when your creating your data array add your links to action

      foreach($filterd_results_paginated as $row) {
      $data[] = [
      ‘qID’ => $row->qID,
      ‘addedDate’ => date(‘jS M Y H:i A’, strtotime($row->addedDate)),
      ‘qty’ => $row->qty,
      ‘item’ => $row->item,
      ‘description’ => $row->description,
      ‘cost’ => $row->cost,
      ‘total’ => $row->total,
      ‘action’ => “Edit
      ];
      }

      Reply
  6. Hi,
    Thank you very much for your sharing.

    I want your help on something. I want to make DESC according to customers_id, but I couldn’t. How should I do the code below?

    Code:
    select * from customers
    WHERE 1 “.$searchQuery.” and company_id=”.$company.” and customers_id=”.$customers_id.” order by “.$columnName.” “.$columnSortOrder.”, customers_id DESC limit “.$row.”,”.$rowperpage;

    Reply
    • Hi Jansi,
      The JSON generating for Datatable is invalid. Navigate to the browser network tab and select the datatable AJAX file. It will show if there is any error on the page. Use this for debugging.

      Reply
  7. Thank you very much for your tutorial .

    What should I do if i would like to have a search button instead of live searching? Thank you 🙂

    Reply
  8. Thanks for the article, but i wanna ask you something. Can we make datatabales default SEARCH BOX, search the data that’s already in the table. When we type something in the default SEARCH BOX, it does not trigger server side processing again. Only when we using the Custom Filter. Thanks before, and sorry for my English.

    Reply
  9. Is there a way to implement this when creating the table using external JSON-file data ?
    I’ve a column named ‘category’ which contains multiple values of a data and I want to create a filter based on that column. I would need to use sub-string method to search for the string element in the column I think ?

    Reply
  10. Hi Sir. By the way, I am very much appreciated your great tutorial.
    Can I ask if possible, how to add an update button in the last column?
    Thank you for your response.

    Reply
  11. what about if i have a column that contains (id) for another table?
    i need this tutorial code but mysql table contains column join to another table
    what should i do in this column ?
    please send the same code to me with 2 tables mysql example as soon as possible
    it’s very important to me
    thanks

    Reply

Leave a Comment