เมื่อมีข้อมูลจำนวนมาก โดยที่ไม่ได้แสดง primary key หรือข้อมูลมาจากการ join ข้อมูลหลาย ๆ ตาราง การอ้างอิงแถวในตารางก็จะทำไม่สะดวก เดิมจะใช้วิธี query โดยใช้ :rownum หรือ row_number ให้แสดงแถวของข้อมูลตาม database ที่ใช้ แต่ข้อเสียก็คือ มันจะเพิ่มภาระให้ฐานข้อมูล ซึ่งจริง ๆ แล้วสามารถผลักภาระส่วนนี้ไปให้ทางฝั่ง client ได้
<!doctype html> <html> <head> <meta charset="utf-8"> <meta name="author" content="Pitt Phunsanit"> <title>DataTables: row number</title> <link href="../vendor/twbs/bootstrap/dist/css/bootstrap.min.css" rel="stylesheet" type="text/css"> <link href="../vendor/twbs/bootstrap/dist/css/bootstrap-theme.min.css" rel="stylesheet" type="text/css"> <link href="../vendor/datatables/datatables/media/css/dataTables.bootstrap.min.css" rel="stylesheet" type="text/css"> </head> <body> <table class="table table-bordered table-hover table-striped" id="tableA" width="100%"></table> <script src="../vendor/components/jquery/jquery.min.js"></script> <script src="../vendor/datatables/datatables/media/js/jquery.dataTables.min.js"></script> <script src="../vendor/datatables/datatables/media/js/dataTables.bootstrap.min.js"></script> <script src="row_number.js"></script> </body> </html>
$ (function () {
tableA = $ ('#tableA') ;
datatable = tableA.DataTable ({
"ajax": {
"data": function (parameters) {},
"method": "POST",
"url": "data.json.php",
},
"columns": [{
"orderable": false,
"render": function render (data, type, row, meta) {
var row_number = (parseInt (meta.settings._iDisplayStart) + parseInt (meta.row) + 1) ;
return String (row_number) .replace (/ (\d) (?= (\d{3})) /g, '$1,') ;;
},
"searchable": false,
"targets": 0,
"title": "No.",
},
{
"orderable": false,
"render": function (data, type, row, meta) {
return parseInt (meta.row) + parseInt (meta.settings._iDisplayStart) + 1;
},
"title": 'No.',
"width": "10px",
},
{
"orderable": false,
"render": function (data, type, row, meta) {
return '<input type="checkbox" value="' + row.DISTRICT_CODE + '">';
},
"title": '<input class="checkAll" type="checkbox">',
"width": "10px",
},
{
"orderable": false,
"render": function (data, type, row, meta) {
if (row.enable == '1') {
return '<span class="glyphicon glyphicon-ok"></span>';
} else {
return '<span class="glyphicon glyphicon-remove"></span>';
}
},
"title": "Enable",
"width": "10px",
}, {
"data": "DISTRICT_CODE",
"title": "District Code",
"width": "90px",
}, {
"data": "DISTRICT_NAME",
"title": "District Name",
}, {
"data": "PROVINCE_NAME",
"title": "Province Name",
}
],
/* default sort */
"order": [
[3, "asc"],
[4, "asc"],
],
"processing": true,
"serverSide": true,
"stateSave": true,
}) ;
$ ('.checkAll', tableA) .click (function () {
$ ('input:checkbox', tableA) .not (this) .prop ('checked', this.checked) ;
}) ;
}) ;
<?php
/* https://datatables.net/manual/server-side */
$output = [
'data' => [],
'debug' => [
'length' => $_REQUEST['length'],
'post' => $_REQUEST,
'sqlCount' => '',
'sqlResult' => '',
'start' => $_REQUEST['start'],
],
'draw' => $_REQUEST['draw'],
'recordsFiltered' => $_REQUEST['length'],
'recordsTotal' => 0,
];
$dns = new PDO ('mysql:host=localhost;dbname=snippets', 'root', '', [
//PDO::ATTR_EMULATE_PREPARES => false,
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
]) ;
$condition = [];
$from = ' FROM district AS d LEFT JOIN province AS p ON d.PROVINCE_ID = p.PROVINCE_ID';
$parameters = [];
$where = '';
if (isset ($_REQUEST['filters']) || isset ($_REQUEST['search']['value'])) {
if (isset ($_REQUEST['search']['value'])) {
if ($_REQUEST['search']['value'] != '') {
$parameter = ':d_DISTRICT_NAME';
$parameters[$parameter] = '%' . $_REQUEST['search']['value'] . '%';
array_push ($condition, 'd.DISTRICT_NAME LIKE ' . $parameter) ;
}
}
if (isset ($_REQUEST['filters'])) {
foreach ($_REQUEST['filters'] as $tableAlias => $filter) {
foreach ($filter as $field => $value) {
if ($value != '') {
$parameter = ':' . $tableAlias . '_' . $field;
$parameters[$parameter] = '%' . $value . '%';
array_push ($condition, $tableAlias . '.' . $field . ' LIKE ' . $parameter) ;
}
}
}
}
}
if (isset ($_REQUEST['geo_id']) && $_REQUEST['geo_id'] != '') {
$parameter = ':d_geo_id';
$parameters[$parameter] = $_REQUEST['geo_id'];
array_push ($condition, 'd.GEO_ID = ' . $parameter) ;
}
if (count ($parameters)) {
$where = ' WHERE ' . implode ("\n\t AND ", $condition) ;
}
if (isset ($_REQUEST['order']) && isset ($_REQUEST['order'][0])) {
$columns = [
0 => 'DISTRICT_NAME',
3 => 'DISTRICT_CODE',
4 => 'DISTRICT_NAME',
5 => 'PROVINCE_NAME',
];
$order = ' ORDER BY ' . $columns[$_REQUEST['order'][0]['column']] . ' ' . strtoupper ($_REQUEST['order'][0]['dir']) ;
} else {
$order = ' ORDER BY DISTRICT_NAME ASC';
}
$output['debug']['parameters'] = $parameters;
/* Total records, before filtering */
$sql = 'SELECT COUNT (d.DISTRICT_ID) ' . $from;
try {
$output['debug']['sqlCount'] = $sql;
$stmt = $dns->prepare ($sql) ;
$stmt->execute ($parameters) ;
$output['recordsTotal'] = (int) $stmt->fetchColumn (0) ;
} catch (PDOException $e) {
exit ($e->getMessage ()) ;
}
/* Total records, after filtering */
$sql = 'SELECT COUNT (d.DISTRICT_ID) ' . $from . $where;
try {
$output['debug']['sqlCount'] = $sql;
$stmt = $dns->prepare ($sql) ;
$stmt->execute ($parameters) ;
$output['recordsFiltered'] = (int) $stmt->fetchColumn (0) ;
} catch (PDOException $e) {
exit ($e->getMessage ()) ;
}
/* data */
if ($output['recordsTotal'] > 0) {
$sql = 'SELECT d.enable, d.DISTRICT_ID, d.DISTRICT_CODE, d.DISTRICT_NAME, p.PROVINCE_NAME' . $from . $where . $order . " LIMIT " . $_REQUEST['start'] . ", " . $_REQUEST['length'] . ";";
try {
$output['debug']['sqlResult'] = $sql;
$stmt = $dns->prepare ($sql) ;
$stmt->execute ($parameters) ;
$output['data'] = $stmt->fetchAll (PDO::FETCH_ASSOC) ;
} catch (PDOException $e) {
exit ($e->getMessage ()) ;
}
}
/* unset debug for security */
unset ($output['debug']) ;
header ('Content-type: application/json; charset=utf-8') ;
echo json_encode ($output) ;