Datatable Scroller (Server Side) Part:3

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

<script type="text/javascript" language="javascript" src="js/dataTables.scroller.js"></script>

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”计算。

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

//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,这一属性来控制全局的表格翻页。效果大致如下图：

但是有时候你也会用到滚动条式的翻页方式，类似下面这种：

这时，你只需要加上以下两个属性即可。

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