Datatable Scroller (Server Side) Part:3

怼烎@ 2021-06-26 16:06 346阅读 0赞

假设您有数百万条记录，你不需要分页，然后您可以使用数据表滚动条。 我认为滚动条比分页更好的用户体验，因为几乎你可以一次查看整个表。 今天我将展示如何在`datatable.js v1.10`中实现`scroller`并通过`ajax`显示服务器端数据。 要在`datatable`中实现`scroller`，您必须添加`js`库或扩展`dataTables.scroller.js`。 您可以直接从这里下载。

Datatable将根据您的视图（如表格）获取一些额外的记录，以实现平滑渲染。 它将根据滚动位置向服务器请求新记录。  
让我初始化数据表：

$(document).ready(function() { 
       var dataTable =  $('#employee-grid').DataTable( { 
    	serverSide: true,
    	ajax:{ 
    			url :"employee-grid-data.php", // json datasource
    			type: "post",  // method , by default get
    			error: function(){   // error handling
    				$(".employee-grid-error").html("");
    				$("#employee-grid").append('<tbody class="employee-grid-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
    				$("#employee-grid_processing").css("display","none");
    			}
    		},
    	dom: "frtiS",
    	scrollY: 200,
    	deferRender: true,
    	scroller: { 
    	    loadingIndicator: true
    	}
        } );
    } );

这里有三个选项很重要：:

*  `dom`: “`frtiS`”表格的`Dom`元素，这里“`f`”表示过滤，“r”用于处理，“t”用于表，“i”用于表的信息，“S”用于表滚动
 *  `scrollY`: 200这是网格高度。
 *  `deferRender`: `true`仅当您滚动时，它才会从服务器渲染大量数据（表行）中的块数据。 它将显着提高性能。

根据“`scrollY`”中提到的表高度，滚动条将触发带有起始行号和限制条数的`ajax`。假设您的数据库中有200条记录。在只显示5行的表视图中，默认情况下，`scroller`将获取`10`条记录以进行平滑渲染。 在下一个滚动触发器中，`ajax`将请求更多10条记录。此长度按数据表滚动条本身的“`scrollY`”计算。  
![datatable scroller viewport][]

在我的第一篇文章中，我讨论了服务器端处理。 根据您的搜索参数，顺序和限制条数，这是服务器端代码：

//Database connections
    $servername = "localhost";
    $username = "root";
    $password = "password";
    $dbname = "test";
    $conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: ".mysqli_connect_error());
    
    // storing request (ie, get/post) global array to a variable
    $requestData = $_REQUEST;
    
    $columns = array(
    	// datatable column index => database column name
    	0 => 'employee_name',
    	1 => 'employee_salary',
    	2 => 'employee_age'
    );
    
    // getting total number records without any search
    $sql = "SELECT employee_name, employee_salary, employee_age ";
    $sql. = " FROM employee";
    $query = mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
    $totalData = mysqli_num_rows($query);
    $totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.
    
    $sql = "SELECT employee_name, employee_salary, employee_age ";
    $sql. = " FROM employee WHERE 1=1";
    if (!empty($requestData['search']['value'])) {  // if there is a search parameter, $requestData['search']['value'] contains search parameter
    	$sql. = " AND ( employee_name LIKE '".$requestData['search']['value']. "%' "; $sql. = " OR employee_salary LIKE '".$requestData['search']['value']. "%' "; $sql. = " OR employee_age LIKE '".$requestData['search']['value']. "%' )"; } $query = mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees"); $totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result. $sql. = " ORDER BY ".$columns[$requestData['order'][0]['column']]. "   ".$requestData['order'][0]['dir']. "   LIMIT ".$requestData['start']. " ,".$requestData['length']. "   "; /* $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc , $requestData['start'] contains start row number ,$requestData['length'] contains limit length. */ $query = mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees"); $data = array(); while ($row = mysqli_fetch_array($query)) { // preparing an array $nestedData = array(); $nestedData[] = $row["employee_name"]; $nestedData[] = $row["employee_salary"]; $nestedData[] = $row["employee_age"]; $data[] = $nestedData; } $json_data = array( "draw" => intval($requestData['draw']), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw. "recordsTotal" => intval($totalData), // total number of records "recordsFiltered" => intval($totalFiltered), // total number of records after searching, if there is no searching then totalFiltered = totalData "data" => $data // total data array
    );
    
    echo json_encode($json_data); // send data as json format

--------------------

## Datatable实现滚动加载 ##

一般来说`datatable`都是默认使用`“paging”:true`,这一属性来控制全局的表格翻页。效果大致如下图：  
![在这里插入图片描述][20190115144436831.png]

但是有时候你也会用到滚动条式的翻页方式，类似下面这种：  
![在这里插入图片描述][20190115144629415.png]  
这时，你只需要加上以下两个属性即可。

scrollY: 1000,
        scroller: { 
              loadingIndicator: true
                },

其中`scrollY`表示：定义一个高度,当列表内容超过这个高度时,显示垂直滚动条。

`scroller`表示开启滚动翻页。

下面是官网论坛上给出的一个`demo`：

$(document).ready(function() { 
           var dataTable =  $('#employee-grid').DataTable( { 
            serverSide: true,
            ajax:{ 
                    url :"employee-grid-data.php", // json datasource
                    type: "post",  // method , by default get
                    error: function(){   // error handling
                        $(".employee-grid-error").html("");
                        $("#employee-grid").append('<tbody class="employee-grid-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
                        $("#employee-grid_processing").css("display","none");
                    }
                },
            dom: "frtiS",
            scrollY: 200,
            deferRender: true,
            scroller: { 
                loadingIndicator: true
            }
            } );
        } );

[datatable scroller viewport]: /images/20210626/cfd2a11b223b4319a43d3a0845ec65c4.png
[20190115144436831.png]: /images/20210626/7c1c2d0df62a4e028c646e35469c0b3c.png
[20190115144629415.png]: /images/20210626/347593837cb54e9f9dfa293787bac877.png