使用DataTables添加复选框


Adding checkboxes with DataTables

我正在使用Datatables制作网页,我应该使用它来控制MySQL表(一项非常普通的任务)。有一件事我不知道为什么它不起作用。我想在包含复选框的表中添加一列,这样以后我就可以使用这些复选框和一些javascript代码对行进行一些操作。总之,这是我的代码:

index.html

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="description" content="">
<meta name="author" content="">
<title>Process manager</title>
<!-- Bootstrap core CSS -->
<link href="css/bootstrap/bootstrap.min.css" rel="stylesheet">
<!-- Custom styles for this template -->
<link href="css/bootstrap/navbar-static-top.css" rel="stylesheet">
<!-- DataTables bootstrap CSS -->
<link href="css/dataTables/dataTables.bootstrap.css" rel="stylesheet">
</head>
<body>
    <table id="processManager" class="table table-striped table-bordered">
        <thead>
            <tr>
                <th></th>
                <th>process</th>
                <th>Resource</th>
                <th>Category</th>
                <th>Format</th>
                <th>Automaticity</th>
                <th>Process_type</th>
                <th>Access</th>
                <th>Source</th>
                <th>A</th>
                <th>B</th>
                <th>status_A</th>
                <th>status_B</th>
                <th>time_A</th>
                <th>time_B</th>
                <th>exec_A</th>
                <th>exec_B</th>
                <th>period</th>
                <th>overtime</th>
                <th>param</th>
                <th>last_update</th>
                <th>last_triples</th>
                <th>error</th>
                <th>description</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th></th>
                <th>process</th>
                <th>Resource</th>
                <th>Category</th>
                <th>Format</th>
                <th>Automaticity</th>
                <th>Process_type</th>
                <th>Access</th>
                <th>Source</th>
                <th>A</th>
                <th>B</th>
                <th>status_A</th>
                <th>status_B</th>
                <th>time_A</th>
                <th>time_B</th>
                <th>exec_A</th>
                <th>exec_B</th>
                <th>period</th>
                <th>overtime</th>
                <th>param</th>
                <th>last_update</th>
                <th>last_triples</th>
                <th>error</th>
                <th>description</th>
            </tr>
        </tfoot>
    </table>
    <script src="js/jquery/jquery-1.11.1.min.js"></script>
    <script src="js/bootstrap/bootstrap.min.js"></script>
    <script src="js/dataTables/jquery-1.10.1.dataTables.min.js"></script>
    <script src="js/dataTables/dataTables.bootstrap.js"></script>
    <script>
    $(document).ready( function loadData() {
        processManager = $('#processManager').DataTable( {
            "ajax": {
                url: "getSingleTable.php",
            },
            "columns": [
            {
                "orderable": false,
                // Create an HTML select with all the versions of the data
                //TODO Correggi +01
                "data": function (row, type, val, meta) {
                    return  '<input type="checkbox"/>';
                }
            },
            { "data": "Process ID" },
            { "data": "Resource" },
            { "data": "Category" },
            { "data": "Format" },
            { "data": "Automaticity" },
            { "data": "Process type" },
            { "data": "Access" },
            { "data": "Source" },
            { "data": "A" },
            { "data": "B" },
            { "data": "Status A" },
            { "data": "Status B" },
            { "data": "Time A" },
            { "data": "Time B" },
            { "data": "Exec A" },
            { "data": "Exec B" },
            { "data": "Period" },
            { "data": "Overtime" },
            { "data": "Parameters" },
            { "data": "Last Update" },
            { "data": "Last Triples" },
            { "data": "Error" },
            { "data": "Description" }
            ],
            "language": {
                "decimal": ",",
                "thousands": "."
            },
            "lengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
            "order": [[0, 'asc']],
            "pagingType": "full_numbers",
            "processing": true,
            "serverSide": true,
            "stateSave": true
        } );
});
    </script>
</body>
</html>

getSingleTable.php

<?php
// DB table to use
// $table = 'process_manager';
$table = 'process_manager2';
// Table's primary key
// $primaryKey = 'Process';
$primaryKey = 'Process';
// 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' => 'process',
                'dt' => 'Process ID' 
        ),
        array (
                'db' => 'Resource',
                'dt' => 'Resource' 
        ),
        array (
                'db' => 'Category',
                'dt' => 'Category' 
        ),
        array (
                'db' => 'Format',
                'dt' => 'Format' 
        ),
        array (
                'db' => 'Automaticity',
                'dt' => 'Automaticity' 
        ),
        array (
                'db' => 'Process_type',
                'dt' => 'Process type' 
        ),
        array (
                'db' => 'Access',
                'dt' => 'Access' 
        ),
        array (
                'db' => 'Source',
                'dt' => 'Source' 
        ),
        array (
                'db' => 'A',
                'dt' => 'A' 
        ),
        array (
                'db' => 'B',
                'dt' => 'B' 
        ),
        array (
                'db' => 'status_A',
                'dt' => 'Status A' 
        ),
        array (
                'db' => 'status_B',
                'dt' => 'Status B' 
        ),
        array (
                'db' => 'time_A',
                'dt' => 'Time A' 
        ),
        array (
                'db' => 'time_B',
                'dt' => 'Time B' 
        ),
        array (
                'db' => 'exec_A',
                'dt' => 'Exec A' 
        ),
        array (
                'db' => 'exec_B',
                'dt' => 'Exec B' 
        ),
        array (
                'db' => 'period',
                'dt' => 'Period' 
        ),
        array (
                'db' => 'overtime',
                'dt' => 'Overtime' 
        ),
        array (
                'db' => 'param',
                'dt' => 'Parameters' 
        ),
        array (
                'db' => 'last_update',
                'dt' => 'Last Update' 
        ),
        array (
                'db' => 'last_triples',
                'dt' => 'Last Triples' 
        ),
        array (
                'db' => 'error',
                'dt' => 'Error' 
        ),
        array (
                'db' => 'description',
                'dt' => 'Description' 
        ) 
);
// SQL server connection information
$sql_details = array (
        'user' => 'USER',
        'pass' => 'PASS',
        'db' => 'DB',
        'host' => 'IP' 
);
require ('ssp.class.php');
echo json_encode ( SSP::simple ( $_GET, $sql_details, $table, $primaryKey, $columns ) );

当然,我给出的是真正的值,而不是"USER"、"PASS"、"DB"answers"IP"。如果我不把复选框放进去,一切都会好起来的。正如您所看到的,我正试图将复选框放在"进程"之前的第一列中,维奇应该是MySQL表中唯一不存在的列。有人知道怎么了吗?

您应该将函数放入render参数,而不是data参数。data用于一个您不需要的列名。

      "orderable": false,
            // Create an HTML select with all the versions of the data
            //TODO Correggi +01
            "render": function (row, type, val, meta) {
                return  '<input type="checkbox"/>';
        }

这是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, with the associations between dataTables and the database
     * @param array $data Data obtained from the SQL SELECT
     * @return array Formatted data in a row based format
     *        
     */
    static function data_output($columns, $data) {
        // Output array
        $out = array ();
        // For each row in data
        for($i = 0, $ien = count ( $data ); $i < $ien; $i ++) {
            // The current row to fulfill (i-th row)
            $row = array ();
            // For each column of the array results-dataTables
            for($j = 0, $jen = count ( $columns ); $j < $jen; $j ++) {
                // The current column
                $column = $columns [$j];
                // If a formatter is set, apply the formatter function
                // The arguments of it are:
                // * id: the column name in database
                // * row: the i-th row of database results
                //
                // The destionation column is identified by 'dt', i.e. the column name in dataTables
                if (isset ( $column['formatter'])) {
                    $row [$column['dt']] = $column['formatter']($data[$i][$column['db']], $data[$i]);
                } 
                // If no formatter is set, set the value of a cell of the current row
                // The destionation column is identified by 'dt', i.e. the column name in dataTables
                // The source cell is in the i-th row of $data, and the column is identified by 'db', i.e. the column name in database
                else {
                    $row [$column['dt']] = $data [$i][$columns [$j]['db']];
                }
            }
            $out [] = $row;
        }
        return $out;
    }

    /**
     *
     * Construct the LIMIT clause for the SQL query
     *
     * @param array $request Data sent to server by DataTables
     * @param array $columns Column information array, with the associations between dataTables and the database
     * @return string SQL limit clause to use
     *        
     */
    static function limit($request, $columns) {
        // The limit clause to set
        $limit = '';
        // Set the limit clause, if find the right parameters
        if (isset ( $request ['start'] ) && $request ['length'] != - 1) {
            $limit = "LIMIT " . intval ( $request ['start'] ) . ", " . intval ( $request ['length'] );
        }
        return $limit;
    }

    /**
     *
     * Construct the ORDER BY clause for the SQL query
     *
     * @param array $request Data sent to server by DataTables
     * @param array $columns Column information array, with the associations between dataTables and the database
     * @return string SQL order clause to use
     *        
     */
    static function order($request, $columns) {
        // The order clause to set
        $order = '';
        if (isset ( $request ['order'] ) && count ( $request ['order'] )) {
            $orderBy = array ();
            $dtColumns = self::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 = self::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 = self::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 = self::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 ', $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, $sql_details, $table, $primaryKey, $columns) {
        //TODO Non chiara l'utilità di bindings
        $bindings = array ();
        // Get an handle to the database connection
        $db = self::sql_connect ( $sql_details );
        // Build the SQL query string from the request
        $limit = self::limit ( $request, $columns );
        $order = self::order ( $request, $columns );
        $where = self::filter ( $request, $columns, $bindings );
        // The query to get the data
        $data = self::sql_exec ( $db, $bindings, "SELECT SQL_CALC_FOUND_ROWS `" . implode ( "`, `", self::pluck ( $columns, 'db' ) ) . "`
            FROM `$table`
            $where
            $order
            $limit" );
        // Dataset length after filtering
        $resFilterLength = self::sql_exec ( $db, "SELECT FOUND_ROWS()" );
        $recordsFiltered = $resFilterLength[0][0];
        // Total data set length
        $resTotalLength = self::sql_exec ( $db, "SELECT COUNT(`{$primaryKey}`) FROM `$table`" );
        $recordsTotal = $resTotalLength[0][0];
        // Data output
        return array (
                "draw" => intval ($request['draw']),
                "recordsTotal" => intval ($recordsTotal),
                "recordsFiltered" => intval ($recordsFiltered),
                "data" => self::data_output ($columns, $data) 
        );
    }

    /**
     * 
     * Connect to the specified database, returning an handle to it.
     *
     * @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 to connect to the specified database
        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, 
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", 
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET utf8" 
                )
            );
        } 
        // Catch an exception if it was not possible to connect to database
        catch ( PDOException $e ) {
            self::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, $bindings, $sql = null) {
        // Argument shifting
        // NON MI PIACE
        if ($sql === null) {
            $sql = $bindings;
        }
        // Prepare the statement for execution
        $stmt = $db->prepare ( $sql );
        // Bind parameters
        //TODO Capire questo blocco
        if (is_array ( $bindings )) {
            for($i = 0, $ien = count ( $bindings ); $i < $ien; $i ++) {
                $binding = $bindings [$i];
                $stmt->bindValue ( $binding ['key'], $binding ['val'], $binding ['type'] );
            }
        }
        // Try to execute the statement
        try {
            $stmt->execute();
        } 
        // Catch an exception
        catch ( PDOException $e ) {
            self::fatal ( "An SQL error occurred: " . $e->getMessage () );
        }
        // Return all the result sets rows
        return $stmt->fetchAll ();
    }

    /**
     * 
     * 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 associative
     * array in a numeric array,
     * returning an 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;
    }
}

有趣的问题。我遇到了同样的问题,解决方案的关键字是render,正如一些人已经指出的那样。

以下是一个完整的工作示例。

<!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>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Custom datatable work</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<link href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" rel="stylesheet" />
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js" type="text/javascript"></script>
</head>
<body>
<script>
    $(document).ready(function() {
        $('#example').DataTable({
            "processing": true,
            "serverSide": true,
            "ajax": "your_server_feed.php",
            "columnDefs": [{
                "targets": 5,  // column target which that render will return text to, starts from 0 
                "data": "text",
                "render": function(data, type, full, meta) { /
                    return 'Put here any html or text, your case <input type="checkbox">';
                }
            }]
        });
    });
</script>
<table id="example" cellspacing="0" class="display" width="100%">
    <thead>
        <tr>
            <th>ID</th>
            <th>Firstname</th>
            <th>Lastname</th>
            <th>Age</th>
            <th>Gender</th>
            <th>column 5</th><!-- this is the column where you want to add a checkbox, image or icon...etc -->
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th>ID</th>
            <th>Firstname</th>
            <th>Lastname</th>
            <th>Age</th>
            <th>Gender</th>
            <th>Column 5</th>
        </tr>
    </tfoot>
</table>
</body>
</html>

欲了解更多信息,请访问:Datatable列渲染