DataTables warning: table id=users_list - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

I am getting data from users table based on active-inactive status after that with foreach loop data should be displayed using data table but getting an error

DataTables warning: table id=users_list - Invalid JSON response

so my requirement is

1) Show all data in the data table
2) per page 5 records
3) Default search box of a data table
4) Pagination of a data table

Following is my code but this didn't work for me In index.php file dropdown list and based on onchange event ajax call to a users_list method in ajax controller.

In index.php  file drop-down list with ajax call request



 <select class="custom-select" onchange="get_users(this.value)">
        <option value="active">Active</option>
        <option value="inactive">Inactive</option>
    </select>
<div class="show_data"></div>

    <script>
    function get_users(status){
    $.ajax({
    type:'POST',
    url:'<?php echo SITE_URL . 'ajax/users_list'; ?>',
    data:{ status:status},        
    success: function(data)
    {        
    $(".show_data").html(data);
    },
    error: function (jqXHR, textStatus, errorThrown)
    {
    alert('Failed');
    }
    });
    }

    </script>

In controller Ajax.php

function users_list(){
$status=$this->input->post('status');   //active  or inactive 
$data['users']=$this->db->where('status',$status)->get('users')->result();
$this->load->view('view_assets',$data); 
}

In view_assets.php files

<table id="display_userinfo">
    <thead>
        <tr class="bg-light">
            <th>user id</th>
            <th>user name</th>
            <th>Mobile no</th>
        </tr>
    </thead>
    <?php foreach ($users as $user) { ?>
        <tbody>
            <tr>
                <td><?= $user->id ?></td>
                <td><?= $user->name ?></td>
                <td><?= $user->mobile ?></td>
            </tr>
        <?php } ?>
    </tbody>
    <script>
        $('#display_userinfo').DataTable({
            "processing": true,
            "serverSide": true,
        });
    </script>

Solution

JavaScript

You've set ServerSide to be true when you invoke DataTable(). But you've already created the table. Change this:

    $('#display_userinfo').DataTable({
        "processing": true, //Optional, only useful for *large* tables
        //"serverSide": true, //REMOVE THIS
    });

From their documentation:

By default DataTables operates in client-side processing mode, but can be switched to server-side processing mode using this option. Server-side processing is useful when working with large data sets (typically >50'000 records) as it means a database engine can be used to perform the sorting etc calculations - operations that modern database engines are highly optimised for, allowing use of DataTables with massive data sets (millions of rows).

You should only use ServerSide if you're planning on feeding DataTable() JSON. Otherwise you can just run DataTable() without the ServerSide flag and it will translate your HTML to its format.

If you rejig your backend you can still use ServerSide, but it may not be necessary. Here's an example to get your started:

$('#display_userinfo').dataTable( {
  "serverSide": true, //Here it's necessary
  "ajax": {
     "url": '<?php echo SITE_URL . 'ajax/users_list'; ?>',
     "data": {}
   }
});

HTML

You've also made a small markup error, that may or may not have an impact on DataTables. Change the following:

    <tbody> <!-- This needs to be BEFORE the foreach() loop -->
<?php foreach ($users as $user) { ?>
            <tr>
                <td><?= (int)$user->id ?></td>
                <td><?= htmlspecialchars((string)$user->name) ?></td>
                <td><?= htmlspecialchars((string)$user->mobile) ?></td>
            </tr>
        <?php } ?>
    </tbody> <!-- Correctly placed *AFTER* the foreach() loop -->