排序后jQuery DataTable数据消失

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:

从用户单击提交按钮后触发的jQuery开始:

 $('#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脚本,我可以添加(第一次)使用SQLSRV编码:

 <?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:

回到我的HTML页面,表格设置如下:

 <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:

在HTML页面的底部附近,在结束体标记上方,我有JavaScript格式化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.

通过上面添加的所有内容,我可以将数据返回到页面。但是一旦我排序(甚至将表的长度从25改为50),数据就会消失。

Does anyone see my error?

有人看到我的错误吗?

2 个解决方案

#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 :

您不能以这种方式向dataTables添加新行;你必须通过API。您只是向DOM表插入新行,而不是dataTables内部,这就是为什么在排序(或过滤等)时行似乎消失了。将dataTable实例保存在全局变量中:

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

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

现在重写整个$ .post以使用API而不是jQuery DOM操作:

$.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
})

#2

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

htmlToInsert = obj.map(...)将htmlToInsert转换为数组,因此您需要将其转换回带有.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:

其次,您没有在$ .post调用中指定数据类型。根据文档,第四个论点是:

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

服务器所需的数据类型。默认值:智能猜测(xml,json,脚本,文本,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.

让它对jQuery的猜测可能使你自己对JSON.parse的调用失败,因为数据很可能已经是一个对象。

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

消除这种风险,将数据类型放在$ .post的第4个参数中:

}, 'json');

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

并删除对JSON.parse的调用,如下所示:

obj = data;

#1

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