ตัวอย่างการใช้งาน datatables js และ ssp.customized.class.php

example-sql.sql

SELECT 
student_id,
first_name,
last_name,
CONCAT(first_name,' ',last_name) AS full_name,
FROM person p
INNER JOIN class_room cr
ON p.student_id = cr.student_id
WHERE
cr.class_room_name = '6/1'
AND cr.class_room_builder = 3

init-example-table.js

$(document).ready(function() {
    $('#example').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "server-side-processing.php",
            "type": "POST"
        },
      "columnDefs": [
      { 
        "targets": [3],
        "visible": true,
        "searchable": false
      }
      ]
    });
});

server-side-processing.php

<?php
$table = 'person';
$primaryKey = 'student_id';

$columns = array();
$columns[] = array('db' => 'p.student_id', 'dt' => 0, 'field' => 'student_id');
$columns[] = array('db' => 'p.first_name', 'dt' => 0, 'field' => 'first_name');
$columns[] = array('db' => 'p.last_name', 'dt' => 0, 'field' => 'last_name');
$columns[] = array('db' => 'CONCAT(first_name,' ',last_name)', 'dt' => 0, 'field' => 'full_name', 'AS' => 'full_nmae');

$sql_details = array(
'user' => 'root',
'pass' => '',
'db' => 'my_db',
'host' => 'localhost'
);

$joinQuery = "FROM person p
INNER JOIN class_room cr
ON p.student_id = cr.student_id
";

$extraWhere = "cr.class_room_name = '6/1'
AND cr.class_room_builder = 3
";

require('ssp.customized.class.php');

echo json_encode(
	SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere)
);
?>

view-datatables.html

<table id="example" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>ID</th>
                <th>First name</th>
                <th>Last name</th>
                <th>Full name</th>
                <th>Class room</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>ID</th>
                <th>First name</th>
                <th>Last name</th>
                <th>Full name</th>
                <th>Class room</th>
            </tr>
        </tfoot>
</table>

<script src="init-example-table.js"></script>