Load jQuery DataTable on change event

问题:

I am using this script to load the DataTable on ready:

function renderDataTable(serviceUrl) 
{
  var $dataTable = $('#example1').DataTable({
    "ajax": serviceUrl,
    "iDisplayLength": 25,
    "order": [[2, "asc"]],
    "scrollY": 600,
    "scrollX": true,
    "bDestroy": true
   });
 });

Here is the ready event:

 $(document).ready(function()
 {
   renderDataTable('api/service_teus.php');
 });

The PHP script looks like this:

 <?php
 $select = "SELECT SERVICE, SIZE_TYPE, TEUS FROM table";
 $query = mysqli_query($dbc, $select) or die(mysqli_error());

 $out = array();
 while($row = $query->fetch_assoc())
 {
   $out[] = $row;
 }
 echo json_encode($out);
 mysqli_free_result($query);
 ?>     

All the above code works fine. The DataTable loads when the page is ready, and the DataTable works like how it's supposed to.

What I need to do is create the ability for the user to reload the datatable when a new option is selected in a dropdown with an ID #serviceload.

So I remove the ready event.

Now, in the JavaScript, I create a change event:

 $('#serviceload').change(function()
 {
   var page = $('#serviceload').val();  // user selection

   var $dataTable: $('#example1').DataTable({  // datatable
     "ajax": "api/service_teus.php", {page: page},  // here is where I think the problem lies
     "data": data,
     "iDisplayLength": 25,
     "order": [[2, "asc"]],
     "scrollY": 600,
     "scrollX": true,
     "bDestroy": true
   });
 });

More than likely, I'm guessing the error is in the ajax call above.

I alter the PHP script slightly to look like this:

 <?php
 if($_POST['page'] == true)
 {
   $service = mysqli_real_escape_string($dbc, $_POST['page']);
   $select = "SELECT SERVICE, SIZE_TYPE, TEUS FROM table WHERE SERVICE - '$service'";
   $query = mysqli_query($dbc, $select) or die(mysqli_error());

   $out = array();
   while ($row = $query->fetch_assoc())
   {
     $out[] = $row;
   }
   echo json_encode($out);
   mysqli_free_result($query);
 }
 ?>

I am not sure if I using the AJAX call correctly in the JavaScript directly above.

回答1:

So, after 2 weeks of research, I finally found the solution to my problem.

Since I already had a HTML dropdown in place, in the READY event, I added this:

 var table = $('#example1').DataTable();
 $('#serviceload').on('change', function(){
   table.columns(1).search( this.value ).draw();
 });

Which I found here: http://jsfiddle.net/Ratan_Paul/5Lj6peof/1/

And now, when the CHANGE event is fired, the new data is populated without sending a variable to the server, which is what I was trying to do in my initial code above.

回答2:

You could do a GET request

var $dataTable: $('#example1').DataTable({
     "ajax": {
        "url": "scripts/server_processing.php",
        "data": { page: page }
     },
     "data": data,
     "iDisplayLength": 25,
     "order": [[2, "asc"]],
     "scrollY": 600,
     "scrollX": true,
     "bDestroy": true
});

and the retrieve your get param with $_GET['page']

If you really want to do a POST, you could do:

var $dataTable: $('#example1').DataTable({
     "ajax": {
        "url": "scripts/server_processing.php",
        "data": function ( d ) {
           d.page = page;
        },
        "type": "POST"
     },
     "data": data,
     "iDisplayLength": 25,
     "order": [[2, "asc"]],
     "scrollY": 600,
     "scrollX": true,
     "bDestroy": true
});

I never used datatables, I got this from the documentation. Hope this helps.