jQuery Datatables when initialized on the HTML table it has automatically make certain valuable feature like data pagination, table column sorting, search all table column data from single textbox, display number of records per page functionality without writing of any line of code or script. So, here we have to discuss how can we remove default DataTables search textbox and make custom serach of filter DataTables data using PHP script with Ajax. Because Default Search box is used for search records of all table column and display on Datatables. But in Web Development, Sometimes we want to required custom search filter for get specific column search onlu not whole table column search. For this in this tutorial, We will learn how can we implement custom search filter into jQuery DataTables by using Ajax with PHP script.
jQuery Datatables Customer Search filter with Server side processing help us to get records based on our choices of data filter. For example from the list of data we want to just filter "Male" gender data only. So we have search search in textbox then it will return female data also. For this we have to required custom search filter in Datatables for increase efficiency of exact search of data. We can implement custom search by using HTML Select box or even textbox also for filter jQuery Datatables data.
Database
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_customer`
--
CREATE TABLE `tbl_customer` (
`CustomerID` int(11) NOT NULL,
`CustomerName` varchar(250) NOT NULL,
`Gender` varchar(30) NOT NULL,
`Address` text NOT NULL,
`City` varchar(250) NOT NULL,
`PostalCode` varchar(30) NOT NULL,
`Country` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `tbl_customer`
--
INSERT INTO `tbl_customer` (`CustomerID`, `CustomerName`, `Gender`, `Address`, `City`, `PostalCode`, `Country`) VALUES
(1, 'Maria Anders', 'Female', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),
(2, 'Ana Trujillo', 'Female', 'Avda. de la Construction 2222', 'Mexico D.F.', '5021', 'Mexico'),
(3, 'Antonio Moreno', 'Male', 'Mataderos 2312', 'Mexico D.F.', '5023', 'Mexico'),
(4, 'Thomas Hardy', 'Male', '120 Hanover Sq.', 'London', 'WA1 1DP', 'United Kingdom'),
(5, 'Paula Parente', 'Female', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(6, 'Wolski Zbyszek', 'Male', 'ul. Filtrowa 68', 'Walla', '01-012', 'Poland'),
(7, 'Matti Karttunen', 'Male', 'Keskuskatu 45', 'Helsinki', '21240', 'Finland'),
(8, 'Karl Jablonski', 'Male', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'United States'),
(9, 'Paula Parente', 'Female', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(10, 'John Koskitalo', 'Male', 'Torikatu 38', 'Oulu', '90110', 'Finland'),
(39, 'Ann Devon', 'Female', '35 King George', 'London', 'WX3 6FW', 'United Kingdom'),
(38, 'Janine Labrune', 'Female', '67, rue des Cinquante Otages', 'Nantes', '44000', 'Finland'),
(37, 'Kathryn Segal', 'Female', 'Augsburger Strabe 40', 'Ludenscheid Gevelndorf', '58513', 'Germany'),
(36, 'Elizabeth Brown', 'Female', 'Berkeley Gardens 12 Brewery', 'London', 'WX1 6LT', 'United Kingdom'),
(30, 'Trina Davidson', 'Female', '1049 Lockhart Drive', 'Barrie', 'ON L4M 3B1', 'Canada'),
(31, 'Jeff Putnam', 'Male', 'Industrieweg 56', 'Bouvignies', '7803', 'Belgium'),
(32, 'Joyce Rosenberry', 'Female', 'Norra Esplanaden 56', 'HELSINKI', '380', 'Finland'),
(33, 'Ronald Bowne', 'Male', '2343 Shadowmar Drive', 'New Orleans', '70112', 'United States'),
(34, 'Justin Adams', 'Male', '45, rue de Lille', 'ARMENTIERES', '59280', 'France'),
(35, 'Pedro Afonso', 'Male', 'Av. dos Lusiadas, 23', 'Sao Paulo', '05432-043', 'Brazil'),
(100, 'Kathryn Segal', 'Female', 'Augsburger Strabe 40', 'Ludenscheid Gevelndorf', '58513', 'Germany'),
(101, 'Tonia Sayre', 'Female', '84 Haslemere Road', 'ECHT', 'AB32 2DY', 'United Kingdom'),
(102, 'Loretta Harris', 'Female', 'Avenida Boavista 71', 'SANTO AMARO', '4920-111', 'Portugal'),
(103, 'Sean Wong', 'Male', 'Rua Vito Bovino, 240', 'Sao Paulo-SP', '04677-002', 'Brazil'),
(104, 'Frederick Sears', 'Male', 'ul. Marysiuska 64', 'Warszawa', '04-617', 'Poland'),
(105, 'Tammy Cantrell', 'Female', 'Lukiokatu 34', 'HAMEENLINNA', '13250', 'Finland'),
(106, 'Megan Kennedy', 'Female', '1210 Post Farm Road', 'Norcross', '30071', 'United States'),
(107, 'Maria Whittaker', 'Female', 'Spresstrasse 62', 'Bielefeld Milse', '33729', 'Germany'),
(108, 'Dorothy Parker', 'Female', '32 Lairg Road', 'NEWCHURCH', 'HR5 5DR', 'United Kingdom'),
(109, 'Roger Rudolph', 'Male', 'Avenida Julio Saul Dias 78', 'PENAFIEL', '4560-470', 'Portugal'),
(110, 'Karen Metivier', 'Female', 'Rua Guimaraes Passos, 556', 'Sao Luis-MA', '65025-450', 'Brazil'),
(111, 'Charles Hoover', 'Male', 'Al. Tysiaclecia 98', 'Warszawa', '03-851', 'Poland'),
(112, 'Becky Moss', 'Female', 'Laivurinkatu 6', 'MIKKELI', '50120', 'Finland'),
(113, 'Frank Kidd', 'Male', '2491 Carson Street', 'Cincinnati', 'KY 45202', 'United States'),
(114, 'Donna Wilson', 'Female', 'Hallesches Ufer 69', 'Dettingen', '73265', 'Germany'),
(115, 'Lillian Roberson', 'Female', '36 Iolaire Road', 'NEW BARN', 'DA3 3FT', 'United Kingdom');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
ADD PRIMARY KEY (`CustomerID`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
MODIFY `CustomerID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=116;
database_connection.php
<?php
//database_connection.php
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
?>
index.php
<?php
include('database_connection.php');
$country = '';
$query = "SELECT DISTINCT Country FROM tbl_customer 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>Custom Search in jQuery Datatables using PHP Ajax</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container box">
<h3 align="center">Custom Search in jQuery Datatables using PHP Ajax</h3>
<br />
<div class="row">
<div class="col-md-4"></div>
<div class="col-md-4">
<div class="form-group">
<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="form-group">
<select name="filter_country" id="filter_country" class="form-control" required>
<option value="">Select Country</option>
<?php echo $country; ?>
</select>
</div>
<div class="form-group" align="center">
<button type="button" name="filter" id="filter" class="btn btn-info">Filter</button>
</div>
</div>
<div class="col-md-4"></div>
</div>
<div class="table-responsive">
<table id="customer_data" class="table table-bordered table-striped">
<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>
<br />
<br />
<br />
</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('database_connection.php');
$column = array('CustomerName', 'Gender', 'Address', 'City', 'PostalCode', 'Country');
$query = "
SELECT * FROM tbl_customer
";
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 CustomerID 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['CustomerName'];
$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 tbl_customer";
$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);
?>
