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

1
2
<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

1
$("#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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
<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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
$('.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.

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

So here is full server side code :

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
// 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