A DataTable with Initial Column Search Values - Jamie Johnson
Jamie Johnson
May 3, 2016
Last month, I wrote multiple articles about SpryMedia's DataTables Table plug-in for jQuery and various features or functions thereof, including how to use my own script to further extend the use of the plug-in. In this article, I show how to take advantage of DataTable's search functionality in a way so as to pre-populate each column's search with an initial value. To do this, I wrote some of my own script, which parses the URL and from it grabs the respective parameter to pass to the DataTable. This is a bit more involved than my prior article A DataTable with an Initial Search Value where only the main search was pre-populated.
| Reset Search Results | |||||
| First Name | Last Name | Where from | Years | Demise | Reason |
|---|---|---|---|---|---|
| Catherine | N/A | Aragon, Spain | 1509-1533 | Divorced | Lack of a male heir / Henry VIII had an affair with the sister of his mistress Mary Boleyn -- Anne Boleyn |
| Anne | Boleyn | probably Blickling Hall | 1533-1536 | Executed | Lack of a male hair / Henry VIII fancied a lady-in-waiting, Jane Seymour |
| Jane | Seymour | Wiltshire | 1536-1537 | Died | Complications from the birth of Edward |
| Anne | N/A | Cleves | 1540 | Divorced | Arranged marriage / Henry not attracted to her and took a liking to Kathryn Howard |
| Kathryn | Howard | unspecified | 1540-1542 | Executed | Rumored infidelity with men her age (she was 30 years younger than Henry VIII) |
| Katherine | Parr | unspecified | 1543-1547 | Widowed | Henry VIII died |
And here's the code:
<script type="text/javascript" src="//code.jquery.com/jquery-1.12.0.min.js"></script>
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/t/dt/dt-1.10.11,r-2.0.2/datatables.min.css"/>
<script type="text/javascript" src="//cdn.datatables.net/t/dt/dt-1.10.11,r-2.0.2/datatables.min.js"></script>
<style>
td { text-align:center; }
th:first-child, td:first-child { text-align:left; }
#example_filter { position: relative; left: -40px; }
</style>
<script>
/* start DataTable initial-value search from parameter by @jamesarthurjohn */
var pagebase = '';
if(window.location.toString().indexOf('?filter')!=-1) {
pagebase = window.location.toString().split('?filter');
pagebase = pagebase[0];
} else {
pagebase = window.location;
}
var searchquery3 = '';
if(window.location.toString().indexOf('?filter3=')!=-1){
searchquery3 = window.location.toString().split('?filter3=');
searchquery3 = searchquery3[1];
searchquery3 = searchquery3.replace('%20',' ');
searchquery3 = searchquery3.replace('%27',"'");
searchquery3 = searchquery3.replace('%22','');
}
var searchquery4 = '';
if(window.location.toString().indexOf('?filter4=')!=-1){
searchquery4 = window.location.toString().split('?filter4=');
searchquery4 = searchquery4[1];
searchquery4 = searchquery4.replace('%20',' ');
searchquery4 = searchquery4.replace('%27',"'");
searchquery4 = searchquery4.replace('%22','');
}
var searchquery5 = '';
if(window.location.toString().indexOf('?filter5=')!=-1){
searchquery5 = window.location.toString().split('?filter5=');
searchquery5 = searchquery5[1];
searchquery5 = searchquery5.replace('%20',' ');
searchquery5 = searchquery5.replace('%27',"'");
searchquery5 = searchquery5.replace('%22','');
}
/* end DataTable initial-value search from parameter by @jamesarthurjohn */
$(document).ready(function() {
var oTable = $('.datatable').dataTable( { // used .dataTable() instead of .DataTable()
responsive: true,
"aaSorting": [ ],
"oLanguage": {"sSearch": "Search all columns:"},
"aoColumns": [
{ "asSorting": [ "desc", "asc" ] },
{ "asSorting": [ "desc", "asc" ] },
{ "asSorting": [ "desc", "asc" ] },
{ "asSorting": [ "desc", "asc" ] }, //[ null ] will make it not sortable
{ "asSorting": [ "desc", "asc" ] },
{ "asSorting": [ "desc", "asc" ] }
],
"searchCols": [
null,
null,
{ "search": searchquery3 },
{ "search": searchquery4 },
{ "search": searchquery5 },
null
]
} );
// First column
// 0 is the first td in tr.colsearch and is identified as firstsearch
$(".colsearch td").eq(0).html('<input id="firstsearch" name="descriptionSearch" size="6" type="text" />');
$("#firstsearch").keyup( function () {
oTable.fnFilter( this.value, 0, null, false ); // 0 is the first column
} );
// Second search
$(".colsearch td").eq(1).html('<input id="secondsearch" name="descriptionSearch" type="text" />');
$("#secondsearch").keyup( function () {
oTable.fnFilter( this.value, 1, null, false );
} );
// third search
// 2 is the third td in tr.colsearch and is identified as thirdsearch
$(".colsearch td").eq(2).html('<input id="thirdsearch" name="descriptionSearch" size="6" type="text" />');
$("#thirdsearch").keyup( function () {
oTable.fnFilter( this.value, 2, null, false ); // 2 is the third column
} );
// fourth search
$(".colsearch td").eq(3).html('<input id="fourthsearch" name="descriptionSearch" size="6" type="text" />');
$("#fourthsearch").keyup( function () {
oTable.fnFilter( this.value, 3, null, false );
} );
// fifth search
$(".colsearch td").eq(4).html('<input id="fifthsearch" name="descriptionSearch" size="6" type="text" />');
$("#fifthsearch").keyup( function () {
oTable.fnFilter( this.value, 4, null, false );
} );
// sixth search
$(".colsearch td").eq(5).html('<input id="sixthsearch" name="descriptionSearch" size="10" type="text" />');
$("#sixthsearch").keyup( function () {
oTable.fnFilter( this.value, 5, null, false );
} );
} );
</script>
<table border="0" cellpadding="0" cellspacing="0" class="datatable">
<thead>
<tr class="filterlnx">
<td colspan="2"><a href="javascript:location.href=pagebase">Reset Search Results</a></td>
<td><select>
<option onclick="location.href=pagebase" />
<option onclick="location.href=pagebase + '?filter3=aragon'">Aragon</option>
<option onclick="location.href=pagebase + '?filter3=spain'">Spain</option>
<option onclick="location.href=pagebase + '?filter3=blickling hall'">Blickling Hall</option>
<option onclick="location.href=pagebase + '?filter3=wiltshire'">Wiltshire</option>
<option onclick="location.href=pagebase + '?filter3=cleves'">Cleves</option>
<option onclick="location.href=pagebase + '?filter3=unspecified'">unspecified</option>
</select></td>
<td><select>
<option onclick="location.href=pagebase" />
<option onclick="location.href=pagebase + '?filter4=1509'">1509</option>
<option onclick="location.href=pagebase + '?filter4=1533'">1533</option>
<option onclick="location.href=pagebase + '?filter4=1536'">1536</option>
<option onclick="location.href=pagebase + '?filter4=1537'">1537</option>
<option onclick="location.href=pagebase + '?filter4=1540'">1540</option>
<option onclick="location.href=pagebase + '?filter4=1543'">1543</option>
<option onclick="location.href=pagebase + '?filter4=1547'">1547</option>
</select></td>
<td><select>
<option onclick="location.href=pagebase" />
<option onclick="location.href=pagebase + '?filter5=divorced'">divorced</option>
<option onclick="location.href=pagebase + '?filter5=executed'">executed</option>
<option onclick="location.href=pagebase + '?filter5=died'">died</option>
<option onclick="location.href=pagebase + '?filter5=widowed'">widowed</option>
</select></td>
<td></td>
</tr>
<tr class="colsearch">
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Where from</th>
<th>Years</th>
<th>Demise</th>
<th>Reason</th>
</tr>
</thead>
<tbody>
<tr>
<td>Catherine</td>
<td>N/A</td>
<td>Aragon, Spain</td>
<td>1509-1533</td>
<td>Divorced</td>
<td>Lack of a male heir / Henry VIII had an affair with the sister of his mistress Mary Boleyn
-- Anne Boleyn</td>
</tr>
<tr>
<td>Anne</td>
<td>Boleyn</td>
<td>probably Blickling Hall</td>
<td>1533-1536</td>
<td>Executed</td>
<td>Lack of a male hair / Henry VIII fancied a lady-in-waiting, Jane Seymour</td>
</tr>
<tr>
<td>Jane</td>
<td>Seymour</td>
<td>Wiltshire</td>
<td>1536-1537</td>
<td>Died</td>
<td>Complications from the birth of Edward</td>
</tr>
<tr>
<td>Anne</td>
<td>N/A</td>
<td>Cleves</td>
<td>1540</td>
<td>Divorced</td>
<td>Arranged marriage / Henry not attracted to her and took a liking to Kathryn Howard</td>
</tr>
<tr>
<td>Kathryn</td>
<td>Howard</td>
<td>unspecified</td>
<td>1540-1542</td>
<td>Executed</td>
<td>Rumored infidelity with men her age (she was 30 years younger than Henry VIII)</td>
</tr>
<tr>
<td>Katherine</td>
<td>Parr</td>
<td>unspecified</td>
<td>1543-1547</td>
<td>Widowed</td>
<td>Henry VIII died</td>
</tr>
</tbody>
</table>
Now, I know that drop-down select menus are available natively with DataTables, but the use of these drop-down select menus are able to exist in addition to the column search fields. AND you can search from the results using the global search. Also know that you can create links to pre-populate columns (with granular specificity) using this approach like this:
Pre-Populate 5th column with results for "di"
And the code is as follows to do this:
<a href="http://tech.beacondeacon.com/datatable-with-initial-column-search-values.html?filter5=di"> Pre-Populate 5th column with results for "di" </a>
And in this case you would get both "divorced" and "died" entries as a result, not even restricted to the drop-down. Granted, used of this technology would be more useful with a larger table with much more data.