假设您有数百万条记录,你不需要分页,然后您可以使用数据表滚动条。 我认为滚动条比分页更好的用户体验,因为几乎你可以一次查看整个表。 今天我将展示如何在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
}
} );
} );