jQueryのプラグインテーブル(jTableなど)とSQLite3+PHP連携 (その2)

前回に続いて、DataTablesプラグインを使ったデータ表示を行う。

高機能だが、Server-side processingはちょっと手間が掛かった。DataTablesで気に入ったのは、見た目がシンプルなのと標準でAjaxを使った検索機能が搭載されている。

jquery.dataTables.min.jsとthemeのcssを指定する

<script type="text/javascript" src="/pathto/jquery.dataTables.min.js"></script>
<link type="text/css" href="/pathto/jquery.dataTables.css" rel="stylesheet">

つづいてjavascript。いろんな方法がある。#example1htmlにハードコードされたデータをDataTables化する。#example2はサーバにおいてあるjson形式のファイルをロードする方法。#example3はGUIでのソート状態、検索キーワードをGETデータで受け取った後、DBからデータを取り出してjoson形式でレスポンス送信する方法。#example3が理想だったが、ソートや検索もSQLで行わないといけなくて手間がかかる。

<script type="text/javascript">
    function format ( d )
    {
        return 'id = '+d.Id+' '+' text = '+d.TextValue;
    }
    $(document).ready(function()
    {
         
        var dt = $('#example3').DataTable({
            "processing": true,
            "serverSide": true,
            "ajax" : "./sample3-processing.php",
            "columns": [
                {
                    "class":          'details-control',
                    "orderable":      false,
                    "data":           null,
                    "defaultContent": ''
                },
                { "data": "Id"},
                { "data": "TextValue" },
                { "data": "NumberValue" },
                { "data": "DateValue" },
                { "data": "ComboList" },
                { "data": "RadioButton" },
                { "data": "CheckBox" },
                { "data": "Comment" }
                 
            ]
        });
 
        var detailRows = [];
 
        $('#example3 tbody').on( 'click', 'tr td:first-child', function ()
        {
            var tr = $(this).closest('tr');
            var row = dt.row( tr );
            var idx = $.inArray( tr.attr('id'), detailRows );
      
            if ( row.child.isShown() )
            {
                tr.removeClass( 'details' );
                row.child.hide();
      
                // Remove from the 'open' array
                detailRows.splice( idx, 1 );
            }
            else
            {
                tr.addClass( 'details' );
                row.child( format( row.data() ) ).show();
      
                // Add to the 'open' array
                if ( idx === -1 ) {
                    detailRows.push( tr.attr('id') );
                }
            }
        } );
        // On each draw, loop over the `detailRows` array and show any child rows
        dt.on( 'draw', function () {
            $.each( detailRows, function ( i, id ) {
                $('#'+id+' td:first-child').trigger( 'click' );
            } );
        } );
 
    });
</script>

以下がサーバーサイドで実行されるPHPのサンプル。
公式サイトのサンプルなどを参考に作成。公式サイトのサンプルだと、スペースで区切った複数キーワードによる検索ができなかったので改良してある。

<!--?php
    include_once( "../lib/class.sqliteex.php" );
/*
    if( isset($_GET) )
    {
        error_log("[_GET]");
        error_log(print_r($_GET,true));
    }
 
    if( isset($_POST) )
    {
        error_log("[_POST]");
        error_log(print_r($_POST,true));
    }
 
*/
 
    // DBテーブル情報
    $tbl = "sampleTbl2";
    $db = "sample2.db";
    $primaryKey = "Id";
 
    $sqex = new SqLiteEx( $db, SQLITE3_OPEN_READONLY );
    $result = null;
    $count = 0;
 
    $sqex--->dbopen();
     
    // columns
    $columnsList = array();
    foreach( $_GET['columns'] as $column )
    {
        $columnsList[] = $column['data'];
    }
    error_log(print_r($columnsList, true ) );
 
    $orderList = array();
    if( isset( $_GET['order'] ) )
    {
        foreach( $_GET['order'] as $order_col )
        {
            if( $columnsList[ $order_col['column'] ] != "" )
            {
                $orderList[] = sprintf( "%s %s",
                    $_GET['columns'][ $order_col['column'] ][ 'data' ], $order_col['dir'] );
            }
        }
    }
 
    if( isset( $orderList ) && count( $orderList ) )
    {
        $ordertype = " order by ".implode( ", ", $orderList );
        error_log( "[order by]".$ordertype );
    }
    else
    {
        $ordertype = "";
    }
 
    // filtering
    $where = "";
 
    if( isset($_GET['search']['value']) && $_GET['search']['value'] != "" )
    {
        $searchkeys = array();
 
        $concat_columns = "";
         
        foreach( $columnsList as $column )
        {
            if( $column == "" )
            {
                continue;
            }
 
            if( $concat_columns != "" )
            {
                $concat_columns .= " || ' ' || ";
            }
            $concat_columns .= $column;
        }
        //error_log( $concat_columns );
 
        foreach ( explode( ' ', $_GET['search']['value'] ) as $word )
        {
            array_push( $searchkeys, $concat_columns . " like '%$word%'");
        }
        $where = ' where (' . implode(' and ', $searchkeys ) . ')';
        //error_log( $where );
    }
 
    // SQL
    $result = $sqex->query( 
        sprintf( "select * from %s %s %s limit %d , %d",
            $tbl, $where, $ordertype, $_GET["start"], $_GET["length"] )
        );
 
    $output = array(
        "draw" => intval($_GET['draw']),
        "recordsTotal" => 0,
        "recordsFiltered" => 0,
        "data" => array()
    );
    // SQLITE3_ASSOC,SQLITE3_NUM, SQLITE3_BOTH
    $res = $sqex->fetchArray( $result, SQLITE3_ASSOC );
    $conv_res = array();
    foreach( $res as $d )
    {
        $d["DT_RowId"] = "row_".$d["Id"];
        $conv_res[] = $d;
    }
    //error_log(print_r($conv_res,true));
    $output['data'] = $conv_res;
 
    // 全レコード数
    $output['recordsTotal'] = $sqex->querySingle(
        sprintf( "select count( %s ) from %s", $primaryKey, $tbl ), false );
    // WHERE句でフィルタリングされたレコード数
    $output['recordsFiltered'] = $sqex->querySingle(
        sprintf( "select count( %s ) from %s %s", $primaryKey, $tbl, $where ), false );
     
    $sqex->dbclose();
 
    $json_data = json_encode( $output );
    //error_log( print_r($json_data, true ) );
    print $json_data;
?>

example3のイメージ
datatables_server_side