tech @ Beacon Deacon

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.

Back to top