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