Reinitialise DataTable

Question

I have build a filter in my DataTable so I can check for the status. I am using the following code to retrieve the status:

    if(isset($_POST['status']))
    {
        if (!empty($where) ) {
         $where .= "AND status = '". $_POST['status'] ."'";
        } else {
         $where .= "WHERE status = '". $_POST['status'] ."'";
        }
    }
    else{
        if (!empty($where) ) {
         $where .= "AND status = '1'";
        } else {
         $where .= "WHERE status = '1'";
        }
    }

I have no issues with selecting the data. When I look at the WebConsole I can see that the script is posting the right data en gets the right response.

But I have some issues with the presentation of the data.

When the response is correct I want to update my DataTable.

I am using the following code for updating my datatable:

      success:function(data){
        $('#tb1').DataTable(data);
      }

When I execute this code I get a DataTables warning:

DataTables warning: table id=tb1 - Cannot reinitialise DataTable.

I cant figure out whats wrong with my script. Looking to multiple examples the script should work. Does someone know what is wrong with my script and how I can solve this problem?

Here is my full script:

    <script type="text/javascript">
     $( document ).ready(function() {
      $('#tb1').DataTable({
       "bprocessing": true,
       "serverSide": true,
    "ajax": {
    "url": "./Response1.php",
    "type": "POST",
    "error": function(){
    $("#grid_processing").css("display","none");
    }
    }     
      });   
      $("div.toolbar1").html('<select id="status" type="status"><option value="1">Active</option><option value="0">Inactive</option></select>');

      $("#status").on('change',function () {

    var val = $(this).val();
    $.ajax({
      url: './Response1.php',
      type: 'POST',
      data: 'status='+val,
      success:function(data){
        $('#tb1').DataTable(data);
      }
    });
      });
     });
    </script>

Answer 1

Updated with explanation and references

There is no need separate Ajax request. Stick with Datatables Ajax option is enough.

We can use Datatables ajax.data option to add additional data to the request, or to modify the data object being submitted to server if required.

To work with new and refresh data input we need to use ajax.data as a function otherwise it will initialized as a static object which will evaluated only once.

var table = $('#example').dataTable( {
  "ajax": {
    "url": "data.json",
    "data": function ( data) {
        //your data altering codes
    }
  }
} );

And then use Datatables ajax.reload() to reload the table data from the Ajax data source within your event calls.

Possible ways to update data request using Datatables ajax.data is by:

1. Using element value directly

var table = $('#example').dataTable({
    "ajax": {
        "url": "data.json",
        "data": function(data) {
            data.status = $('#status').val();
        }
    }
});
table.ajax.reload();

2. Using global variable that can be changed inside event call before Datatables ajax.reload()

var global_status = 1;
var table = $('#example').dataTable({
    "ajax": {
        "url": "data.json",
        "data": function(data) {
            data.status = global_status;
        }
    }
});

$("#status").on('change', function() {
    global_status = $(this).val();
    table.ajax.reload();
});

Sample demo:

$.mockjax({
    url: "Response1.php",
    response: function(settings) {
        // Investigate the `settings` to determine the response...
        if (settings.data.status == 1) {
            this.responseText = {
                "draw": settings.data.draw,
                "recordsTotal": 57,
                "recordsFiltered": 57,
                "data": [
                    [
                        "Airi",
                        "Satou",
                        "Accountant",
                        "Tokyo",
                        "28th Nov 08",
                        "1"
                    ],
                    [
                        "Angelica",
                        "Ramos",
                        "Chief Executive Officer (CEO)",
                        "London",
                        "9th Oct 09",
                        "1"
                    ],
                    [
                        "Ashton",
                        "Cox",
                        "Junior Technical Author",
                        "San Francisco",
                        "12th Jan 09",
                        "1"
                    ],
                    [
                        "Bradley",
                        "Greer",
                        "Software Engineer",
                        "London",
                        "13th Oct 12",
                        "1"
                    ],
                    [
                        "Brenden",
                        "Wagner",
                        "Software Engineer",
                        "San Francisco",
                        "7th Jun 11",
                        "1"
                    ],
                    [
                        "Brielle",
                        "Williamson",
                        "Integration Specialist",
                        "New York",
                        "2nd Dec 12",
                        "1"
                    ],
                    [
                        "Bruno",
                        "Nash",
                        "Software Engineer",
                        "London",
                        "3rd May 11",
                        "1"
                    ],
                    [
                        "Caesar",
                        "Vance",
                        "Pre-Sales Support",
                        "New York",
                        "12th Dec 11",
                        "1"
                    ],
                    [
                        "Cara",
                        "Stevens",
                        "Sales Assistant",
                        "New York",
                        "6th Dec 11",
                        "1"
                    ],
                    [
                        "Cedric",
                        "Kelly",
                        "Senior Javascript Developer",
                        "Edinburgh",
                        "29th Mar 12",
                        "1"
                    ]
                ]
            }
        }
        if (settings.data.status == 0) {
            this.responseText = {
                "draw": settings.data.draw,
                "recordsTotal": 57,
                "recordsFiltered": 57,
                "data": [
                    [
                        "Airi",
                        "Satou",
                        "Accountant",
                        "Tokyo",
                        "28th Nov 08",
                        "0"
                    ],
                    [
                        "Angelica",
                        "Ramos",
                        "Chief Executive Officer (CEO)",
                        "London",
                        "9th Oct 09",
                        "0"
                    ],
                    [
                        "Ashton",
                        "Cox",
                        "Junior Technical Author",
                        "San Francisco",
                        "12th Jan 09",
                        "0"
                    ]
                ]
            }
        }
    }
});



$(document).ready(function() {
    var req_status = 1;
    var table = $('#example').DataTable({
        "processing": true,
        "serverSide": true,
        "paging":   false,
        "ordering": false,
        "info":     false,
        "searching": false,
        "ajax": {
            "url": "Response1.php",
            "type": "POST",
            "data": function(data) {
                data.status = req_status;
            }
        },
    });

    $("div.toolbar1").html('<select id="status" type="status"><option value="1">Active</option><option value="0">Inactive</option></select>');

    $("#status").on('change', function() {
        req_status = $(this).val();
        table.ajax.reload();
        console.log('Status Val',table.ajax.params().status);
    });

});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-mockjax/1.6.2/jquery.mockjax.min.js"></script>

<script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css" rel="stylesheet" />
<div class="toolbar1"></div>
<table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th>First name</th>
                <th>Last name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Start date</th>
                <th>Status</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>First name</th>
                <th>Last name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Start date</th>
                <th>Status</th>
            </tr>
        </tfoot>
    </table>

Answer 2

Try this

$("#tb1").dataTable().fnDestroy();
$("#tb1").html(data);
$("#tb1").DataTable();

Answer 3

Add this after serverside: true,

destroy: true

to destroy the table after its initialised

Answer 4

Try using a single call

function initTable(tableId, apiUrl){
 var table_element = "#" + tableId;
 if ($.fn.DataTable.isDataTable(table_element )) {
    //remove datatable framework on the table so we can re-initialize new record
      $(table_element).DataTable().clear().destroy();
      $(table_element).html(''); //empty the table entirely
    }
//re-initialize table
     var table = $(table_element).DataTable({
                "destroy": true,
                "bprocessing": true,
                "serverSide": true,
                "ajax": {
                "url": apiUrl,
                "type": "POST",
                "error": function(){
                  $("#grid_processing").css("display","none");
                  }
               }     
          });   
      }

Call function from anywhere like this

initTable('tb1','./Response1.php');