DataTables Part2 -Server-side processing-
Part1では説明は概要とデフォルト機能のみであったが、Demoのデータは実はTableにベタ書きではなく、DB上から読み込んでいた。今回はそのやり方についてのお話。
実際に使用する場合、 数件程度ならベタ書きでも十分だが、数百件以上になってくるとPHP等でサーバーからデータを読み込んでテーブルを作るやり方の方が実用的だ。
今回利用するテストデータは、以前「MySQLのサンプルデータベースをインポート」で紹介したものを利用する。
まず、通常のHTMLファイルを用意する。データはSQLから呼び出すのでTableの構成はテーブルヘッダのみ記述する。
<table id="example" class="display" width="100%" cellspacing="0">
<thead>
<tr>
<th>CountryCode</th>
<th>Language</th>
<th>IsOfficial</th>
<th>Percentage</th>
</tr>
</thead>
</table>
次に、DataTablesのjQueryを記述しているjavascriptを編集する。Part1で説明したものに、今回は”serverSide”と”ajax”の2行を追加。また、件数切替(lengthMenu)で0未満にすると全件表示するとPart1では説明したが、serverSideではどうも機能しないようなので十分多い件数を設定しておくことで全件表示に見せかけている。
$(document).ready(function(){
$('#example').DataTable( {
/**
* Part1で説明済み
*/
dom: '<"top"f>rt<"bottom"lip><"clear">',
lengthMenu: [[10, 50, 100, 500, 9999], [10, 50, 100, 500, "全"]],
language: {
url: "https://cdn.datatables.net/plug-ins/1.10.15/i18n/Japanese.json"
},
/**
* サーバー側でテーブルを作成する場合、true
*/
serverSide: true,
/**
* JSON datasource
* MySQLから呼び出したデータをJSON形式で出力
*/
ajax: "datatables_griddata1.php",
} );
});
更に上記で呼び出している”datatables_griddata1.php”を用意。長いのでソースを折りたたんでいるため、見る場合は下記をクリック。
<?php
session_start();
header('Expires:-1');
header('Cache-Control:');
header('Pragma:');
try {
/**
* Connect to MySQL
*/
require_once 'pdo.php';
$pdo = get_pdo(); //PDOを利用してMySQLへ接続する自作関数
$requestData= $_REQUEST;
/**
* Datatables column index => Database column name
*/
$columns = array(
0 => 'CountryCode',
1 => 'Language',
2 => 'IsOfficial',
3 => 'Percentage',
);
/**
* Number of all results
* @totalData 全○件の表示に使用
* @totalFiltered フィルタリング後の件数だがここでは全件と同じ数で初期化
*/
$sql_cnt = "SELECT count(*) as reccnt FROM countrylanguage";
$stmt_cnt = $pdo->query($sql_cnt);
$totalData = $stmt_cnt->fetchColumn();
$totalFiltered = $totalData;
/**
* SQL for filtering
* @sql_filtering 検索窓からフィルタリングした際に利用するSQL部
*/
$sql_filtering = "";
if (!empty($requestData['search']['value'])) {
$sql_filtering = " WHERE (CountryCode LIKE :col0 ESCAPE '!'
OR Language LIKE :col1 ESCAPE '!'
OR IsOfficial LIKE :col2 ESCAPE '!'
OR Percentage LIKE :col3 ESCAPE '!') ";
}
/**
* Number of filtering results
* @sql_filtered_cnt フィルタリング後の件数カウント用SQL
* @totalFiltered フィルタリング後の件数
*/
$sql_filtered_cnt = "SELECT count(*) as reccnt FROM countrylanguage {$sql_filtering}";
if (!empty($sql_filtering)) {
$stmt_num = $pdo->prepare($sql_filtered_cnt);
$stmt_num->bindValue(col0, '%' . preg_replace('/(?=[!_%])/', '!', $requestData['search']['value']) . '%');
$stmt_num->bindValue(col1, '%' . preg_replace('/(?=[!_%])/', '!', $requestData['search']['value']) . '%');
$stmt_num->bindValue(col2, '%' . preg_replace('/(?=[!_%])/', '!', $requestData['search']['value']) . '%');
$stmt_num->bindValue(col3, '%' . preg_replace('/(?=[!_%])/', '!', $requestData['search']['value']) . '%');
$stmt_num->execute();
} else {
$stmt_num = $pdo->query($sql_filtered_cnt);
}
$totalFiltered = $stmt_num->fetchColumn();
/**
* Order
* @order_col SQLのOrder byで使用。並び替えするカラム名。
* @order_dir SQLのOrder byで使用。ASC or DESC。
*/
if (isset($columns[$requestData['order'][0]['column']])) {
$order_col = $columns[$requestData['order'][0]['column']];
} else {
$order_col = $columns[0];
}
if (strtoupper($requestData['order'][0]['dir']) === 'ASC'
|| strtoupper($requestData['order'][0]['dir']) === 'DESC') {
$order_dir = $requestData['order'][0]['dir'];
} else {
$order_dir = 'ASC';
}
/**
* Refining search results
* フィルタリングや並び順決定後のテーブルデータを作成
*/
$sql = "SELECT CountryCode, Language, IsOfficial, Percentage
FROM countrylanguage {$sql_filtering}
ORDER BY $order_col $order_dir
LIMIT :st, :len";
$stmt = $pdo->prepare($sql);
if (!empty($sql_filtering)) {
$stmt->bindValue(col0, '%' . preg_replace('/(?=[!_%])/', '!', $requestData['search']['value']) . '%');
$stmt->bindValue(col1, '%' . preg_replace('/(?=[!_%])/', '!', $requestData['search']['value']) . '%');
$stmt->bindValue(col2, '%' . preg_replace('/(?=[!_%])/', '!', $requestData['search']['value']) . '%');
$stmt->bindValue(col3, '%' . preg_replace('/(?=[!_%])/', '!', $requestData['search']['value']) . '%');
}
$stmt->bindValue(st, (int)$requestData['start'], PDO::PARAM_INT);
$stmt->bindValue(len, (int)$requestData['length'], PDO::PARAM_INT);
$stmt->execute();
/**
* Create data array
*/
$data = array();
foreach($stmt as $row) {
$nestedData = array();
$nestedData[] = $row["CountryCode"];
$nestedData[] = $row["Language"];
$nestedData[] = $row["IsOfficial"];
$nestedData[] = $row["Percentage"];
$data[] = $nestedData;
}
/**
* json_data
*/
$json_data = array(
"draw" => intval( $requestData['draw'] ),
"recordsTotal" => intval( $totalData ),
"recordsFiltered" => intval( $totalFiltered ),
"data" => $data
);
echo json_encode($json_data);
} catch (PDOException $e) {
// Exception message
header('Content-Type: text/plain; charset=UTF-8', true, 500);
exit($e->getMessage());
}
こんな感じで前回示したDemoのようなテーブルが出来上がる。