DataTable (Server side) Custom Column Search Part:2

This post is second part of a series called Getting Started with Datatable 1.10 .

  1. DataTable demo (Server side) in Php,Mysql and Ajax
  2. DataTable (Server side) Custom Column Search
  3. Datatable Scroller (Server Side)
  4. DataTable Responsive (Server side)
  5. Datatable Custom Loader
  6. Datatable Colvis (Column Visibility Customization)
  7. DataTable Table tools
  8. DataTable Custom Search By Tags Input (Server side)
  9. DataTable Search By Datepicker (Server side)
  10. Datatable Bulk Delete (Server Side)
  11. Datatable Page Resize (Server Side)

In my previous post I have shown basic server side data integration with datatable.js through ajax,php and mysql. Today I will show how to customize searching in datatable 1.10. By default datatable provides a global search box. I will implement custom column search functionality for each columns. So first take a look of the demo here

First I have added two search text fields for name and salary. Then I have also added a select box for age range.Have defined column index for every column in html5 data attribute, starting from “0”. Here is the markup:

<table id="employee-grid"  cellpadding="0" cellspacing="0" border="0" class="display" width="100%">
	<thead>
		<tr>
			<th>Employee name</th>
			<th>Salary</th>
			<th>Age</th>
		</tr>
	</thead>
	<thead>
		<tr>
			<td><input type="text" data-column="0"  class="search-input-text"></td>
			<th><input type="text" data-column="1"  class="search-input-text"></td>
			<td>
				<select data-column="2"  class="search-input-select">
					<option value="">(Select a range)</option>
					<option value="19-30">19 - 30</option>
					<option value="31-66">31 - 66</option>
				</select>
			</td>
		</tr>
	</thead>
</table>

Then I have modified initialization of the datatable.(If you have facing any problem to integrate the datatable, please take look of my first post about datatable).

After initialize I have hide the global search box by

$("#employee-grid_filter").css("display","none");

Then I have taken input of the search value and column index through keyup, click and change event.

$('.search-input-text').on( 'keyup click', function () {   // for text boxes
	var i =$(this).attr('data-column');  // getting column index
	var v =$(this).val();  // getting search input value
	dataTable.columns(i).search(v).draw();
} );
$('.search-input-select').on( 'change', function () {   // for select box
	var i =$(this).attr('data-column');
	var v =$(this).val();
	dataTable.columns(i).search(v).draw();
} );

By dataTable.columns(i).search(v).draw(); we can draw or request for search result by sending column name and value in “search()” function with “datatable” object variable which has been created at the time of initialization.

In server side we can fetch search value through column index like “$requestData[‘columns’][0][‘search’][‘value’]”.

I have sent four parameters (ie: draw ,recordsTotal, recordsFiltered, data) in json output.

    • draw is request/draw number sent from client side.
    • recordsTotal is numbers of total records in your table.
    • recordsFiltered is calculating as per search parameters, When there is no search parameter, then total numbers filtered data is same as total number of the records, other wise it will be set as per search result with out limit parameter.
    • data is your result sets.

.

// storing  request (ie, get/post) global array to a variable
$requestData= $_REQUEST;

$columns = array(
// datatable column index  => database column name
	0 =>'employee_name',
	1 => 'employee_salary',
	2=> 'employee_age'
);
// getting total number records without any search
$sql = "SELECT employee_name, employee_salary, employee_age ";
$sql.=" FROM employee";
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.

$sql = "SELECT employee_name, employee_salary, employee_age  ";
$sql.=" FROM employee WHERE 1 = 1";

// getting records as per search parameters
if( !empty($requestData['columns'][0]['search']['value']) ){   //name
	$sql.=" AND employee_name LIKE '".$requestData['columns'][0]['search']['value']."%' ";
}
if( !empty($requestData['columns'][1]['search']['value']) ){  //salary
	$sql.=" AND employee_salary LIKE '".$requestData['columns'][1]['search']['value']."%' ";
}
if( !empty($requestData['columns'][2]['search']['value']) ){ //age
	$rangeArray = explode("-",$requestData['columns'][2]['search']['value']);
	$minRange = $rangeArray[0];
	$maxRange = $rangeArray[1];
	$sql.=" AND ( employee_age >= '".$minRange."' AND  employee_age <= '".$maxRange."' ) ";
}
$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result.

$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."   LIMIT ".$requestData['start']." ,".$requestData['length']."   ";  // adding length

$query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");

$data = array();
while( $row=mysqli_fetch_array($query) ) {  // preparing an array
	$nestedData=array(); 

	$nestedData[] = $row["employee_name"];
	$nestedData[] = $row["employee_salary"];
	$nestedData[] = $row["employee_age"];

	$data[] = $nestedData;
}

$json_data = array(
			"draw"            => intval( $requestData['draw'] ),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
			"recordsTotal"    => intval( $totalData ),  // total number of records
			"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
			"data"            => $data   // total data array
			);

echo json_encode($json_data);  // send data as json format

Download full code from below link or comment for any query or suggestion.

DownloadDemo


  • sailor

    Yet another great tutorial. Keep up the good work.. 🙂 in you example all search boxer are big, do you how to make a lenght so it only have space for 3-4 letter? Cheers!

    • Use maxlength html attribue and css style to make input box as you need.

      By the way, thanks for the being with this blog.

  • davel

    Thanks Arkaprava! Very good tutorial. Could you please show an example of how to filter with date range (using datapicker) with server side? I think many many would be interested in this. Thanks!

  • Hasan

    thanks to the people like you who contributes in knowledge sharing. May Allah bless you.

  • Markus Philipp

    Hey Arkaprava! Instead of selecting a ‘range’, how would you adjust the above for a ‘multi’ selector?! Meaning more than one (or multiple) select options in a column?!

    • Rudi Giat

      please Arkaprava Majumder answer this question? and how search item that hidden to show in datatable. I mean datatable show this

      Employee name
      Salary
      Age
      but in my search i wanna searching, example : address
      Many Thanks

      • You have to tweak your code in server side.based on your input query in database and based on the result set json.

  • Zorge

    Thank’s very helpfull

  • Ahmed Rasheed

    How do i change certain data column’s directon to rtl?

  • khaleed

    I want to do it with CodeIgniter. please help me.

  • Thanks!

  • Kevin

    why have `$sql.=” FROM employee WHERE 1 = 1″;` and not just have `$sql.=” FROM employee”;` ? tks

  • Yegor

    Hello there, I use django-datatables-views in order to create the json file.
    According to this, I’ve changed “type” to “get” (i can’t use ‘post’).
    And I’ve got the problem with filters. When I’m writing text into ‘search-input-text’ I can see ‘processing modal’ but filtering isn’t working at all. Why is so?

  • Yegor

    hello there, please show the example how to write a range number filter or a range date filter. Thanks!

  • Nadeem Ahmad

    Great work, really appreciate you. There is first column as its index is 0 is by default sorted as asc order. But i want to sorts the data as second columns who’s index is 1 in DESC oreder please any help. Thanks in advance

    • Nadeem Ahmad

      Any one reply please

      • There is an api called columnDef, you can set second column as target:1 and set order as DESC.

        • Nadeem Ahmad

          here is my code
          “targets”: 1,
          “orderable”: true,
          “searchable”: true,
          “order”: ‘DESC’
          its not working correctly any error here

    • Nadeem Ahmad

      Here is no one to answer.????????

    • Pranay Gondane

      Hi You can try this…$(‘#testResult’).DataTable({
      “columns”: [
      {“data”: “name”},
      {“data”: “dob”},
      {“data”: “age”}
      ],
      “order”: [[ 1, “desc” ]],
      “ajax”: {
      url: ‘serverside.php’,
      dataType: “JSON”,
      type: ‘POST’
      }
      });

  • Nadeem Ahmad

    I want to add id and class to each of the table tr, how can i done this. Thanks in advance for the help please reply anyone

  • Ajay

    Hi, thanks for this great tutorial. But how can we search with different search parameters by using on BUTTON click? Instead of keyup/ change.

    • Pranay Gondane

      Have you tried like this….

      $(‘#ur_search_textbox_id’).on( ‘click’, function () {
      var i =$(this).attr(‘data-column’);
      var v =$(this).val();
      table.columns(i).search(v).draw();
      table.search(v).draw();
      } );

      Or also you can refer this https://coderexample.com/datatable-custom-column-search/

  • حسام محمود

    Is there anyway to make the search the same as excel filter search

  • sruthi varghese

    Hi.. How can i reset the filter text boxes to blank on page refresh?

  • Sruthi

    Hi.. How can i reset the filter text boxes to blank on page refresh?

  • Aang Jaeni

    hi, how can i use join 2 table in datatables?

  • Lavy Ya

    how to filter range date with server side script !

  • Yasin Can Tarlan

    Thank you 🙂

  • Alilat

    Hello ! i am a big fun of you awsome work, thank you for all !
    with this tutorial am facing an issue and i will be glade if you culd help.
    i edit this code to fit in a table containing thousands of rows. when i try to filter some columns i keep getting : Invalid JSON response

  • CJ

    Amazing work from you. Thx a million. How can i add an “edit” button to the table to open another php page using a row-id, to find the same record on that page? example “<a href="update2.php?u=”><span class="glyphicon glyphicon-pencil" aria-hidden="true" <font size="3" color="red" Edit“. Pls assist if you can. Thx again

  • Numan YILMAZ

    How can i add a “static (not sortable) row numbers column?

  • Katheesh S Ajith

    Thank you so much for your help

  • thanks for this tutorial, works perfectly!

  • Hafidz Ikhsan Baihaki

    Thank you so much for your awesome tutorial!
    I wanna ask something
    When we type the middle name of employee in search box
    Why it doesn’t filter as usual and we could just search the employee by the first name only?
    Any help?

  • Robert

    Hi, how can the width of the input be modified?

  • Ravindra Yadav

    I am using the same line of Code and its working good… but when i used 3 table Inner join .. the search option seems ..data loaded in table successfully but search option is not working can u please help me !!!

  • genial gracias

  • 🙂 <3

  • tom chan

    thanks the great job.
    can I preselect the data-column 2 like 19-30 and get the result.
    (I know how to preselect the data-column 2 but I don’t know how to preload the data)
    many thanks

  • Ajeet

    Good Work Thnak you Sir..

  • how to show only non-empty values ​​from the database?