Tag Archive pagination

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

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

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

Codeigniter reused active record query

ในการเขียน query บางครั้งก็ต้องเขียนคิวรี่ที่ต่างกันเล็กๆ น้อยๆ เช่น ต้องแบ่ง list ข้อมูลออกเป็นหน้าๆ หรือที่เรียกกันว่า pagination จะมี 2 query ที่ต้องใช้คือ query ที่นับ ข้อมูลที่มีทั้งหมด

SELECT `d`.`district_id`,
       `d`.`enable`,
       `d`.`district_code`,
       `d`.`district_name`,
       `p`.`province_name`
FROM   `district` AS `d`
       LEFT JOIN `province` AS `p`
              ON `d`.`province_id` = `p`.`province_id`

และ query ที่ดึงข้อมูลเฉพาะช่วงที่กำหนด

SELECT `d`.`district_id`,
       `d`.`enable`,
       `d`.`district_code`,
       `d`.`district_name`,
       `p`.`province_name`
FROM   `district` AS `d`
       LEFT JOIN `province` AS `p`
              ON `d`.`province_id` = `p`.`province_id`
LIMIT  0, 10

จะเห็นว่า ต่างกันแค่ LIMIT 0, 10 เท่านั้น แต่เพราะว่า ไม่ควร query ข้อมูล ที่ไม่ได้ใช้ จึงเปลี่ยน

SELECT COUNT(district_id) AS recordsTotal
FROM   `district` AS `d`
       LEFT JOIN `province` AS `p`
              ON `d`.`province_id` = `p`.`province_id`

แก้ select ให้เป็น SELECT COUNT(DISTRICT_ID) AS recordsTotal แทนที่จะเลือกหลายๆ column หลาย row มาเพื่อแค่จะนับว่ามีข้อมูลกี่ record แค่นั้นเอง

ยังยุ่งไม่พอ เมื่อทำเป็น data table หรือ grid จะมีเงื่อนไขอย่างเช่น filter ข้อมูล หรือ order ข้อมูลใหม่ตาม column ต่างๆ ที่ user ปลับเปลี่ยนเวลาใช้งาน ถ้าต้องเขียนเงื่อนใขเพิ่ม ถ้าเป็น query string อย่าง DISTRICT_ID = ‘xxx’ ก็พอจะเก็บในตัวแปรแล้วเอามาต่อกันได้ แต่ถ้าเขียนเป็น active record ก็ลำบากมากที่ต้องมาเขียนใหม่หลายๆจุด แต่ Codeigniter มีวิธี reused active record มาใช้ใหม่ได้ลดความซับซ้อนลงไปได้เยอะเลย

วิธีการคือเขียน query ส่วนที่ใช้ ร่วมกัน ระหว่าง ->start_cache(); และ ->stop_cache(); จากนั้นเขียน ส่วนอื่นเพิ่มเข้าไป เป็นวิธีที่ฉลาดมากๆ

<?php

class DatabasecachingModel extends CI_Model
{

    public function __construct()
    {
        // Call the CI_Model constructor
        parent::__construct();

        $this->db = $this->load->database('db', true);
    }

    public function getPagination()
    {
        $post = $this->input->post();

        // Turn caching on
        $this->db->start_cache();

        $this->db->from('district AS d');
        $this->db->join('province AS p', 'd.PROVINCE_ID = p.PROVINCE_ID', 'left');

        if (isset($post['DISTRICT_ID'])) {
            $this->db->where('DISTRICT_ID', $post['DISTRICT_ID']);
        }

        if (isset($post['PROVINCE_ID'])) {
            $this->db->where('PROVINCE_ID', $post['PROVINCE_ID']);
        }

        $this->db->stop_cache();

        $this->db->select('COUNT(DISTRICT_ID) AS recordsTotal');
        $recordsTotal = $this->db->get()->row()->recordsTotal;

        echo '<br><br> query string for count = ' . $this->db->last_query();
        echo '<br> records total = ' . $recordsTotal;

        $this->db->select('d.DISTRICT_ID, d.enable, d.DISTRICT_CODE, d.DISTRICT_NAME, p.province_NAME');
        $this->db->limit(10, 0);
        $results = $this->db->get()->result_array();

        echo '<br><br> query string for results = ' . $this->db->last_query();
        echo '<pre>', print_r($results, true), '<pre>';

    }

}