table – Page 3 – PlusMagi's Blog By Pitt Phunsanit

Byphunsanit

AJAX, DataTables, grid, pagination, paging, table, กริด, ตาราง, เอแจ็กซ์

DataTables: ทำ Ajax data grid table

DataTables เป็น grid ที่สามารถให้ฟรีได้โดยไม่มีเงื่อนไขจริง ๆ การใช้ก็ไม่ยุ่งยากจนเกินไป
jQuery.DataTables/data.json.html

<!doctype html>
<html>
 
<head>
    <meta charset="utf-8">
    <meta name="author" content="Pitt Phunsanit">
    <title>DataTables: datas from ajax</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="data.json.js"></script>
</body>
 
</html>

jQuery.DataTables/data.json.js

$(function() {
 
    tableA = $('#tableA');
 
    datatable = tableA.DataTable({
        "ajax": {
            "data": function(parameters) {},
            "method": "POST",
            "url": "data.json.php",
        },
        "columns": [{
                "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);
    });
 
});

jQuery.DataTables/data.json.php

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

json ที่ส่งกลับมาที่สำคัญคือ

data: เป็นข้อมูลที่นำมาสร้างเป้นตัว body ใน table หรือคือ data ที่ต้องการนำมาแสดง
draw: เป็นรหัสอ้างอิงให้ DataTables รู้ว่า json ที่ส่งมาอันไหนใหม่กว่า จะเรียกว่าเป็นเลข version ของข้อมูลก็ได้
recordsFiltered: เป็นจำนวนชุดข้อมูลทั้งหมดที่ query ได้ (ไม่ใช่จำนวนจริงๆ ที่ return กลับมา เช่น query ได้ข้อมูลทั้งหมด 8,880 รายการ แต่ทำ paging ดึงข้อมูล data มาแค่ครั้งละ 10, 20, 50 และ 100 รายการ)
recordsTotal: เป็นจำนวนข้อมูลทั้งหมดที่มีใน table โดยที่ยังไม่ใส่เงื่อนไข query ซึ่งจริงๆแล้วคัดลอกค่ามากจาก recordsTotal ไปเลยก็ได้ จะได้ไม่เสียเวลา query

อ่านเพิ่มเติม

Tag Archive table

Recent Posts