Tag Archive DataTables

  • Home   /  
  • Posts tagged "DataTables"
    • ( Page8 ) ( Page8 )
Byphunsanit
, , , , , , , ,

DataTables: ทำ Ajax data grid table

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<!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
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
$(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
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
<?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

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

Generic selectors
Exact matches only
Search in title
Search in content
Post Type Selectors

Recent Posts

Proudly powered by WordPress | Theme: Appointment by Webriti