Wednesday 19 September 2018

Add Server-side DataTables Custom Filter using PHP with Ajax



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);

?>

27 comments:

  1. Every good.
    How to do it at laravel?

    ReplyDelete
  2. How to add the search box that we can search columns like the rest of your example ? I need to have select drop down and the search input box within the same app.

    ReplyDelete
  3. I love your tutorial very much, it helps me to learn coding with php and mysql . recently i found a very interesting plug for datatables. it calls YADCF(yet another datatable column filter) http://yadcf-showcase.appspot.com/, could you please make a tutorial for this plug,how to achive it in php server side with advanced filter? such as select2? Appreciated for your great job.

    ReplyDelete
  4. Same question as FAN OF SNOOPY, i would like to have search box also. Please. Thank you

    ReplyDelete
  5. same question can we add search box to it please? and can we make this fetch file generic example, just pass the table and columns to retrieve to make it reusable using class/oops, please?

    ReplyDelete
  6. Replies
    1. can i ask for the code (CI)???

      Delete
    2. How to add the search box that we can search columns like the rest of your example ? I need to have select drop down and the search input box within the same app.

      Delete
  7. How do I put the two functions together?
    1- Date Range Search in Datatables
    https://www.webslesson.info/2017/06/date-range-search-in-datatables-using-php-ajax.html

    and

    2-Datatables Individual column searching using PHP Ajax Jquery
    https://www.webslesson.info/2017/07/datatables-individual-column-searching-using-php-ajax-jquery.html

    ReplyDelete
  8. Very useful, thanks. I hhave one question: is it possible to choose multiple values for Country?

    ReplyDelete
  9. very useful lesson. Thank you!
    Can you place the filter on the left side of the table please.

    ReplyDelete
  10. very useful lesson. Thank you! Can you place the filter on the left side of table please!!!

    ReplyDelete
  11. function fill_datatable(filter_gender = ' ', filter_country = ' ')
    i have this error i am using dreamweaver software.
    i think the reason is this software how to fix it?.
    this my mygamil: Salmaanyare168@gmail.com
    or Saalimyare2012@hotmail.com

    ReplyDelete
  12. Thanks Sir, your tutorial very great, its very easy to understand as well as less coding.

    ReplyDelete
  13. {"draw":0,"recordsTotal":20,"recordsFiltered":null,"data":[]}

    ReplyDelete
  14. DataTables warning: table id=customer_data - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

    ReplyDelete
  15. how if to add column automatic numbering index in table?

    ReplyDelete
  16. Execllent sir i am big fan of u.

    ReplyDelete
  17. sir i possible create video toturials on codeigniter project

    ReplyDelete
  18. sir also create toturial of datatable to fetch data from two table

    ReplyDelete
  19. Cara muito obrigado. Fica com Deus

    ReplyDelete
  20. How can I do this in mysqli

    ReplyDelete
  21. I have a problme with datatable in ajax call is not working

    ReplyDelete