Skip to content

All topics  /  PHP

How to Add Custom Filter in DataTable using Ajax in PHP?

PHP ·

Teams applying “How to Add Custom Filter in DataTable using Ajax in PHP?” in a production project can use this operations example to record implementation, review and debugging time, then compare that effort with tests and shipped functionality instead of treating activity as performance by itself.

Before copying the example into a live application, confirm version-specific behaviour with the PHP project and test it against the project’s actual database and runtime.

Hi friends,

I am going to explain you How to add Custom Filter in DataTable using Ajax in PHP?. You will learn how to add custom filter in datatable – ajax and php.

This article will give you How to filter data table column using custom filer - php. We will see how to add custom filter in datatable.

I will give you simple Example of How to add custom filter in DataTable Ajax and PHP?.

So, let's see bellow solution:

connection.php


<?php
    $connect = new PDO("mysql:host=localhost;dbname=aatman", "root", "root");
?>

index.php 

<?php

    
    include('connection.php');

    
    $country = '';

    
    $query = "SELECT DISTINCT country FROM students ORDER BY country ASC";
    $statement = $connect->prepare($query);
    $statement->execute();
    $result = $statement->fetchAll();

    
    foreach($result as $row){
       $country .= '<option value="'.$row['country'].'">'.$row['country'].'</option>';
    }
?>
<html>
<head>
    <title>How to Add Custom Filter in DataTable using Ajax in PHP?</title>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.11.5/css/dataTables.bootstrap5.min.css" />
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
    <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js" integrity="sha512-894YE6QWD5I59HgZOGReFYm4dnWc1Qt5NtvYSaNcOP+u1T9qYdvdihz0PPSiiqn/+/3e7Jo4EaG7TubfWGUrMQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
    <script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.11.5/js/dataTables.bootstrap5.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.min.js" integrity="sha384-cVKIPhGWiC2Al4u+LWgxfKTRIcfu0JTxR+EQDz/bgldoEyl4H0zUF0QKbrJ0EcQF" crossorigin="anonymous"></script>  

  
</head>
<body>
    <div class="container mt-4">
        <div class="card">
            <div class="card-header">
                <h1 class="text-center">How to Add Custom Filter in DataTable using Ajax in PHP? - Nicesnippets.com</h1>
            </div>
            <div class="card-body">
                <div class="row d-flex justify-content-center mb-4">
                    <div class="col-md-4">
                        <select name="filter_gender" id="filter_gender" class="form-control" required>
                            <option value="">Select Gender</option>
                            <option value="Male">Male</option>
                            <option value="Female">Female</option>
                        </select>
                    </div>
                    <div class="col-md-4">
                        <select name="filter_country" id="filter_country" class="form-control" required>
                            <option value="">Select Country</option>
                            <?php echo $country; ?>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <button type="button" name="filter" id="filter" class="btn btn-success"><i class="fa fa-search" aria-hidden="true"></i> Search</button>
                    </div>
                </div>
                <table id="customer_data" class="table table-bordered table-striped mt-4">
                    <thead>
                        <tr>
                            <th width="20%">Customer Name</th>
                            <th width="10%">Gender</th>
                            <th width="25%">Address</th>
                            <th width="15%">City</th>
                            <th width="15%">Postal Code</th>
                            <th width="15%">Country</th>
                        </tr>
                    </thead>
                </table>
            </div>
        </div>
    </div>
</body>
</html>
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
    fill_datatable();
    function fill_datatable(filter_gender = '', filter_country = ''){
        var dataTable = $('#customer_data').DataTable({
            "processing" : true,
            "serverSide" : true,
            "order" : [],
            "searching" : false,
            "ajax" : {
                url:"fetch.php",
                type:"POST",
                data:{
                    filter_gender:filter_gender, filter_country:filter_country
                }
            }
        });
    }
    $('#filter').click(function(){
        var filter_gender = $('#filter_gender').val();
        var filter_country = $('#filter_country').val();

        
        if(filter_gender != '' && filter_country != ''){
            $('#customer_data').DataTable().destroy();
            fill_datatable(filter_gender, filter_country);
        }else{
            alert('Select Both filter option');
            $('#customer_data').DataTable().destroy();
            fill_datatable();
        }
    });
});
</script>

fetch.php 

<?php
    include('connection.php');
    $column = array('first_name', 'gender', 'address', 'city', 'postalcode', 'country');
    $query = "SELECT * FROM students ";
    if(isset($_POST['filter_gender'], $_POST['filter_country']) && $_POST['filter_gender'] != '' && $_POST['filter_country'] != '')
    {
       $query .= 'WHERE gender = "'.$_POST['filter_gender'].'" AND country = "'.$_POST['filter_country'].'" ';
    }
    if(isset($_POST['order']))
    {
       $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
    }else {
       $query .= 'ORDER BY id DESC ';
    }
    $query1 = '';
    if($_POST["length"] != -1)
    {
       $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
    }
    $statement = $connect->prepare($query);
    $statement->execute();
    $number_filter_row = $statement->rowCount();
    $statement = $connect->prepare($query . $query1);
    $statement->execute();
    $result = $statement->fetchAll();
    $data = array();
    foreach($result as $row)
    {
        $sub_array = array();
        $sub_array[] = $row['first_name'];
        $sub_array[] = $row['gender'];
        $sub_array[] = $row['address'];
        $sub_array[] = $row['city'];
        $sub_array[] = $row['postalcode'];
        $sub_array[] = $row['country'];
        $data[] = $sub_array;
    }
    function count_all_data($connect)
    {
        $query = "SELECT * FROM students";
        $statement = $connect->prepare($query);
        $statement->execute();
        return $statement->rowCount();
    }
    $output = array(
        "draw"       =>  intval($_POST["draw"]),
        "recordsTotal"   =>  count_all_data($connect),
        "recordsFiltered"  =>  $number_filter_row,
        "data"       =>  $data
    );
    echo json_encode($output);
?>

Output:

I hope it will help you.....

More on this topic