Reading Time: 9 minutes

2,205 Views

Inside this article we will see the concept i.e PHP MySQLi How To add Custom Filter to DataTable Tutorial. Article contains the classified information about adding custom dynamic filters to datatable.

DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, built upon the foundations of progressive enhancement. We will use this jquery plugin file and also will add the concept of data load via server side code.

If you are looking for an article which gives you the concept of server side datatable in PHP MySQLi and also to a custom dynamic dropdown then this article will help you a lot to understand the things in a very clear way.

Learn More –

Let’s get started.

Create Database & Table

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE php_applications;

Inside this database, we need to create a table.

Table we need – employees

CREATE TABLE `employees` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `username` varchar(50)  NOT NULL,
 `name` varchar(50)  NOT NULL,
 `email` varchar(100)  NOT NULL,
 `gender` enum('Male','Female')  NOT NULL,
 `designation` enum('SEO','PHP Developer','Mean Stack Developer','Node Js Developer','Freelancer','CEO','CTO','Wordpress Developer','Full Stack Developer','Designer','Python Developer')  NOT NULL,
 `phone_number` varchar(60)  NOT NULL,
 `complete_address` text  NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When you create this table.

Next,

Download this test data for employees table.

Download Employees Table

After test data insertion, table will look like this –

Application Folder Structure

You need to create a folder structure to develop this application in PHP and MySQLi. Have a look the files inside this application –

Create a folder with name php-mysqli-datatable-with-custom-filter and create these 4 files into it. And also a .sql file which is for test data for employees table.

Database Configuration

Open dbconfig.php file from folder. Add these lines of code into it.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP MySQLi Add Custom Filter To DataTable Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/

// Database configuration
$host   = "localhost";
$dbuser = "admin";
$dbpass = "Admin@123";
$dbname = "php_applications";

// Create database connection
$conn = new mysqli($host, $dbuser, $dbpass, $dbname);

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

Application Programming

Open index.php file and write this following code into it.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP MySQLi How To Add Custom Filter To DataTable Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/

// Include the database configuration file 
require 'dbconfig.php';

$query = "SELECT DISTINCT designation FROM employees ORDER BY designation ASC";
$statement = $conn->prepare($query);
$statement->execute();
$result = $statement->get_result();

$designationData = "";
while ($item = $result->fetch_assoc()) {
    extract($item);
    $designationData .= '<option value="' . $designation . '">' . $designation . '</option>';
}

?>

<html>

<head>
    <title>PHP MySQLi How To Add Custom Filter To DataTable Tutorial</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/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></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/bootstrap@5.0.2/dist/js/bootstrap.min.js"></script>
</head>

<body>
    <div class="container mt-4">
        <div class="card">
            <div class="card-header">
                <h4 class="text-center">PHP MySQLi How To Add Custom Filter To DataTable Tutorial</h4>
            </div>
            <div class="card-body">
                <div class="row d-flex justify-content-center mb-4">
                    <div class="col-md-4">
                        <select name="filter_designation" id="filter_designation" class="form-control" required>
                            <option value="">Select Designation</option>
                            <?php echo $designationData; ?>
                        </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="employees_data" class="table table-bordered table-striped mt-4">
                    <thead>
                        <tr>
                            <th width="20%">Employee Name</th>
                            <th width="10%">Username</th>
                            <th width="25%">Email</th>
                            <th width="15%">Gender</th>
                            <th width="15%">Designation</th>
                        </tr>
                    </thead>
                </table>
            </div>
        </div>
    </div>

    <script type="text/javascript" language="javascript">
        $(document).ready(function() {

            fill_datatable();

            function fill_datatable(filter_designation = '') {

                var dataTable = $('#employees_data').DataTable({
                    "processing": true,
                    "serverSide": true,
                    "order": [],
                    "searching": false,
                    "ajax": {
                        url: "process.php",
                        type: "POST",
                        data: {
                            filter_designation: filter_designation
                        }
                    }
                });
            }

            $('#filter').click(function() {
                var filter_designation = $('#filter_designation').val();

                if (filter_designation != '') {
                    $('#employees_data').DataTable().destroy();
                    fill_datatable(filter_designation);
                }
            });
        });
    </script>
</body>

</html>

Ajax Handler – Upload Files

Open process.php file from application. Write these lines of code into it.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP MySQLi How To Add Custom Filter To DataTable Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/

// Include the database configuration file 
require 'dbconfig.php';

$column = array('name', 'username', 'email', 'gender', 'designation');

$query = "SELECT * FROM employees";

if (isset($_POST['filter_designation']) && $_POST['filter_designation'] != '') {
    $query .= ' WHERE designation = "' . $_POST['filter_designation'] . '"';
}

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"]) {
    $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$statement = $conn->prepare($query . $query1);
$statement->execute();
$result = $statement->get_result();

$data = array();
while ($item = $result->fetch_assoc()) {
    $sub_array = array();
    $sub_array[] = $item['name'];
    $sub_array[] = $item['username'];
    $sub_array[] = $item['email'];
    $sub_array[] = $item['gender'];
    $sub_array[] = $item['designation'];
    $data[] = $sub_array;
}

function count_all_data($conn, $filter = "")
{
    if (!empty($filter)) {
        $query = "SELECT count(*) FROM employees WHERE designation = '" . $filter . "'";
    } else {
        $query = "SELECT count(*) FROM employees";
    }

    $statement = $conn->prepare($query);
    $statement->execute();
    $number_filter_row = $statement->get_result()->fetch_row();
    return $number_filter_row = $number_filter_row[0];
}

$output = array(
    "draw"       =>  intval($_POST["draw"]),
    "recordsTotal"   =>  count_all_data($conn),
    "recordsFiltered"  =>  count_all_data($conn, $_POST['filter_designation']),
    "data"       =>  $data
);

echo json_encode($output);

Application Testing

Now,

URL: http://localhost/php-mysqli-datatable-with-custom-filter/index.php

Server Side DataTable with Table Data

Filter Data by Dropdown Filter value

Download Complete Source Code

Download Source Code

We hope this article helped you to learn PHP MySQLi How To Add Custom Filter To DataTable Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.