php – 使用Datatables v1.10.0进行服务器端处理

嗨,我有一些问题,使数据表的服务器端处理功能与sql Server一起使用.

我有一个测试页面应该显示数据库表中的两列(现在).

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
    <link rel="Stylesheet" type="text/css" href="DataTables-1.10.0/media/css/jquery.dataTables.min.css" />
</head>
<body>
<table id="example" class="display" cellspacing="0" width="100%">
<thead>
    <tr>
            <th align="center">PK</th>
            <th align="center">Network</th>               
    </tr>
</thead>

<tfoot>
    <tr>
            <th align="center">PK</th>
            <th align="center">Network</th>               
    </tr>
</tfoot>
</table>
</body>
<script type="text/javascript" src="DataTables-1.10.0/media/js/jquery.js"></script>
<script type="text/javascript" src="DataTables-1.10.0/media/js/jquery.dataTables.min.js">

</script>
<script type="text/javascript" charset="utf-8">
$(document).ready(function () {
    $('#example').dataTable({
        "processing": true,"bServerSide": true,"ajax": "PHP/testGetArchive.PHP"
    });
});
</script>

</html>

我正在使用网站上的示例代码这里为服务器端功能：

http://next.datatables.net/examples/server_side/simple.html

这是我调用的PHP 页面的版本：

<?PHP

/*
 * DataTables example server-side processing script.
 *
 * Please note that this script is intentionally extremely simply to show how
 * server-side processing can be implemented,and probably shouldn't be used as
 * the basis for a large complex system. It is suitable for simple use cases as
 * for learning.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */

// DB table to use
$table = 'tblViews';

// Table's primary key
$primaryKey = 'PK';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database,while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array( 'db' => 'PK','dt' => 0 ),array( 'db' => 'Network','dt' => 1 )
);

// sql server connection information
$sql_details = array(
    'user' => '******','pass' => '******','db'   => '******db','host' => '******\sqlEXPRESS'
);


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side,there is no need to edit below this line.
 */

require( 'ssp.class.PHP' );

echo json_encode(
    SSP::simple( $_GET,$sql_details,$table,$primaryKey,$columns )
);

然后调用这里的第二个PHP示例：

https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/ssp.class.php

这是我的副本.我执行的唯一修改是删除示例所需的代码块.

<?PHP

/*
 * Helper functions for building a DataTables server-side processing sql query
 *
 * The static functions in this class are just helper functions to help build
 * the sql used in the DataTables demo server-side processing scripts. These
 * functions obvIoUsly do not represent all that can be done with server-side
 * processing,they are intentionally simple to show how it works. More complex
 * server-side processing operations will likely require a custom script.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */

class SSP {
    /**
     * Create the data output array for the DataTables rows
     *
     *  @param  array $columns Column information array
     *  @param  array $data    Data from the sql get
     *  @return array          Formatted data in a row based format
     */
    static function data_output ( $columns,$data )
    {
        $out = array();

        for ( $i=0,$ien=count($data) ; $i<$ien ; $i++ ) {
            $row = array();

            for ( $j=0,$jen=count($columns) ; $j<$jen ; $j++ ) {
                $column = $columns[$j];

                // Is there a formatter?
                if ( isset( $column['formatter'] ) ) {
                    $row[ $column['dt'] ] = $column['formatter']( $data[$i][ $column['db'] ],$data[$i] );
                }
                else {
                    $row[ $column['dt'] ] = $data[$i][ $columns[$j]['db'] ];
                }
            }

            $out[] = $row;
        }

        return $out;
    }


    /**
     * Paging
     *
     * Construct the LIMIT clause for server-side processing sql query
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  array $columns Column information array
     *  @return string sql limit clause
     */
    static function limit ( $request,$columns )
    {
        $limit = '';

        if ( isset($request['start']) && $request['length'] != -1 ) {
            $limit = "LIMIT ".intval($request['start']).",".intval($request['length']);
        }

        return $limit;
    }


    /**
     * Ordering
     *
     * Construct the ORDER BY clause for server-side processing sql query
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  array $columns Column information array
     *  @return string sql order by clause
     */
    static function order ( $request,$columns )
    {
        $order = '';

        if ( isset($request['order']) && count($request['order']) ) {
            $orderBy = array();
            $dtColumns = SSP::pluck( $columns,'dt' );

            for ( $i=0,$ien=count($request['order']) ; $i<$ien ; $i++ ) {
                // Convert the column index into the column data property
                $columnIdx = intval($request['order'][$i]['column']);
                $requestColumn = $request['columns'][$columnIdx];

                $columnIdx = array_search( $requestColumn['data'],$dtColumns );
                $column = $columns[ $columnIdx ];

                if ( $requestColumn['orderable'] == 'true' ) {
                    $dir = $request['order'][$i]['dir'] === 'asc' ?
                        'ASC' :
                        'DESC';

                    $orderBy[] = '`'.$column['db'].'` '.$dir;
                }
            }

            $order = 'ORDER BY '.implode(',',$orderBy);
        }

        return $order;
    }


    /**
     * Searching / Filtering
     *
     * Construct the WHERE clause for server-side processing sql query.
     *
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here performance on large
     * databases would be very poor
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  array $columns Column information array
     *  @param  array $bindings Array of values for PDO bindings,used in the
     *    sql_exec() function
     *  @return string sql where clause
     */
    static function filter ( $request,$columns,&$bindings )
    {
        $globalSearch = array();
        $columnSearch = array();
        $dtColumns = SSP::pluck( $columns,'dt' );

        if ( isset($request['search']) && $request['search']['value'] != '' ) {
            $str = $request['search']['value'];

            for ( $i=0,$ien=count($request['columns']) ; $i<$ien ; $i++ ) {
                $requestColumn = $request['columns'][$i];
                $columnIdx = array_search( $requestColumn['data'],$dtColumns );
                $column = $columns[ $columnIdx ];

                if ( $requestColumn['searchable'] == 'true' ) {
                    $binding = SSP::bind( $bindings,'%'.$str.'%',PDO::PARAM_STR );
                    $globalSearch[] = "`".$column['db']."` LIKE ".$binding;
                }
            }
        }

        // Individual column filtering
        for ( $i=0,$ien=count($request['columns']) ; $i<$ien ; $i++ ) {
            $requestColumn = $request['columns'][$i];
            $columnIdx = array_search( $requestColumn['data'],$dtColumns );
            $column = $columns[ $columnIdx ];

            $str = $requestColumn['search']['value'];

            if ( $requestColumn['searchable'] == 'true' &&
             $str != '' ) {
                $binding = SSP::bind( $bindings,PDO::PARAM_STR );
                $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
            }
        }

        // Combine the filters into a single string
        $where = '';

        if ( count( $globalSearch ) ) {
            $where = '('.implode(' OR ',$globalSearch).')';
        }

        if ( count( $columnSearch ) ) {
            $where = $where === '' ?
                implode(' AND ',$columnSearch) :
                $where .' AND '. implode(' AND ',$columnSearch);
        }

        if ( $where !== '' ) {
            $where = 'WHERE '.$where;
        }

        return $where;
    }


    /**
     * Perform the sql queries needed for an server-side processing requested,* utilising the helper functions of this class,limit(),order() and
     * filter() among others. The returned array is ready to be encoded as JSON
     * in response to an SSP request,or can be modified if needed before
     * sending back to the client.
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  array $sql_details sql connection details - see sql_connect()
     *  @param  string $table sql table to query
     *  @param  string $primaryKey Primary key of the table
     *  @param  array $columns Column information array
     *  @return array          Server-side processing response array
     */
    static function simple ( $request,$columns )
    {
        $bindings = array();
        $db = SSP::sql_connect( $sql_details );

        // Build the sql query string from the request
        $limit = SSP::limit( $request,$columns );
        $order = SSP::order( $request,$columns );
        $where = SSP::filter( $request,$bindings );

        // Main query to actually get the data
        $data = SSP::sql_exec( $db,$bindings,"SELECT sql_CALC_FOUND_ROWS `".implode("`,`",SSP::pluck($columns,'db'))."`
             FROM `$table`
             $where
             $order
             $limit"
        );

        // Data set length after filtering
        $resFilterLength = SSP::sql_exec( $db,"SELECT FOUND_ROWS()"
        );
        $recordsFiltered = $resFilterLength[0][0];

        // Total data set length
        $resTotalLength = SSP::sql_exec( $db,"SELECT COUNT(`{$primaryKey}`)
             FROM   `$table`"
        );
        $recordsTotal = $resTotalLength[0][0];


        /*
         * Output
         */
        return array(
            "draw"            => intval( $request['draw'] ),"recordsTotal"    => intval( $recordsTotal ),"recordsFiltered" => intval( $recordsFiltered ),"data"            => SSP::data_output( $columns,$data )
        );
    }


    /**
     * Connect to the database
     *
     * @param  array $sql_details sql server connection details array,with the
     *   properties:
     *     * host - host name
     *     * db   - database name
     *     * user - user name
     *     * pass - user password
     * @return resource Database connection handle
     */
    static function sql_connect ( $sql_details )
    {
        try {
            $db = @new PDO(
                "MysqL:host={$sql_details['host']};dbname={$sql_details['db']}",$sql_details['user'],$sql_details['pass'],array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )
            );
        }
        catch (PDOException $e) {
            SSP::fatal(
                "An error occurred while connecting to the database. ".
                "The error reported by the server was: ".$e->getMessage()
            );
        }

        return $db;
    }


    /**
     * Execute an sql query on the database
     *
     * @param  resource $db  Database handler
     * @param  array    $bindings Array of PDO binding values from bind() to be
     *   used for safely escaping strings. Note that this can be given as the
     *   sql query string if no bindings are required.
     * @param  string   $sql sql query to execute.
     * @return array         Result from the query (all rows)
     */
    static function sql_exec ( $db,$sql=null )
    {
        // Argument shifting
        if ( $sql === null ) {
            $sql = $bindings;
        }

        $stmt = $db->prepare( $sql );
        //echo $sql;

        // Bind parameters
        if ( is_array( $bindings ) ) {
            for ( $i=0,$ien=count($bindings) ; $i<$ien ; $i++ ) {
                $binding = $bindings[$i];
                $stmt->bindValue( $binding['key'],$binding['val'],$binding['type'] );
            }
        }

        // Execute
        try {
            $stmt->execute();
        }
        catch (PDOException $e) {
            SSP::fatal( "An sql error occurred: ".$e->getMessage() );
        }

        // Return all
        return $stmt->fetchAll();
    }


    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Internal methods
     */

    /**
     * Throw a fatal error.
     *
     * This writes out an error message in a JSON string which DataTables will
     * see and show to the user in the browser.
     *
     * @param  string $msg Message to send to the client
     */
    static function fatal ( $msg )
    {
        echo json_encode( array( 
            "error" => $msg
        ) );

        exit(0);
    }

    /**
     * Create a PDO binding key which can be used for escaping variables safely
     * when executing a query with sql_exec()
     *
     * @param  array &$a    Array of bindings
     * @param  *      $val  Value to bind
     * @param  int    $type PDO field type
     * @return string       Bound key to be used in the sql where this parameter
     *   would be used.
     */
    static function bind ( &$a,$val,$type )
    {
        $key = ':binding_'.count( $a );

        $a[] = array(
            'key' => $key,'val' => $val,'type' => $type
        );

        return $key;
    }


    /**
     * Pull a particular property from each assoc. array in a numeric array,* returning and array of the property values from each item.
     *
     *  @param  array  $a    Array to get data from
     *  @param  string $prop Property to read
     *  @return array        Array of property values
     */
    static function pluck ( $a,$prop )
    {
        $out = array();

        for ( $i=0,$len=count($a) ; $i<$len ; $i++ ) {
            $out[] = $a[$i][$prop];
        }

        return $out;
    }
}

我一直收到一个错误,说代码找不到驱动程序虽然我已经安装了sqlserv和pdo_sqlsrv驱动程序但我的PHP环境.导致此错误的代码是否有问题？我的司机不正确吗？任何帮助都将不胜感激.我有超过65,000行数据要处理,并且一次性将所有数据发送到客户端是不可能的.

我花了一段时间但是我发现了我出错的地方,现在DataTables通过服务器端脚本使用sql Server.我发布这个解决方案,希望它能帮助像我这样有问题的人.我把我的答案分成几部分.

PHP环境

可以在here找到用于PHP的sqlSRV驱动程序.下载sqlSRV30.EXE安装程序包.您可能会发现,当您尝试运行此可执行文件时,您会收到错误“这不是一个有效的win32应用程序”如果是这种情况,请使用7-zip解压缩可执行文件.生成的文件将包含您需要的文件.

解压缩包后,需要选择正确的驱动程序.大多数Windows安装使用非线程安全驱动程序,它们是：

PHP版本5.3：

PHP_sqlsrv_53_nts.dll

PHP_pdo_sqlsrv_53_nts.dll

PHP版本5.4：

PHP_sqlsrv_54_nts.dll

PHP_pdo_sqlsrv_54_nts.dll

将相应的文件复制到PHP目录中的ext文件夹.现在修改您的PHP.ini文件以引用这些文件.通过在动态扩展部分下添加条目来执行此操作.结果将是这样的：

extension=PHP_sqlsrv_54_nts.dll

然后在模块部分设置下为驱动程序添加一个部分,如下所示：

[sqlsrv]
sqlsrv.LogSubSystems=-1
sqlsrv.LogSeverity=-1
sqlsrv.WarningsReturnAsErrors=0

这些设置的文档可以在here找到.

添加这些驱动程序并在PHP.ini文件中添加对它们的引用后,还必须确保还安装了Microsoft sql Server Client Profile 2012.

These Links have been taken from the PHP.net website:

Microsoft SQL Server Client Profile 2012 x86
Microsoft SQL Server Client profile 2012 x64

执行这些步骤后,请重新启动Web服务器.现在应该安装驱动程序并准备使用.您可以使用info.PHP 页面进行检查.

服务器端脚本：

现在Web服务器已配置为使用sql SRV驱动程序,我们现在可以使用它来查询 sql Server数据库.我使用了here提供的服务器端脚本.以下是我发现的一些问题：

<?PHP
    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "";

    /* DB table to use */
    $sTable = "";

    /* Database connection information */
    $gasql['user']       = "";
    $gasql['password']   = "";
    $gasql['db']         = "";
    $gasql['server']     = "";

    /*
    * Columns
    * If you don't want all of the columns displayed you need to hardcode $aColumns array with your elements.
    * If not this will grab all the columns associated with $sTable
    */
    $aColumns = array();


    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * If you just want to use the basic configuration for DataTables with PHP server-side,there is
     * no need to edit below this line
     */

    /*
     * ODBC connection
     */
    $connectionInfo = array("UID" => $gasql['user'],"PWD" => $gasql['password'],"Database"=>$gasql['db'],"ReturnDatesAsStrings"=>true);
    $gasql['link'] = sqlsrv_connect( $gasql['server'],$connectionInfo);
    $params = array();
    $options =  array( "Scrollable" => sqlSRV_CURSOR_KEYSET );


    /* Ordering */
    $sOrder = "";
    if ( isset( $_GET['iSortCol_0'] ) ) {
        $sOrder = "ORDER BY  ";
        for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ ) {
            if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" ) {
                $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                    ".addslashes( $_GET['sSortDir_'.$i] ) .",";
            }
        }
        $sOrder = substr_replace( $sOrder,"",-2 );
        if ( $sOrder == "ORDER BY" ) {
            $sOrder = "";
        }
    }

    /* Filtering */
    $sWhere = "";
    if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
            $sWhere .= $aColumns[$i]." LIKE '%".addslashes( $_GET['sSearch'] )."%' OR ";
        }
        $sWhere = substr_replace( $sWhere,-3 );
        $sWhere .= ')';
    }
    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
        if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )  {
            if ( $sWhere == "" ) {
                $sWhere = "WHERE ";
            } else {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumns[$i]." LIKE '%".addslashes($_GET['sSearch_'.$i])."%' ";
        }
    }

    /* Paging */
    $top = (isset($_GET['iDisplayStart']))?((int)$_GET['iDisplayStart']):0 ;
    $limit = (isset($_GET['iDisplayLength']))?((int)$_GET['iDisplayLength'] ):10;
    $sQuery = "SELECT TOP $limit ".implode(",",$aColumns)."
        FROM $sTable
        $sWhere ".(($sWhere=="")?" WHERE ":" AND ")." $sIndexColumn NOT IN
        (
            SELECT $sIndexColumn FROM
            (
                SELECT TOP $top ".implode(",$aColumns)."
                FROM $sTable
                $sWhere
                $sOrder
            )
            as [virtTable]
        )
        $sOrder";

    $rResult = sqlsrv_query($gasql['link'],$sQuery) or die("$sQuery: " . sqlsrv_errors());

    $sQueryCnt = "SELECT * FROM $sTable $sWhere";
    $rResultCnt = sqlsrv_query( $gasql['link'],$sQueryCnt,$params,$options) or die (" $sQueryCnt: " . sqlsrv_errors());
    $iFilteredTotal = sqlsrv_num_rows( $rResultCnt );

    $sQuery = " SELECT * FROM $sTable ";
    $rResultTotal = sqlsrv_query( $gasql['link'],$sQuery,$options) or die(sqlsrv_errors());
    $iTotal = sqlsrv_num_rows( $rResultTotal );

    $output = array(
        "sEcho" => intval($_GET['sEcho']),"iTotalRecords" => $iTotal,"iTotalDisplayRecords" => $iFilteredTotal,"aaData" => array()
    );

    while ( $aRow = sqlsrv_fetch_array( $rResult ) ) {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
            if ( $aColumns[$i] != ' ' ) {
                $v = $aRow[ $aColumns[$i] ];
                $v = mb_check_encoding($v,'UTF-8') ? $v : utf8_encode($v);
                $row[]=$v;
            }
        }
        If (!empty($row)) { $output['aaData'][] = $row; }
    }   
    echo json_encode( $output );
?>

索引列

指定要用于搜索的索引列时,请确保它包含在列数组中！如果在指定要使用哪些列时将其遗漏,则分页将无效.使用此代码对数据表的分页工作是在不在另一个查询的TOP X结果中时执行所有主键的选择查询.

连接参数

确保连接参数完整且正确.这些是允许脚本连接到数据库所必需的.如果没有任何参数或参数对sql服务器登录不正确,则脚本将永远无法连接到数据库.

列数组

我发现使用没有指定列的代码返回错误或NULL数据.阻止这种情况的最好方法是使用我想要选择的列名填充数组,每个列都用引号括起来并用逗号分隔.它还有理由说为什么要将除了所需数据之外的任何内容发送回客户端？

客户端

HTML

DataTables需要一个格式良好的html表来运行.这意味着拥有一个包含完整标签的表格.如果所有标记都没有返回,那么DataTables将返回错误.如果您要返回但未显示的列,则可以使用ColVis exntension并在java脚本中设置默认列视图设置.

Datatable使用自己的CCS文件,因此请确保包含它！

java脚本

DataTables使用Jquery及其自己的Javascrpt文件,因此请确保在脚本标记中包含对它们的引用！

//Datatables Basic server side initilization
$(document).ready(function () {

    //Datatable
    var table = $('#tableID').DataTable({
        "bProcessing": true,"sAjaxSource": "serverSideScript.PHP"
    });    
});

这些是此服务器端脚本工作所需的基本功能.它将使用您在PHP 页面中指定的数据库参数获得初始绘制中的前10行.在这里,您可以添加所需的扩展名,例如ColVis和TableTools.可以在here找到这些扩展的完整文档和数据表的其他初始化选项.

我希望这个答案可以帮助那些遇到同样问题的人.

php – 使用Datatables v1.10.0进行服务器端处理

猜你在找的PHP相关文章