Tag Archive ตาราง

Byphunsanit

DataTable: แสดงเลขที่รายการ

เมื่อมีข้อมูลจำนวนมาก โดยที่ไม่ได้แสดง 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);

Byphunsanit

DataTable: ดัดแปลง / คัดลอก ข้อมูล

แนวคิดคือ คัดลอกข้อมูลที่เลือกเอาไว้จาก DataTables ตัวหนึ่งไปยังอีกตัวหนึง

<!doctype html>
<html>

<head>
    <meta charset="utf-8">
    <meta name="author" content="Pitt Phunsanit">
    <title>DataTables: Transfer Data To Another Table</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">
    <link href="../assets/fronteed/icheck/skins/minimal/red.css" rel="stylesheet" type="text/css">
</head>

<body>
    <table class="table table-bordered table-hover table-striped" id="filtersTableA" width="100%"></table>
    <button class="btn btn-success" id="copyBtn" type="button">Copy Data To Table</button>
    <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="../assets/fronteed/icheck/icheck.min.js"></script>

    <script src="DataTables.js"></script>

    <script src="data.json.transferDataToAnotherTable.js"></script>
</body>

</html>

ส่วนที่เขียนเป็นฟังก์ชั่นกลางไว้ให้ไฟล์อื่นเรียกใช้

function iCheckBulk(dataTableArea, dataTableObject) {
    dataTableArea.on('ifChanged', '.checkAll', function(event) {

        var datas = dataTableObject.data();
        var inputs = $('input:checkbox, input:radio', dataTableArea);

        if (event.target.checked) {
            var enable = '1';
            var state = 'check';
        } else {
            var enable = '0';
            var state = 'uncheck';
        }

        $.each(datas, function(index, value) {
            value.enable = enable;

            dataTableObject.row(index).data(value);
        });

        iCheckInit($('input:checkbox, input:radio', dataTableArea));
    });

}

/* change filtersTable data value on input name enables is change */
function iCheckChange(filtersTableArea, filtersTableObject, dataTableObject) {
    $('tbody', filtersTableArea).on('ifChanged', 'input[name="enables[]"]', function(event) {

        event.stopPropagation();

        var row = $(this).closest('tr');

        var data = filtersTableObject.row(row).data();

        if ($(this).is(':checked')) {
            $(this).attr('checked', 1);
            data.enable = true;
        } else {
            $(this).attr('checked', 0);
            data.enable = false;
        }
        filtersTableObject.row(row).data(data);

        iCheckInit(row);
    });
}

function iCheckCopy(dataTableObject, filtersTableObject, pkField) {
    $('#copyBtn').click(function() {

        /* loop current data (pkField) in current dataTableObject */
        var datas = dataTableObject.data();
        var hasKeys = new Array();
        $.each(datas, function(index, value) {
            hasKeys.push(value[pkField]);
        });

        var datasChoose = filtersTableObject.data();

        $.each(datasChoose, function(index, value) {
            /* add row to filtersTableObject if input name enables[] is checked */
            if (value.enable == true && hasKeys.indexOf(value[pkField]) == -1) {
                dataTableObject
                    .row.add(value)
                    .draw()
                    .node();
            }
        });

    });

}

function iCheckInit(selector) {
    selector.iCheck({
        checkboxClass: 'icheckbox_minimal-red',
        radioClass: 'iradio_minimal-red',
    });
}

ไฟล์ที่ทำหน้าที่ควบคุมการคัดลอกข้อมูล

$(function() {

    dataTableA = $('#dataTableA');
    filtersTableA = $('#filtersTableA');
    tableA = $('#tableA');

    filtersTable = filtersTableA
        .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) {
                        if (row.enable == '1') {
                            var checked = ' checked';
                        } else {
                            var checked = '';
                        }

                        return '<input' + checked + ' name="enables[]" 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",
                }
            ],
            "processing": true,
            "serverSide": true,
            "stateSave": true
        })
        .on('draw', function(event, settings, json, xhr) {
            /* add style to checkbox, radio */
            iCheckInit($('input:checkbox, input:radio', settings.nTable));
        });

    iCheckBulk(filtersTableA, filtersTable);

    dataTable = tableA
        .DataTable({
            "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) {
                        if (row.enable == 1) {
                            var checked = ' checked';
                        } else {
                            var checked = '';
                        }

                        return '<input' + checked + ' name="enables[]" 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) {
                            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",
                }
            ]
        })
        .on('draw', function(event, settings, json, xhr) {
            /* add style to checkbox, radio */
            iCheckInit($('input:checkbox, input:radio', settings.nTable));
        });

    iCheckBulk(tableA, dataTable);

    iCheckChange(filtersTableA, filtersTable, dataTable);

    iCheckCopy(dataTable, filtersTable, 'DISTRICT_CODE');

});

อธิบาย

  • มี 2 ตารางคือ id=”filtersTableA” เป็นต้นฉบับ และ id=”tableA” เป็นตารางที่จะรับค่าที่จะคัดลอก
  • เพราะว่าอาจจะแบ่งข้อมูลไว้หลายหน้าและ DataTable จะ render ที่ละหน้าเท่านั้น ถ้า user เลือกรายการโดยใช้ input enables[] แล้วเปลี่ยนไปหน้าอื่น input นั้นจะหายไป ทำให้ต้องใช้วิธี update กลับไปที่ Data ของตัว DataTable filtersTableA โดยเปลี่ยนค่าใน object data เช่น data.enable = true; หรือ data.enable = false;
  • เมือคลิก ปุ่ม id=”copyBtn” ให้คัดลอกข้อมูล เพื่อป้องกันการเก็บข้อมูลซ้ำจึงต้อง ดึงรายการที่เก็บข้อมูลเอาไว้แล้ว มาเทียบว่าที่จะเข้ามาใหม่มีรึยังโดยใช้ primary key คือ DISTRICT_CODE

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

Byphunsanit

DataTable: การค้นหาชั้นสูง

เราสามารถเพิ่มเงื่อนไขให้ DataTables ใช้ในการค้นหาได้ โดยส่วนที่เปลี่ยนไปจากเดิมคือใช้ “beforeSend” ในการรวมข้อมูลจาก form (formA) และเขียน validation ง่ายๆ โดยบังคับว่าถ้า advanceSearch ถูกติ๊กอยู่จะต้องเลือก geo_id ด้วย (จริงๆคือ ถึงไม่ติ๊ก advanceSearch ก็ใช้ geo_id search ได้เหมือนกัน)

<!doctype html>
<html>

<head>
    <meta charset="utf-8">
    <meta name="author" content="Pitt Phunsanit">
    <title>DataTables: external.search</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>
    <form action="DataTables.json.php" class="form-inline" id="formA" method="post">
        <div class="form-group">
            <input name="advanceSearch" type="checkbox">
            <label for="advanceSearch">advance Search</label>
        </div>
        <div class="form-group">
            <label for="geo_id">ภูมิภาค:</label>
            <select class="form-control" name="geo_id">
               <option selected="selected" value="">--- Select ---</option>
               <option value="1">ภาคเหนือ</option>
               <option value="2">ภาคกลาง</option>
               <option value="3">ภาคตะวันออกเฉียงเหนือ</option>
               <option value="4">ภาคตะวันตก</option>
               <option value="5">ภาคตะวันออก</option>
               <option value="6">ภาคใต้</option>
            </select>
        </div>
        <button type="submit" class="btn btn-default">Search</button>
    </form>
    <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.external.search.js"></script>
</body>

</html>
$(function() {

    formA = $('#formA');
    tableA = $('#tableA');

    datatable = tableA.DataTable({
        "ajax": {
            "beforeSend": function(jqXHR, settings) {
                /* add value form from to DataTable params */
                settings.data = formA.serialize() + '&' + settings.data;

                /* validation */
                var params = new URLSearchParams(settings.data);

                /* user must selected region if enable advance search */
                if (params.get('advanceSearch') == 'on' && params.get('geo_id') == '') {
                    jqXHR.abort();
                    alert('กรุณาเลือกภูมิภาค');
                    return false;
                }

                return true;
            },
            "dataSrc": function(json) {
                alert('data back ' + json.data.length + ' items');

                return json.data;
            },
            "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);
    });

    formA.submit(function(event) {
        event.preventDefault();

        datatable.ajax.reload();
    });

});
  • ข้อมูลจากฟอร์ม
    id="formA"

    จะถูกรวมกับ data ที่ DataTable สร้างขึ้นมาเองโดย

    "beforeSend": function(jqXHR, settings) {
    settings.data = formA.serialize() + '&' + settings.data;
    },
  • สามารถทำ validation data ก่อนส่งข้อมูลออกไป ถ้าต้องการหยุดการทำงาน ก็ให้ใช้
    "beforeSend": function(jqXHR, settings) {
    jqXHR.abort();
    },

    ไม่ให้ส่งข้อมูลกลับไป server อาจจะดัดแปลงให้ datatable ไม่ขอข้อมูลจาก server กรณีที่ไม่มีการเลือกตัว filter ก็ได้เช่นกัน

  • หลังได้ข้อมูลกลับมายังสามารถใช้
    "dataSrc": function(json) {
                    alert('data back ' + json.data.length + ' items');
    
                    return json.data;
                },

    แก้ไขข้อมูลก่อนแสดงผลได้ แต่ไม่ใช่ “success” เหมือนใน jQuery ajax ตามปกตินะครับ เพราะจะทำให้ DataTable มันทำงานผิดปกติได้เลย

  • ถ้าต้องการให้ DataTable ดึงข้อมูลใหม่ให้ใช้รูปแบบ
    datatable.ajax.reload();

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

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

Byphunsanit

DataTable: ทำ ajax data grid table

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

<!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>
$(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);
    });

});
<?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
เป็นรหัสอ้างอิงให้ DataTable รู้ว่า json ที่ส่งมาอันไหนใหม่กว่า จะเรียกว่าเป็นเลข version ของข้อมูลก็ได้
recordsFiltered
เป็นจำนวนชุดข้อมูลทั้งหมดที่ query ได้ (ไม่ใช่จำนวนจริงๆ ที่ return กลับมา เช่น query ได้ข้อมูลทั้งหมด 8,880 รายการ แต่ทำ paging ดึงข้อมูล data มาแค่ครั้งละ 10, 20, 50 และ 100 รายการ)
recordsTotal
เป็นจำนวนข้อมูลทั้งหมดที่มีใน table โดยที่ยังไม่ใส่เงื่อนไข query ซึ่งจริงๆแล้วคัดลอกค่ามากจาก recordsTotal ไปเลยก็ได้ จะได้ไม่เสียเวลา query

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

Byphunsanit

สร้าง Data Dictionary แค่คลิก

สร้าง Data Dictionary ใน sql server แบบง่ายๆ เพียงแค่คลิกเท่านั้น บทความนี้เป็นเวอร์ชั้นปรับปรุงของ สร้าง Data Dictionary แบบด่วนๆ ให้ทำได้ง่ายขั้น

  1. เปิด sql server management studio เลือก database ที่ต้องการ คลิกขวา New Query
  2. copy
    SET ANSI_PADDING  OFF;
    GO
    
    SET NOCOUNT ON;
    GO
    
    SET TEXTSIZE 8192;
    GO
    
    PRINT '<!doctype html>';
    PRINT '<html lang="th">';
    PRINT '<head>';
    PRINT '<meta charset="utf-8" />';
    PRINT '<title>script generates a full data dictionary for all tables in a SQL Server by Pitt Phunsanit</title>';
    PRINT '<style>table {background: #7ba0cd;border-collapse: collapse;border-left: solid 1px #7ba0cd;border-right: solid 1px #7ba0cd;font-family: "Calibri", "sans-serif";font-size: 11pt;width: 100%;}table tr {background: #FFFFFF;border-bottom: solid 1px #7ba0cd;}table thead tr {background-color: #4e80bc;color: white;}table tbody tr:nth-of-type(odd) {background: #d2deed;border-bottom: solid 1px #FFFFFF;box-shadow: 0 2px 0 -1px #7ba0cd;}table tbody tr.case,table tfoot tr {background: #d7d7d7;}</style>';
    PRINT '</head>';
    PRINT '<body>';
    PRINT '<h1>' + DB_NAME() + '</h1>';
    PRINT '<h3>create date : ' + convert(VARCHAR(10), GETDATE(), 111) + '</h3>';
    
    DECLARE @record CURSOR;
    DECLARE @table VARCHAR(128);
    DECLARE @object_id INT;
    DECLARE @schema_id INT;
    
    SET @record = CURSOR
    FOR
    SELECT [name] AS [table], [object_id], [schema_id]
    FROM sys.tables AS t
    WHERE type_desc = 'USER_TABLE'
    
    OPEN @record
    
    FETCH NEXT
    FROM @record
    INTO @table, @object_id, @schema_id
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT CAST('<br><table><caption><b>' + @table + '</b>' + ISNULL(' : '+RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), '') + '</caption>' AS NTEXT)
            ,CAST('<thead><tr>' + '<td style="width:100px;"><b>Primary Key</b></td>' + '<td style="width:200px;"><b>Foreign key</b></td>' + '<td style="width:400px;"><b>Column Name</b></td>' + '<td><b>Description</b></td>' AS NTEXT)
            ,CAST('<td style="width:100px;"><b>Data Type</b></td>' + '<td style="width:100px;"><b>Allow Nulls</b></td>' + '<td style="width:100px;"><b>Identity</b></td>' + '<td style="width:100px;"><b>Default Value</b></td>' + '</tr></thead><tbody>' AS NTEXT)
        FROM sys.schemas AS s
        -- get description of table, if available
        LEFT OUTER JOIN sys.extended_properties AS ep ON ep.major_id = @object_id
            AND ep.minor_id = 0
            AND ep.NAME = 'MS_Description'
            AND ep.class = 1
        WHERE s.[schema_id] = @schema_id
        AND NOT EXISTS (
                SELECT *
                FROM sys.extended_properties ms
                WHERE ms.major_id = @object_id
                    AND ms.minor_id = 0
                    AND ms.class = 1
                    AND ms.[name] = 'microsoft_database_tools_support'
                )
    
        UNION ALL
    
      SELECT '' AS html
            ,'<tr><td>' + CASE
                WHEN pk.column_id IS NOT NULL
                    THEN 'PK'
                ELSE ''
                END + '</td><td>' + CASE
                WHEN fk.primary_table IS NOT NULL
                    THEN fk.primary_table + '.' + fk.primary_column
                ELSE ''
                END + '</td><td>' + c.[name] + '</td><td>' + ISNULL(RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), '') + '</td><td>' + CASE
                WHEN uty.[name] IS NOT NULL
                    THEN uty.[name]
                ELSE ''
                END + CASE
                WHEN uty.[name] IS NOT NULL
                    AND sty.[name] IS NOT NULL
                    THEN ' (
                '
                ELSE ''
                END + CASE
                WHEN sty.[name] IS NOT NULL
                    THEN sty.[name]
                ELSE ''
                END + CASE
                WHEN sty.[name] IN (
                        'CHAR'
                        ,'NCHAR'
                        ,'VARCHAR'
                        ,'NVARCHAR'
                        ,'BINARY'
                        ,'VARBINARY'
                        )
                    THEN ' (
                    ' + CASE
                            WHEN c.max_length = - 1
                                THEN 'max'
                            ELSE CASE
                                    WHEN sty.[name] IN (
                                            'NCHAR'
                                            ,'NVARCHAR'
                                            )
                                        THEN CAST(c.max_length / 2 AS VARCHAR(MAX))
                                    ELSE CAST(c.max_length AS VARCHAR(MAX))
                                    END
                            END + '
                    ) '
                WHEN sty.[name] IN (
                        'NUMERIC'
                        ,'DECIMAL'
                        )
                    THEN ' (
                    ' + CAST(c.precision AS VARCHAR(MAX)) + '
                    ,' + CAST(c.scale AS VARCHAR(MAX)) + '
                    ) '
                ELSE ''
                END + CASE
                WHEN uty.[name] IS NOT NULL
                    AND sty.[name] IS NOT NULL
                    THEN '
                ) '
                ELSE ''
                END + '</td><td>' + CASE
                WHEN c.is_nullable = 1
                    THEN 'Y'
                ELSE ''
                END + '</td><td>' AS html1
            ,CASE
                WHEN c.is_identity = 1
                    THEN 'Y'
                ELSE ''
                END + '</td><td>' + ISNULL(dc.[definition], '') + '</td></tr>' AS html2
        FROM sys.columns AS c
        INNER JOIN sys.schemas s ON s.[schema_id] = @schema_id
        -- get name of user data type
        LEFT OUTER JOIN sys.types uty ON uty.system_type_id = c.system_type_id
            AND uty.user_type_id = c.user_type_id
            AND c.user_type_id <> c.system_type_id
        -- get name of system data type
        LEFT OUTER JOIN sys.types sty ON sty.system_type_id = c.system_type_id
            AND sty.user_type_id = c.system_type_id
        -- get description of column, if available
        LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = @object_id
            AND ep.minor_id = c.column_id
            AND ep.[name] = 'MS_Description'
            AND ep.[class] = 1
        -- get default' s code TEXT
        LEFT OUTER JOIN sys.default_constraints dc ON dc.parent_object_id = @object_id
            AND dc.parent_column_id = c.column_id
        -- check for inclusion in primary key
        LEFT OUTER JOIN (
            SELECT ic.column_id
                ,i.[object_id]
            FROM sys.indexes i
            INNER JOIN sys.index_columns ic ON ic.index_id = i.index_id
                AND ic.[object_id] = i.[object_id]
            WHERE i.is_primary_key = 1
            ) pk ON pk.column_id = c.column_id
            AND pk.[object_id] = @object_id
        -- check for inclusion in foreign key
        LEFT OUTER JOIN (
            SELECT CASE
                    WHEN s.[name] = 'dbo'
                        THEN pk.[name]
                    ELSE s.[name] + '.' + pk.[name]
                    END AS primary_table
                ,pkc.[name] AS primary_column
                ,fkc.parent_object_id
                ,fkc.parent_column_id
            FROM sys.foreign_keys fk
            INNER JOIN sys.tables pk ON fk.referenced_object_id = pk.[object_id]
            INNER JOIN sys.schemas s ON s.[schema_id] = pk.[schema_id]
            INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.[object_id]
                AND fkc.referenced_object_id = pk.[object_id]
            INNER JOIN sys.columns pkc ON pkc.[object_id] = pk.[object_id]
                AND pkc.column_id = fkc.referenced_column_id
            ) fk ON fk.parent_object_id = @object_id
            AND fk.parent_column_id = c.column_id
        WHERE c.object_id = @object_id
        AND NOT EXISTS (
                SELECT *
                FROM sys.extended_properties ms
                WHERE ms.major_id = @object_id
                    AND ms.minor_id = 0
                    AND ms.class = 1
                    AND ms.[name] = 'microsoft_database_tools_support'
                )
    
        UNION ALL
    
        SELECT '' AS html
            ,'' AS html1
            ,'</tbody></table><br><hr><br>' AS html2
    
    
           FETCH NEXT
           FROM @record
           INTO @table, @object_id, @schema_id
    END
    
    CLOSE @record
    
    DEALLOCATE @record
    
    PRINT '<a href="https://pitt.plusmagi.com">PlusMagi.com by Pitt Phunsanit ([email protected])</a>';
    PRINT '</body>';
    PRINT '</html>';
    
  3. คลิกปุ่ม Results to file หรือ Ctrl + Shift + F กด Excute หรือ F5 แล้วเซฟเป็นไฟล์ ที่ต้องการแต่ลงท้ายด้วย .html เพราะว่าผลลัพธ์ที่ได้จะเป็นเว็บครับ
  4. เปิดไฟล์ที่ส่งออกออกมาแล้วคัดลอกใส่โปรแกรมเวิร์ดหรือเอ็กเซลได้เลยครับ

ตัวอย่างผลงานที่เร็วกว่ามาม่า

test

create date : 2016/07/14

—————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————-

areas_cities
Primary Key Foreign key Column Name Description Data Type Allow Nulls Identity Default Value
PK city_id int
status tinyint Y
geo_id ภูมิภาค int ((0))
province_id int ((0))
amphur_code varchar (
4
)
Y


—————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————-

areas_cities_lang
Primary Key Foreign key Column Name Description Data Type Allow Nulls Identity Default Value
PK areas_cities.city_id city_id int
PK language char (
2
)
name nvarchar (
600
)


PlusMagi.com by Pitt Phunsanit ([email protected])
ดูไม่สวย แต่จริงๆ มันมีสีสันด้วยนะครับ ไม่ใช่ตารางโล้นๆ

เวลานำไปให้ให้เครดิตผมด้วยนะครับ ถ้าหากเจอข้อบกพร่องแจ้งผมด้วยนะครับ จะได้ปรับปรุงให้คนอื่นใช้กันต่อไป “สังคมแห่งการแบ่งปันครับ”