DataTable Search By Datepicker (Server side) Part:9

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

I received many tutorial requests from my readers that asked to me how to integrate datepicker search in datatable custom column with server side data. So I have modified custom column search demo.

To integrate datepicker I have used jQueryui datepicker. You can go the site and grab the assets from there jQueryui and after existing assets.



	<link rel="stylesheet" type="text/css" href="css/jquery.dataTables.css">
	<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
	<script type="text/javascript" language="javascript" src="js/jquery.js"></script>
	<script type="text/javascript" language="javascript" src="js/jquery.dataTables.js"></script>
	 <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>

 

Now we’ll initialize the datatable


$(document).ready(function() {

	var dataTable =  $('#employee-grid').DataTable( {
	processing: true,
	serverSide: true,
      ajax:"employee-grid-data.php" // json datasource

	});
});

Now hide the global default search


	$("#employee-grid_filter").css("display","none");

In the markup we’ll add input text for each column



<thead>
	<tr>
		<td><input type="text" id="0"  class="employee-search-input"></td>
		<td><input type="text" id="1" class="employee-search-input"></td>
		<td><input type="text" id="2" class="employee-search-input" ></td>
		<td><input type="text" id="3" class="employee-search-input" ></td>
		<td><input type="text" id="4" class="employee-search-input" ></td>
		<td  valign="middle"><input  readonly="readonly" type="text" id="5" class="employee-search-input datepicker" ></td>
		<td><input type="text" id="6" class="employee-search-input" ></td>
	</tr>
</thead>

Each input box has a common class attribute “employee-search-input” . in click or keyup or change event we’ll fetch column index and input value as below and draw a request for searching.
Here in 5th column we have added jquery datepicker by class “datepicker”


$('.employee-search-input').on( 'keyup click change', function () {
	var i =$(this).attr('id');  // getting column index
	var v =$(this).val();  // getting search input value
	dataTable.columns(i).search(v).draw();
} );

Now its time to set datepicker on “Joining Date ” column. So, by “datepicker” class I have initialized datepicker and some extra option like date format and clear button. You can found more here.
Also make input text readonly, that user will not able to put value through text box.

DataTable Search By Datepicker


 $( ".datepicker" ).datepicker({
 	dateFormat: "yy-mm-dd",
	showOn: "button",
	showAnim: 'slideDown',
	showButtonPanel: true ,
	autoSize: true,
	buttonImage: "//jqueryui.com/resources/demos/datepicker/images/calendar.gif",
	buttonImageOnly: true,
	buttonText: "Select date",
	closeText: "Clear"
});

Now Im clearing search value on click of “clear” button.


 
 $(document).on("click", ".ui-datepicker-close", function(){
	$('.datepicker').val("");
	dataTable.columns(5).search("").draw();
});

 

So here is the full initialization code.


$(document).ready(function() {

	var dataTable =  $('#employee-grid').DataTable( {
	processing: true,
	serverSide: true,
	ajax: "employee-grid-data.php", // json datasource

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

	$('.employee-search-input').on( 'keyup click change', function () {
		var i =$(this).attr('id');  // getting column index
		var v =$(this).val();  // getting search input value
		dataTable.columns(i).search(v).draw();
	} );

	 $( ".datepicker" ).datepicker({
	 	dateFormat: "yy-mm-dd",
		showOn: "button",
		showAnim: 'slideDown',
		showButtonPanel: true ,
		autoSize: true,
		buttonImage: "//jqueryui.com/resources/demos/datepicker/images/calendar.gif",
		buttonImageOnly: true,
		buttonText: "Select date",
		closeText: "Clear"
	});
	$(document).on("click", ".ui-datepicker-close", function(){
		$('.datepicker').val("");
		dataTable.columns(5).search("").draw();
	});
} );


My server side code remain same as custom column search code. If you have any issue with server side set up then refer first post (DataTable demo (Server side) in Php,Mysql and Ajax Part:1) of this series. If you have any query comment below of this post.aligncenter size-large wp-image-187

DownloadDemo


  • davel

    Thanks Arkaprava, another great tutorial! Much appreciated.

  • Alexis Stamand

    Few days back I was wondering for that. great tutorial, Now I’m going through whole series.

  • Hi Arkaprava, Great tutorial.Thanks for your help. How can we have checkbox in one column and get data as array.

  • J Ramesh Fernandez

    Awesome Tutorial.
    Date Range search will be more usefull
    and also export to excel or pdf option can be added to it

  • sulung

    Problem with the input field. It process the server side when I put the
    mouse on the empty form and when typing. Better the server side is
    processed after we finish typing (onmouseout). Buy the way I love this
    post very much…

    • make readonly field. you dont need to keep it writable. this way you can solve the problem easily.

  • Imran Shah

    Hi Arkaprava, A great tutorial for beginners like me. How can I stop to display all records by default on screen. I want that, record should be display only when someone search it from the search box. another query is that, in current demo when we start typing on search box its start giving result.. so i want that result should be displayed after completing typing and on click on a search button. Kindly help me to resolve the issue.

  • Mick Byrne

    Brilliant. thanks!

  • Arvind Bhardwaj

    Great:)

  • Manas

    I want to add one more extra Database column field how it is possible. Plz help me. My id manaschakraborty18@gmail.com

  • Manas

    In the Demo there is 7 Column I want extra One means eight or nine how it’s possible

  • Angeloimad

    Hello, i really find this very brilliant. i just have one issue, i want to combine this tutorial with the ColVis tutorial, but am in a deep mess, cause am enabling to solve the column confilct, can you help me plz?

  • Lavy Ya

    how to use range date filter with server side script !

    • Ashutosh Tiwari

      This is a working tutorial

      #
      Name
      Vendor
      Amount
      Status
      Email
      Phone
      Renewal Date
      Policy No.
      Purchased Date

      Vehicle Type
      Two Wheeler
      Four Wheeler

      $(document).ready(function () {
      $(function () {
      // var today_date = new date();
      // console.log(today_date);
      $('.date_range').daterangepicker({
      "showDropdowns": true,
      "autoUpdateInput": true,
      "minYear": 2019,
      "autoApply": false,
      ranges: {
      'Today': [moment(), moment()],
      'Yesterday': [moment().subtract(1, 'days'), moment().subtract(1, 'days')],
      'Last 7 Days': [moment().subtract(6, 'days'), moment()],
      'Last 30 Days': [moment().subtract(29, 'days'), moment()],
      'This Month': [moment().startOf('month'), moment().endOf('month')],
      'Last Month': [moment().subtract(1, 'month').startOf('month'), moment().subtract(1, 'month').endOf('month')]
      },

      "locale": {
      "format": "YYYY-MM-DD",
      "separator": " to ",
      "applyLabel": "Apply",
      "cancelLabel": "Clear",
      "fromLabel": "From",
      "toLabel": "To",
      "customRangeLabel": "Custom",
      "weekLabel": "W",
      "daysOfWeek": [
      "Su",
      "Mo",
      "Tu",
      "We",
      "Th",
      "Fr",
      "Sa"
      ],
      "monthNames": [
      "January",
      "February",
      "March",
      "April",
      "May",
      "June",
      "July",
      "August",
      "September",
      "October",
      "November",
      "December"
      ],
      "firstDay": 1
      },
      "alwaysShowCalendars": true,
      "parentEl": "date_range",
      // "startDate": moment().subtract(7, 'days'),
      // "endDate": moment(),
      "maxDate": moment()
      }
      , function (start, end, label) {
      console.log('New date range selected: ' + start.format('YYYY-MM-DD') + ' to ' + end.format('YYYY-MM-DD') + ' (predefined range: ' + label + ')');
      });

      $('.date_range').on('apply.daterangepicker', function (ev, picker) {
      $(this).val(picker.startDate.format('YYYY-MM-DD') + ' to ' + picker.endDate.format('YYYY-MM-DD'));
      var i = $(this).attr('data-column'); // getting column index
      var v = $(this).val(); // getting search input value
      dataTable.columns(i).search(v).draw();
      });

      $('.date_range').on('cancel.daterangepicker', function (ev, picker) {
      $(this).val('');
      var i = $(this).attr('data-column'); // getting column index
      var v = $(this).val(); // getting search input value
      dataTable.columns(i).search(v).draw();
      });
      });
      var dataTable = $('#employee-grid').DataTable({
      "dom": 'lrtip',
      "pageLength": 15,
      "processing": true,
      "ordering": false,
      "responsive": true,
      "serverSide": true,
      "ajax": {
      url: "/get-motor-reports", // json datasource
      type: "post", // method , by default get
      error: function () { // error handling
      $(".employee-grid-error").html("");
      $("#employee-grid").append('No data found in the server');
      $("#employee-grid_processing").css("display", "none");

      }
      }
      });
      $("#employee-grid_filter").css("display", "none"); // hiding global search box
      $('.search-input-text').on('keyup click', function () { // for text boxes
      var i = $(this).attr('data-column'); // getting column index
      var v = $(this).val(); // getting search input value
      dataTable.columns(i).search(v).draw();
      });
      $('.search-input-select').on('change', function () { // for select box
      var i = $(this).attr('data-column');
      var v = $(this).val();
      dataTable.columns(i).search(v).draw();
      });

      });

      /*********************************Ajax Page Add this******************/
      if (!empty($requestData['columns'][2]['search']['value'])) { //date range
      $rangeArray = explode(" to ", $requestData['columns'][2]['search']['value']);
      $minRange = trim($rangeArray[0]);
      $maxRange = trim($rangeArray[1]);
      $sql .= " AND ( DATE(timestamp) >= '" . $minRange . "' AND DATE(timestamp) <= '" . $maxRange . "' ) ";
      }

  • Elmer Jay Tubes Basario

    how can i print reports in the sorted data using this tutorial thanks

  • Elmer Jay Tubes Basario

    plsss sir i need it for my Thesis Study T_T

  • Anisa Azis

    Why do I get “invalid JSON response” when i run your code in my localhost?
    Thanks for your reply in advance!

  • Sergio

    Anybody have all this parts at one? I can`t do it. Code from one page another that code from second page. Dont`t work perfectly….

  • excelente che