DataTable Custom Search By Tags Input (Server side) Part:8

This post is 8th 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)

Few days back I was answering a post about datatable custom search by tags input in Stackoverflow where I have just extended datatable global search by tagsinput jquery plugin. You should use any tagsinput plugin from here. To implement datatable tagsinput, we will follow below steps:

Add tagsinput assets in the index page. You can clone those from Github


<script type="text/javascript" language="javascript" src="js/jquery.tagsinput.js"></script>
<link rel="stylesheet" type="text/css" href="css/tagsinput/jquery.tagsinput.css">

Hide default global search


$("#employee-grid_filter").css("display","none");  // hiding global search box

Its very easy to implement tags input in datatable’s default search box, but It will affect the grid design ui. So I will create a custom tags input under the table header.

Custom search input implementation


<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 colspan="3" ><input type="text" data-column="0"  class="search-input-text"></td>
		</tr>
	</thead>
</table>

Initialization Tagsinput


$('.search-input-text').tagsInput({   // initialization of tags input 
	'height':'100%',
	'width':'100%',
	'interactive':true,
	'defaultText':'Add a tag',
	'hide':true,
	'delimiter':',',
	'unique':true,
	'onAddTag':tagDraw,
	'onRemoveTag':tagDraw,
	'removeWithBackspace' : true,
	'minChars' : 0,
	'maxChars' : 0, //if not provided there is no limit,
	'placeholderColor' : '#AAA'
});
function tagDraw(){              //draw a request on add or remove tag
	var v= $(".search-input-text").val();
	dataTable.search(v).draw();
}

Here tagDraw() is a callback function and will fire after adding or removing a tag. I am fetching data from search input and redraw the search using datatable search() api.

Now our frontend is ready, now we have to modify our server side code. In my first post of this series I have covered how to implement server side integration with datatable.js. When you search through some tag it will request to the server all parameter as a comma separated string.

So we need to use “IN” operator to search through comma separated string. Before search make sure wrap quotation for each string value.


$searchString = "'" . str_replace(",", "','", $requestData['search']['value']) . "'";

So here is full server side code :


// 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";
if( !empty($requestData['search']['value']) ) {   // if there is a search parameter, $requestData['search']['value'] contains search parameter
	$searchString = "'" . str_replace(",", "','", $requestData['search']['value']) . "'"; //wrapping qoutation
	$sql.=" AND ( employee_name IN (".$searchString.") ";    
	$sql.=" OR employee_salary IN (".$searchString.") ";
	$sql.=" OR employee_age IN (".$searchString.")  )";
}
$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']."   ";
/* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc  */	
$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

DownloadDemo


  • Hey thanks for the comment. Can you please check this example https://datatables.net/examples/api/counter_columns.html.

    If you have further any issue, then post/mail your tried code, Hope I will able to help you out 🙂

    • Miku Loder

      Thanks for quick reply. Yeah it’s quite easy to do when all table content is written in html. I’d like to make it work on server-side, though, when index column play nicely with table pagination. Also I want to add a checkbox to index/number column in order to perform bulk deletion of selected rows.Could you make a tutorial about index column and bulk deletion? Cheers.

  • CLAUDIO RODRIGUEZ ORE

    there is a problem with using “IN” within the mysql query , in the “Employee name” column, it is best used “LIKE”

  • harvey mendoza

    hi how to smart search in specific column im trying hard but nothing.. plsssssssssss

  • iosephos

    hello can i use this tag system for image search in a website?

  • vincenzo vecchio

    Hi how can I use prepared statement?