DataTables 1.10服务器端，表连接返回'null'值响应

我正在尝试修改DataTables 1.10（目前处于测试阶段的最新版本）的默认服务器端脚本，以允许表连接和自定义'WHERE'条件 . 'WHERE'条件正如我之前所做的那样正常工作，但是，我在使用表连接方面遇到了一些麻烦 . 我之前取得了一些进展（我不再在firebug中收到任何错误），但每行的所有列都返回一个null值作为响应 . 换句话说，我的表在页面上显示每行的所有空列 .

我已经尝试了数据表论坛，但没有得到太多运气 . 为简单起见，我没有包含所有内容，但也包括了服务器端的php处理脚本和ssp.class.php脚本 .

processing.php :

// DB table to use
$table = "`users`";

// Join condition
$myJoin = "LEFT JOIN `security` ON `users`.`user_id` = `security`.`user_id`";


// Table's primary key
$primaryKey = "`users`.`user_id`";

// 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' => '`security`.`settings_id`', 'dt' => 'settings_id' ),
    array( 'db' => '`users`.`user_id`', 'dt' => 'user_id' ),
    array( 'db' => '`users`.`username`', 'dt' => 'username' ),
    array( 'db' => '`users`.`computer_name`', 'dt' => 'computer_name' ),
    array( 'db' => '`security`.`disable_desktop`', 'dt' => 'disable_desktop' ),
    array( 'db' => '`security`.`disable_start`', 'dt' => 'disable_start' ),
    array( 'db' => '`security`.`disable_shutdown`', 'dt' => 'disable_shutdown' ),
    array( 'db' => '`security`.`disable_run`', 'dt' => 'disable_run' ),
    array( 'db' => '`security`.`disable_mouse`', 'dt' => 'disable_mouse' ),
    array( 'db' => '`security`.`disable_bootkeys`', 'dt' => 'disable_bootkeys' ),
    array( 'db' => '`security`.`disable_cp`', 'dt' => 'disable_cp' ),
    array( 'db' => '`security`.`disable_network`', 'dt' => 'disable_network' ),
    array( 'db' => '`security`.`disable_taskbar`', 'dt' => 'disable_taskbar' ),
    array( 'db' => '`security`.`disable_clock`', 'dt' => 'disable_clock' ),
    array( 'db' => '`security`.`disable_logoff`', 'dt' => 'disable_logoff' ),
    array( 'db' => '`security`.`disable_startchange`', 'dt' => 'disable_startchange' ),
    array( 'db' => '`security`.`disable_taskman`', 'dt' => 'disable_taskman' ),
    array( 'db' => '`security`.`disable_clipboard`', 'dt' => 'disable_clipboard' ),
    array( 'db' => '`security`.`disable_drives`', 'dt' => 'disable_drives' )
);

echo json_encode(
    SSP::simple( $_GET, $db, $table, $primaryKey, $columns, $myJoin, "")
    //SSP::simple( $_GET, $db, $table, $primaryKey, $columns, $myJoin, $myWhere)
);

ssp.class.php :

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 ( $primaryKey, $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
     EDIT : added $mywhere functionality for passing initial filtering conditions
     */
    static function filter ( $request, $columns, &$bindings, $myWhere )
    {
        $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, '%'.$str.'%', 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 ', $globalSearch) :
                $where .' AND '. implode(' AND ', $globalSearch);
        }

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

            // add my clause
            if ($myWhere !== '') {
                $where .= ' AND '.$myWhere;
            }
        }

        if ( $where == '' && $myWhere !== '') {
            // add my clause
            $where = 'WHERE '.$myWhere;
        }       

        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, $db, $table, $primaryKey, $columns, $myJoin, $myWhere )
    {
        $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, $columns, $bindings, $myWhere );    

        // 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
             $myJoin
             $where
             $order
             $limit"
        );

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

        //add my initial where clause for correct results
        $dataWhere = ($myWhere !== '' ? 'WHERE '.$myWhere : '');

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


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

    /**
     * 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, $bindings, $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;
    }
}

EDIT :

这是 SSP::data_output( $primaryKey, $columns, $data ) 的结果 . 我不得不将其打印到文件中，因为我无法在屏幕上打印/显示 . 从结果中可以明显看出，代码的数据部分出现问题，因为它没有返回任何内容，但是，它返回正确数量的结果（在本例中为5） . 请注意，命名是不同的，因为这些是返回到DataTables的名称，而不是实际的db列名称，但是，这可能与它有关 .

Array
(
    [0] => Array
        (
            [settings_id] => 
            [user_id] => 
            [username] => 
            [computer_name] => 
            [disable_desktop] => 
            [disable_start] => 
            [disable_shutdown] => 
            [disable_run] => 
            [disable_mouse] => 
            [disable_bootkeys] => 
            [disable_cp] => 
            [disable_network] => 
            [disable_taskbar] => 
            [disable_clock] => 
            [disable_logoff] => 
            [disable_startchange] => 
            [disable_taskman] => 
            [disable_clipboard] => 
            [disable_drives] => 
            [DT_RowId] => 
        )

    [1] => Array
        (
            [settings_id] => 
            [user_id] => 
            [username] => 
            [computer_name] => 
            [disable_desktop] => 
            [disable_start] => 
            [disable_shutdown] => 
            [disable_run] => 
            [disable_mouse] => 
            [disable_bootkeys] => 
            [disable_cp] => 
            [disable_network] => 
            [disable_taskbar] => 
            [disable_clock] => 
            [disable_logoff] => 
            [disable_startchange] => 
            [disable_taskman] => 
            [disable_clipboard] => 
            [disable_drives] => 
            [DT_RowId] => 
        )

    [2] => Array
        (
            [settings_id] => 
            [user_id] => 
            [username] => 
            [computer_name] => 
            [disable_desktop] => 
            [disable_start] => 
            [disable_shutdown] => 
            [disable_run] => 
            [disable_mouse] => 
            [disable_bootkeys] => 
            [disable_cp] => 
            [disable_network] => 
            [disable_taskbar] => 
            [disable_clock] => 
            [disable_logoff] => 
            [disable_startchange] => 
            [disable_taskman] => 
            [disable_clipboard] => 
            [disable_drives] => 
            [DT_RowId] => 
        )

    [3] => Array
        (
            [settings_id] => 
            [user_id] => 
            [username] => 
            [computer_name] => 
            [disable_desktop] => 
            [disable_start] => 
            [disable_shutdown] => 
            [disable_run] => 
            [disable_mouse] => 
            [disable_bootkeys] => 
            [disable_cp] => 
            [disable_network] => 
            [disable_taskbar] => 
            [disable_clock] => 
            [disable_logoff] => 
            [disable_startchange] => 
            [disable_taskman] => 
            [disable_clipboard] => 
            [disable_drives] => 
            [DT_RowId] => 
        )

    [4] => Array
        (
            [settings_id] => 
            [user_id] => 
            [username] => 
            [computer_name] => 
            [disable_desktop] => 
            [disable_start] => 
            [disable_shutdown] => 
            [disable_run] => 
            [disable_mouse] => 
            [disable_bootkeys] => 
            [disable_cp] => 
            [disable_network] => 
            [disable_taskbar] => 
            [disable_clock] => 
            [disable_logoff] => 
            [disable_startchange] => 
            [disable_taskman] => 
            [disable_clipboard] => 
            [disable_drives] => 
            [DT_RowId] => 
        )

)

更多信息 - 在下面显示的代码的这一点查看$ data将返回所有正确的值 .

$data = SSP::sql_exec( $db, $bindings,
    "SELECT SQL_CALC_FOUND_ROWS ".implode(", ", SSP::pluck($columns, 'db'))."
     FROM $table
     $myJoin
     $where
     $order
     $limit"
);

这是返回数组的一部分（一行），其中包含来自上面的正确值：

[3] => Array
    (
        [settings_id] => 1
        [0] => 1
        [user_id] => 11
        [1] => 11
        [username] => steve
        [2] => steve
        [computer_name] => TESTING
        [3] => TESTING
        [disable_desktop] => 1
        [4] => 1
        [disable_start] => 0
        [5] => 0
        [disable_shutdown] => 0
        [6] => 0
        [disable_run] => 0
        [7] => 0
        [disable_mouse] => 0
        [8] => 0
        [disable_bootkeys] => 0
        [9] => 0
        [disable_cp] => 0
        [10] => 0
        [disable_network] => 0
        [11] => 0
        [disable_taskbar] => 0
        [12] => 0
        [disable_clock] => 0
        [13] => 0
        [disable_logoff] => 0
        [14] => 0
        [disable_startchange] => 0
        [15] => 0
        [disable_taskman] => 0
        [16] => 0
        [disable_clipboard] => 0
        [17] => 0
        [disable_drives] => 0
        [18] => 0
    )

不完全确定这是否是正确的输出，但是，我相信它是因为您可以通过名称或索引引用数据表中的数据来解释此输出 .

EDIT:

我把问题进一步缩小了 . 静态函数data_output（$ primaryKey，$ columns，$ data）是预期的问题的一部分 . $ primaryKey和$ columns具有正确的值，但$ data不具有正确的值 .

在函数中，当您到达实际设置值的部分时，要设置的数组中没有值 .

$row[ $column['dt'] ] = $data[$i][ $columns[$j]['db'] ];

这里 $row[ $column['dt'] ] 是正确的，但 $data[$i][ $columns[$j]['db'] ]; 没有db值 . 正如您在上面的$ data输出示例中所见，db值不存在，而是将dt值与索引一起作为相同的值 . 通过将此更改为dt，我的表显示数据，但这会打破其他一些事情 . 我不确定为什么$ data不包括db值 .