DataTable Custom Filter Server Side

Introduction

DataTable Custom Filter Server Side helps to create and filter data’s based on your choice. Like some peoples have to filter contents based on certain criteria like Status, Types or Groups. So the custom filter helps to make the things for you. Let’s create a server side processing DataTable and insert a filter as drop down and use it here in our table.

Table Code

The below code helps you to create a HTML element for your table to create server side processing DataTable.

<table class="list bordered highlight" id="CustomerInvoices">
 <thead><tr> <th> <?php echo _("Reference"); ?> </th> <th> <?php echo _("Customer Name"); ?> </th> <th> <?php echo _("Invoice Date"); ?></th> <th> <?php echo _("Due Date"); ?></th> <th> <?php echo _("Amount"); ?> </th> <th> <?php echo _("Yet To Receive"); ?></th><th> <?php echo _("Status"); ?></th> </tr></thead>
 </table>

JS

Let’s write JS to call DataTable with below code.

ToReceiveTable = $("#CustomerInvoices").dataTable({
 "processing": true,
 "serverSide": true,
 "order": [[ 6, "asc" ]],
 "pageLength": 25, 
 "ajax": "<?php echo get_url(); ?>ajax?CustomerInvoices=yes"
 });

The Above one is ordinary ServerSide Processing DataTable code.

Let’s create a drop down within the datatable.

ToReceiveTable = $("#CustomerInvoices").dataTable({
 "processing": true,
 "serverSide": true,
 "order": [[ 6, "asc" ]],
 "pageLength": 25,
 "dom": '<"FilterCustomerInvoice">frtip',
 "ajax": "<?php echo get_url(); ?>ajax?CustomerInvoices=yes"
 });
 $("div.FilterCustomerInvoice").html('<label> Filter Status : <select name="FilterCustomerInvoice" id="FilterCustomerInvoice"> <option value="-1" > All </option><option value="Overdue" > Overdue </option><option value="Yet To Receive" > Yet To Receive </option> <option value="Received" > Received </option></select></label>');

Let’s write the DataTable Refresh when user selects a filter option from the drop down which we added.

 $("#FilterCustomerInvoice").on("change", function(){ 
    var FilterVal = $(this).val(); 
    var newURL = "<?php echo get_url(); ?>ajax?CustomerInvoices=yes&FilterStatus="+FilterVal;
    ToReceiveTable.api().ajax.url(newURL).load();
 });

Now, we have to create a Ajax file to handle the request to process.

if(isset($_GET['CustomerInvoices']) && $_GET['CustomerInvoices'] == 'yes'){
	
	$sql_details = array( 
		 'user' => 'kvcodes',
		 'pass' => 'Demo@123',
		 'db'   => 'demo_db',
		 'host' => 'localhost'
		); 
	$table = 'customer_trans';
	$primaryKey = 'id';
	
	$columns = array(
		array( 'db' => '`so`.`reference`',  'dt' => 0,	'field' => 'reference' ),
		array( 'db' => '`debtor`.`name`',  	'dt' => 1,	'field' => 'name'),
		array( 'db' => '`so`.`tran_date`',	'dt' => 2,	'field' => 'tran_date','formatter' => function( $d, $row ) 	{ return date( 'jS M y', strtotime($d)); } ),
		array( 'db' => '`so`.`due_date`',   'dt' => 3,	'field' => 'due_date', 'formatter' => function( $d, $row ) 	{ return date( 'jS M y', strtotime($d)); } ),
		array( 'db' => '`so`.`ov_amount`',  'dt' => 4,	'field' => 'ov_amount'),
		array( 'db' => '`so`.`alloc`',  	'dt' => 5,	'field' => 'alloc'),
		array( 'db' => '`so`.`status`',   'dt' => 6,	'field' => 'status')
	);

	$joinQuery = "FROM `{$table}` AS `so`";  
	$extraWhere = '';
	if(isset($_GET['FilterStatus'])){
			if($_GET['FilterStatus'] == 'Received'){
					$extraWhere .= " AND status= 2 ";
			}elseif($_GET['FilterStatus'] == 'Overdue'){
					$extraWhere .=" AND status = 1";
			}elseif($_GET['FilterStatus'] == 'Yet To Receive' ){
					$extraWhere .=" AND status=3";
			}
	}
	
	$wigepa_sources = Master_Table::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery , $extraWhere, '',  'yes' );
	echo json_encode($wigepa_sources);
}

That’s it. The Ajax will get trigger to get the filters as of the users choice. Let’s do some cosmetic things by CSS

CSS

.FilterCustomerInvoice { float: left; text-align: left; }
.FilterCustomerInvoice select { padding: 7px; border-color: #dddddd; }

Conclusion

The Customer filter for Datatable Serverside processing is done as of your status. You can try yourself with the example code I Provided, if you have any difficulty drop your comment on next tab, I will try to help you on this.