jQuery DataTable data disappears after sorting

Question

I am unsure as to why the data in my datatable disappears after sorting.

Starting with the jQuery which is fired once the user clicks the submit button:

 $('#searchSubmit').on('click', function()
 {
   var searchbooking = $('#searchbooking').val();
   var searchbol = $('#searchbol').val();

   $.post('api/search.php', {searchbooking: searchbooking, searchbol: searchbol}, function(data)
   {
     var obj = JSON.parse(data);
     $('#tableBody').empty(); 
     var htmlToInsert = obj.map(function (item)
     {
       return '<tr><td>'+item.BOL_DATE+'</td><td>'+ item.BOOKING_NUM +'</td></tr>';
     });
     $('#tableBody').html(htmlToInsert.join(''));
   });
 });

Here is the PHP script, with which (I might add) I am using SQLSRV coding for the first time:

 <?php
 if($_POST['searchbooking'] == true || $_POST['searchbol'] == true)
 {
   $_SESSION['where'] = "";
   $searchbooking = stripslashes(str_replace( "'", "''", $_POST['searchbooking']));
   $searchbol = stripslashes(str_replace( "'", "''", $_POST['searchbol']));

   if($searchbooking != "")
   {
     if( $_SESSION['where'] != "" ) $_SESSION['where'] .= " AND ";
     $_SESSION['where'] = "[BOOKING_NUM] = '".$searchbooking."'";
   }
   if($searchbol != "")
   {
     if( $_SESSION['where'] != "" ) $_SESSION['where'] .= " AND ";
     $_SESSION['where'] .= "[BOL_NUM] = '".$searchbol."'";
   }

   $where = "WHERE " . $_SESSION['where'];

   $select = "SELECT [BOL_DATE], [BOOKING_NUM] FROM [brokerage].[dbo].[detailbackup] ".$where."";

   $query = sqlsrv_query($dbc, $select);

   $out = array();
   while( $row = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC) ) 
   {
     $out[] = $row;
   }
   echo json_encode($out);  
 ?>

Back in my HTML page, the table is set up like this:

 <table class='table table-striped table-bordered table-hover display nowrap' id='example1' cellspacing="0" width="100%">
 <thead>
   <tr>
     <th>Bol Date</th>
     <th>Booking Number</th>
   </tr>
 </thead>
 <tbody id="tableBody">
 </tbody>
 </table>

Near the bottom of the HTML page, above the closing body tag, I have the JavaScript that formats the DataTable:

 <script type="text/javascript">
   $('#example1').DataTable({
     "iDisplayLength": 25,
     "scrollX": true,
     "scrollY": 550,
     "order": [[ 0, "desc" ]],
     "bLengthChange": true,  
     "bSort": true,   
     "bAutoWidth": true   
 </script>

With everything I have added above, I can return the data to the page. But once I sort (or even change the length of the table from 25 to 50), the data disappears.

Does anyone see my error?

Answer 1

You cannot add new rows to dataTables this way; you have to go through the API. You are only inserting new rows to the DOM table, not dataTables internals, thats why the rows seems to disappear when you are sorting (or filtering etc). Keep the dataTable instance in a global variable :

table = $('#example1').DataTable({
  "iDisplayLength": 25,
  ...
})

Now rewrite the entire $.post to use the API instead of jQuery DOM manipulation :

$.post('api/search.php', {searchbooking: searchbooking, searchbol: searchbol}, function(data) {

   table.clear() //clear content

   var obj = JSON.parse(data);

   obj.forEach(function(item) { //insert rows
     table.row.add([item.BOL_DATE, item.BOOKING_NUM])
   })

   table.draw() //update display
})

Answer 2

htmlToInsert = obj.map(...) turns htmlToInsert into an array, so you need to turn that back into a string with .join():

$('#tableBody').html(htmlToInsert.join(''));

Secondly, you have not specified the data type in your $.post call. According to the docs the 4th argument is:

The type of data expected from the server. Default: Intelligent Guess (xml, json, script, text, html).

Leaving it to the guess of jQuery may make your own call to JSON.parse fail, as the data might very well already be an object.

Take away this risk, put the data type in the 4th argument of $.post:

}, 'json');

and remove the call to JSON.parse, like so:

obj = data;