Datatables Individual column searching using PHP Ajax Jquery

This is one more post on Jquery Datatables plugin. In this post we have made discussion on filter or searching individual data in Datatable by using PHP script with JQuery Ajax. By using this feature we can search or event you can also say filter specific column data in Datatable. There are many in build functionality are available with this Jquery plugin. But if you want to make this type of advance functionality then you have to write some code at server side for search or filter of specific column data.

We all know Jquery Datatable plugin is widely used for display our web application data on web page in tabular format because it has many rich features are available for ready to use which are very usefull according to needs. So with those feature here we can add one more features we can use with this plugi. Because searching or filtering of data according to particular column then it is also a one type of useful feature for our wep application. If you have large amount of data and from that data you want to filter data according to data of particular column then at that time this type of functionality will reduce your work and increase output of your web application.

For develop this feature we do want to write some many line of code but you have to pass only value of particular column into Ajax Datatable method and by this method we have pass value to server side. So at server side PHP script we have just append query where condition regarding the filter or searching of individual column. So when it will pass any data to server script then it will produce output according to value of that data. But if it has pass blank value then it will send all data from Database. So for develop filter or search data according to particular column data by using PHP script with Ajax JQuery, then you have to follow below source code.

Online Demo

Source Code

index.php


<?php 
$connect = mysqli_connect("localhost", "root", "", "testing1");
$query = "SELECT * FROM category ORDER BY category_name ASC";
$result = mysqli_query($connect, $query);
?>
<html>
 <head>
  <title>Datatables Individual column searching using PHP Ajax Jquery</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">
   <h1 align="center">Datatables Individual column searching using PHP Ajax Jquery</h1>
   <br />
   
   <div class="table-responsive">
    <table id="product_data" class="table table-bordered table-striped">
     <thead>
      <tr>
       <th>Sr. No.</th>
       <th>Product Name</th>
       <th>
        <select name="category" id="category" class="form-control">
         <option value="">Category Search</option>
         <?php 
         while($row = mysqli_fetch_array($result))
         {
          echo '<option value="'.$row["category_id"].'">'.$row["category_name"].'</option>';
         }
         ?>
        </select>
       </th>
       <th>Product Price</th>
      </tr>
     </thead>
    </table>
   </div>
  </div>
 </body>
</html>



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

 function load_data(is_category)
 {
  var dataTable = $('#product_data').DataTable({
   "processing":true,
   "serverSide":true,
   "order":[],
   "ajax":{
    url:"fetch.php",
    type:"POST",
    data:{is_category:is_category}
   },
   "columnDefs":[
    {
     "targets":[2],
     "orderable":false,
    },
   ],
  });
 }

 $(document).on('change', '#category', function(){
  var category = $(this).val();
  $('#product_data').DataTable().destroy();
  if(category != '')
  {
   load_data(category);
  }
  else
  {
   load_data();
  }
 });
});
</script>

fetch.php


<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "testing1");
$column = array("product.id", "product.name", "category.category_name", "product.price");
$query = "
 SELECT * FROM product 
 INNER JOIN category 
 ON category.category_id = product.category 
";
$query .= " WHERE ";
if(isset($_POST["is_category"]))
{
 $query .= "product.category = '".$_POST["is_category"]."' AND ";
}
if(isset($_POST["search"]["value"]))
{
 $query .= '(product.id LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR product.name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR category.category_name LIKE "%'.$_POST["search"]["value"].'%" ';
 $query .= 'OR product.price LIKE "%'.$_POST["search"]["value"].'%") ';
}

if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY product.id DESC ';
}

$query1 = '';

if($_POST["length"] != 1)
{
 $query1 .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));

$result = mysqli_query($connect, $query . $query1);

$data = array();

while($row = mysqli_fetch_array($result))
{
 $sub_array = array();
 $sub_array[] = $row["id"];
 $sub_array[] = $row["name"];
 $sub_array[] = $row["category_name"];
 $sub_array[] = $row["price"];
 $data[] = $sub_array;
}

function get_all_data($connect)
{
 $query = "SELECT * FROM product";
 $result = mysqli_query($connect, $query);
 return mysqli_num_rows($result);
}

$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"  =>  get_all_data($connect),
 "recordsFiltered" => $number_filter_row,
 "data"    => $data
);

echo json_encode($output);

?>

Database


--
-- Database: `testing1`
--

-- --------------------------------------------------------

--
-- Table structure for table `category`
--

CREATE TABLE IF NOT EXISTS `category` (
  `category_id` int(11) NOT NULL,
  `category_name` varchar(250) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `category`
--

INSERT INTO `category` (`category_id`, `category_name`) VALUES
(1, 'Mobiles'),
(2, 'Computers'),
(3, 'Clothing'),
(4, 'Beauty Item'),
(5, 'Sports Item'),
(6, 'Toys Item'),
(7, 'Books'),
(8, 'Entertainment Item');

-- --------------------------------------------------------

--
-- Table structure for table `product`
--

CREATE TABLE IF NOT EXISTS `product` (
  `id` int(11) NOT NULL,
  `category` int(11) NOT NULL,
  `name` varchar(250) NOT NULL,
  `price` double(10,2) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`id`, `category`, `name`, `price`) VALUES
(1, 1, 'Save on BLU Advance 5.5 HD', 74.99),
(2, 2, 'Dell Inspiron 15.6" Gaming Laptop', 860.00),
(3, 3, 'Women''s Slim Sleeveless', 69.00),
(4, 4, 'Andis 1875-Watt Fold-N-Go Ionic Hair Dryer', 17.00),
(5, 5, 'GM Ripple Cricket Grip, Set Of 3', 66.00),
(6, 6, 'Barbie Fashions and Accessories', 12.00),
(7, 7, 'The Ministry of Utmost Happiness', 6.00),
(8, 8, 'The Great Gatsby (3D)', 8.00),
(9, 1, 'iVooMi Me 1+', 49.00),
(10, 2, 'Apple MacBook Air MQD32HN/A 13.3-inch Laptop 2017', 896.00),
(11, 3, 'Balenzia Premium Mercerised Cotton Loafer Socks', 5.00),
(12, 4, 'Organix Mantra Lemon Cold Pressed Essential Oil', 4.50),
(13, 5, 'SpeedArm Cricket Ball Thrower', 15.00),
(14, 6, 'Mattel Bounce Off Game, Multi Color', 10.00),
(15, 7, 'Seven Days With Her Boss', 5.00),
(16, 8, 'Supernatural Season 1-9 DVD', 22.00),
(17, 1, 'InFocus Turbo 5', 189.00),
(18, 2, 'HP 15-bg008AU 15.6-inch Laptop , Jack Black', 350.00),
(19, 3, 'Seven Rocks Men''s V-Neck Cotton Tshirt', 12.00),
(20, 4, 'Exel Elixir Sublime Antioxidant Serum Cream', 55.00),
(21, 5, 'Gray Nicolls Bat Repair Kit', 9.00),
(22, 6, 'Think Fun Rush Hour, Multi Color', 22.00),
(23, 7, 'Pregnancy Notes: Before, During & After', 5.00),
(24, 8, 'Sherlock Season - 4', 15.00),
(25, 1, 'Vivo Y53', 105.00),
(26, 2, 'Dell Inspiron 15-3567 15.6-inch Laptop', 356.00),
(27, 3, 'Fastrack Sport Sunglasses (Black) (P222GR1)', 14.00),
(28, 4, 'Exel Lotion with stabilized Tea Tree Oil', 28.00),
(29, 5, 'Burn Vinyl Hexagonal Dumbbell', 45.00),
(30, 6, 'Cup Cake Surprise Princess', 8.00),
(31, 7, 'Word Power Made Easy', 2.00),
(32, 8, 'Star Wars: The Force Awakens', 5.00),
(33, 1, 'Lenovo Vibe K5 (Gold, VoLTE update)', 65.00),
(34, 2, 'Lenovo 110 -15ACL 15.6-inch Laptop , Black', 225.00),
(35, 3, 'Zacharias Ankle Socks Pack of 12 Pair', 5.00),
(36, 4, 'Exel SUNSCREEN Broad Spectrum UVA & UVB', 26.00),
(37, 5, 'Burn 500124 Inter Lock Mat (Black)', 24.00),
(38, 6, 'Toyshine Devis Boy 9', 10.00),
(39, 7, 'Think and Grow Rich', 2.50),
(40, 8, 'The Jungle Book', 10.00);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `category`
--
ALTER TABLE `category`
  ADD PRIMARY KEY (`category_id`);

--
-- Indexes for table `product`
--
ALTER TABLE `product`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `category`
--
ALTER TABLE `category`
  MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=9;
--
-- AUTO_INCREMENT for table `product`
--
ALTER TABLE `product`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=41;

23 comments:

Unknown13 July 2017 at 07:07
Great tutorials....

Please keep he knowledge sharing platform coming, im learning angular
VS13 July 2017 at 23:09
super share bro
Unknown3 October 2017 at 13:52
After I installed it on my server, I can t see the ::after css style of the rows on the menu.. the little grey rows that sort the columns. Can you help me please?
Unknown12 March 2018 at 05:52
Man..you are great. I love your tutorial.
Unknown27 March 2018 at 07:03
hello code is working perfectly. when i added dropdown box to 2nd column it is not working. can u please solve my problem
laurent28 March 2018 at 03:11
Hello
I would like to put a product in several categories at the same time ex: 1.3.6
and just one explode after.
is it possible and comment?
Unknown15 May 2018 at 12:45
wonderfull !!
is it a open source codes ??, it is free to use
i was wondering if i could use it in one of my projects
ModiseT29 May 2018 at 14:30
nice tutorial.what if i wanna use this filter on product name as well as product price?

I asking because i have created a table employee and columns experience, position, department, contract type. I wanna filter using them.
ModiseT12 June 2018 at 01:34
Can you please make it to use two columns to search?
Unknown19 September 2018 at 23:55
Thank you so much. This taught me alot
Unknown21 September 2018 at 10:47
I can use the same method with your Codeigniter Turorial?:
https://www.webslesson.info/2016/12/datatables-server-side-processing-in-codeigniter-using-ajax.html

I can not make it work (table reload but nothing):
View:
https://i.gyazo.com/0e79c11b752232c7bf33f5dafadfa1e1.png
https://i.gyazo.com/f08f4d5e413187047d3a6a0ce88dcd43.png
Model:
https://i.gyazo.com/cabac7e5b080dfcf8f5823f6f7698c8c.png

Lovecraft Beyond30 September 2018 at 11:09
I had the same issue as the guys before me. If you want to fix it, you simply need to replace load_data(); with:

var dataTable = $('#product_data').DataTable({
"processing":true,
"serverSide":true,
"order":[],
"ajax":{
url:"fetch.php",
type:"POST"
},
"columnDefs":[
{
"targets":[2],
"orderable":false,
},
],
});

lever everything else as it is. This ensures that when you select an option you destroy this table and use the one in the function. Make sure that the table outside the function does not have ,data:{is_category:is_category} and also don't forget to remove the coma before the data parameter!
Lovecraft Beyond1 October 2018 at 02:52
For all those seeking the same solution as me, here is what I did for the function to work on a table to show everything correctly:

var opt = '';

$('#options').on('change', function(){
opt = $(this).val();
dataTable.ajax.reload();
});

var dataTable = $('#user_data').DataTable({
"processing":true,
"serverSide":true,
"order":[],
"ajax":{
url:"fetch.php",
type:"POST",
data: function ( d ) {
d.options = opt;
}
},
"columnDefs":[
{
"targets":[2, 3],
"orderable":false,
},
],

});

The table could not update when it was called from a function in case you wanted to do a CRUD operation on it, so I simply shifted the function only on the data instead of enveloping the whole table! This way, only the data changes, and the table do not require to be destroyed, just simply reloaded!
josefizuda14 February 2019 at 02:39
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
khan13 June 2019 at 10:45
Giving css for td is a big headache. Please help me, if there is a way..!!
Anand2 July 2019 at 21:48
How to add utf 8 char set to it.
i am unable to fetch or get Unicode character for example: Indian languages.
Alex9 July 2019 at 21:27
wonderful work!
please, I would like to know how use a very simple combobox (string options) into the rows.
JAM13 September 2019 at 00:46
NOT WORKING
WASTE OF TIME
alpgnc10 November 2019 at 05:49
Hi,
Database information in German.JSON don't encoding german chars.
Please help!
Unknown14 March 2020 at 15:16
Hi! Can you put the source code? please. Thank you! Sorry for my very bad english.
Unknown27 April 2020 at 06:42
how I insert one column more and how change option column....plz
Thomas Wheal18 May 2020 at 10:58
Hi,
This is great! Is it possible to do this with export to excel? I have tried it but the excel that is exported only has the first page and not all the pages, also the header containd all the dropdown info?
Thanks,
Tom
Senthil16 November 2023 at 01:33
how to filter more than one column

Webslesson

PHP, Node.js, React.js, MySql, Jquery, AngularJS, Ajax, Codeigniter, Laravel Tutorial

Thursday, 13 July 2017