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?

Solution 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
})

Solution 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;

Comments

[John Beasley]

-updated my code to no avail. Data still disappears after sorting. Any thoughts?

[trincot]

Could you revert the change in your question? Your change makes my answer look stupid. In the meanwhile I will check...

[trincot]

Check what console.log(data); gives, before you do the JSON.parse. Are you sure it is a JSON string and not already parsed as an object? Did you debug your code with console.log?

[John Beasley]

trincot - thank you for your suggestions. I was trying to utilize the code you provided but in the end, I just could not get it to work. I was however able to use davidkonrad's answer below. Regardless, I have upvoted your answer and responses. Thank you once again.

[John Beasley]

davidkonrad - thank you, sir. I have updated my code to match your answer and everything now works accordingly. Funny thing is - I was also having a problem with the headers misaligned with the table body. Your code answered a question I didn't even have to ask. Thank you.

[John Beasley]

davidkonrad - I just found that the sorting no longer works. Any thoughts?

[trincot]

Thanks for your feed-back! ;-)