Datatable api.draw无效。 如何定义列并渲染每一行？

Question

Currently appending each row, but without the draw method pagination doesn't show. How can the column be defined and with the defined column need to render rows. What am I doing wrong here ?

//ignore this repeated section . Currently appending each row, but without the draw method pagination doesn't show. How can the column be defined and with the defined column need to render rows. What am I doing wrong here ?

javascript:

<script>
$(function () {

    var loading_image_src = '<?php echo base_url() ?>' + 'project_base_assets/base_demo_images/loading.gif';
    var loading_image = '<img src="' + loading_image_src + ' ">';
    var loading_span = '<span><i class="fa fa-refresh fa-spin fa-4x" aria-hidden="true"></i></span> ';
    var loading_text = "<div style='font-size:larger' ><?php echo lang('loading_text')?></div>";

    $('#user-table').DataTable({
        "processing": true,
        "serverSide": true,
        "paging": true,
        "pagingType": "full_numbers",
        "lengthChange": true,
        "searching": true,
        "ordering": true,
        "info": true,
        "autoWidth": true,
        "language": {
            processing: loading_image + '<br>' + loading_text
        },

        'columnDefs': [{
            // The `data` parameter refers to the data for the cell (defined by the
            // `data` option, which defaults to the column being worked with, in
            // this case `data: 0`.
            "render": function (data, type, row) {

                alert(data);
                console.log(data);
                console.log(type);
                console.log(row);

                return data;
            },
            "targets": 0
        }, {orderable: false, targets: [3, 7]}/*, { visible: false, targets: [3,4,5] }*/
        ],

        "aaSorting": [[5, 'asc']],

        "ajax": {
            url: "<?php echo base_url() . 'users/auth/get_users_by_ajax' ?>",                    // json datasource
            type: "post",
            success: function (res) {

                $('#user-table-tbody').children("tr").remove();

                console.log(res.last_query);
                 console.log(res.common_filter_value);
                 console.log(res.specific_filters);
                 console.log(res.order_column);
                 console.log(res.order_by);
                 console.log(res.limit_start);
                 console.log(res.limit_length);

                $('#user-table_processing').css('display', 'block');

                for (var i = 0; i < res.data.users.length; i++) {

                    var col = new Array();
                    col[0] = '<td>' + res.data.users[i].first_name + '</td>';
                    col[1] = '<td>' + res.data.users[i].last_name + '</td>';
                    col[2] = '<td title="' + res.data.users[i].email + '" >' + res.data.users[i].email + '</td>';

                    //col-3 starts
                    var group_names = '';
                    for (var incr = 0; incr < res.data.users[i].groups.length; incr++) {
                        group_names += res.data.users[i].groups[incr].name;

                        group_names += '<br>';
                    }
                    col[3] = '<td>' + group_names + '</td>';
                    //col-3 ends

                    //col-4 starts
                    var status_span = '';
                    var status_anchor = '';
                    var status_anchor_span = '';
                    var tooltip_deactivate_text = '<?php echo lang('tooltip_deactivate_text') ?>';
                    var tooltip_activate_text = '<?php echo lang('tooltip_activate_text') ?>';
                    var deactivate_url = '<?php echo base_url() ?>' + 'users/auth/deactivateUser/' + res.data.users[i].id;
                    var activate_url = '<?php echo base_url() ?>' + 'users/auth/activateUser/' + res.data.users[i].id;
                    col[4] = '';

                    if (res.data.users[i].active == 1) {
                        status_span = '<span class="label label-primary">' + '<?php echo lang('status_active_text')?>' + '</span>';
                        status_anchor_span = '<span class="label label-danger"><i class="fa fa-check" aria-hidden="true"></i></span>';
                        status_anchor = '<a title="' + tooltip_deactivate_text + '" ' + ' href="' + deactivate_url + '" ' + '>' + status_anchor_span + '</a>';
                        col[4] = '<td data-sort="' + res.data.users[i].active + '"> ' + status_span + '&nbsp; &nbsp;' + status_anchor + '</td>';
                    } else {
                        status_span = '<span class="label label-primary">' + '<?php echo lang('status_inactive_text')?>' + '</span>';
                        status_anchor_span = '<span class="label label-success"><i class="fa fa-check" aria-hidden="true"></i></span>';
                        status_anchor = '<a title="' + tooltip_activate_text + '" ' + ' href="' + activate_url + '" ' + '>' + status_anchor_span + '</a>';
                        col[4] = '<td data-sort="' + res.data.users[i].active + '"> ' + status_span + '&nbsp; &nbsp;' + status_anchor + '</td>';
                    }

                    //col[4] = '<td>' + res.data.users[i].active + '</td>';
                    //col-4 ends

                    col[5] = '<td data-sort="' + res.data.users[i].created_on_timestamp + '" >' + res.data.users[i].created_on + '</td>';
                    col[6] = '<td data-sort="' + res.data.users[i].last_login_timestamp + '" >' + res.data.users[i].last_login + '</td>';

                    //col=7 starts
                    var tooltip_view_text = '<?php echo lang('tooltip_view_text') ?>';
                    var view_url = '<?php echo base_url() ?>' + 'user_profile_module/user_profile_overview/' + res.data.users[i].id;
                    var view_anchor = '<a title="' + tooltip_view_text + '" ' + ' href="' + view_url + '" '+'style="color:#2b2b2b"' + '>' + '<i class="fa fa-eye fa-lg " aria-hidden="true"></i>' + '</a>';

                    //message unavailable
                    var tooltip_message_text = '<?php echo lang('tooltip_message_text') ?>';
                    var message_url = '<?php echo base_url() ?>';
                    var message_anchor = '<a title="' + tooltip_message_text + '" ' + ' href="' + message_url + '" '+'style="color:#2b2b2b"' + '>' + '<i class="fa fa-envelope-o fa-lg" aria-hidden="true"></i>' + '</a>';

                    var tooltip_edit_text = '<?php echo lang('tooltip_edit_text') ?>';
                    var edit_url = '<?php echo base_url() ?>'+'users/auth/edit_user/'+ res.data.users[i].id;
                    var edit_anchor = '<a title="' + tooltip_edit_text + '" ' + ' href="' + edit_url + '" '+'style="color:#2b2b2b"' + '>' + '<i class="fa fa-pencil-square-o fa-lg" aria-hidden="true"></i>' + '</a>';

                    var tooltip_delete_text = '<?php echo lang('tooltip_delete_text') ?>';
                    var delete_url = '<?php echo base_url() ?>'+'users/auth/deleteUser/'+res.data.users[i].id;
                    var delete_anchor = '<a class="confirmation" title="' + tooltip_delete_text + '" ' + ' href="' + delete_url + '" '+'style="color:#2b2b2b"' + '>' + '<i class="fa fa-trash-o fa-lg" aria-hidden="true"></i>' + '</a>';

                    col[7] = '<td>' + view_anchor+'&nbsp; &nbsp;'+edit_anchor+'&nbsp; &nbsp;'+delete_anchor+'&nbsp;&nbsp;'+'</td>';
                    //col-7 ends

                    var row = '<tr>';

                    for (var inc = 0; inc < col.length; inc++) {

                        row += col[inc];
                    }

                    row += '</tr>';


                    //console.log(row);
                    $('#user-table-tbody').append(row);


                }

                //hide loading after appending or drawing rows
                $('#user-table_processing').css('display', 'none');

            }
        }

    }).draw();
});

php:

public function getUsersByAjax()
{
    $requestData = $_REQUEST;

    $columns[0] = 'first_name';
    $columns[1] = 'last_name';
    $columns[2] = 'email';
    $columns[3] = 'group';
    $columns[4] = 'active';
    $columns[5] = 'created_on';
    $columns[6] = 'last_login';
    $columns[7] = 'actions';

    $common_filter_value = false;
    $order_column = false;

    $specific_filters = array();
    //$specific_filters = false;

    if (!empty($requestData['columns'][0]['search']['value'])) {
        $specific_filters['first_name'] = $requestData['columns'][0]['search']['value'];
    }

    if (!empty($requestData['columns'][1]['search']['value'])) {
        $specific_filters['last_name'] = $requestData['columns'][1]['search']['value'];
    }

    if (!empty($requestData['columns'][2]['search']['value'])) {
        $specific_filters['email'] = $requestData['columns'][2]['search']['value'];
    }

    if (!empty($requestData['columns'][3]['search']['value'])) {
        $specific_filters['group'] = $requestData['columns'][3]['search']['value'];
    }

    if (!empty($requestData['columns'][4]['search']['value'])) {
        $specific_filters['active'] = $requestData['columns'][4]['search']['value'];
    }


    if (!empty($requestData['search']['value'])) {
        $common_filter_value = $requestData['search']['value'];
    }

    if ($specific_filters == true || !empty($specific_filters)) {
        $common_filter_value = false;       //either search with specific filters or with common filter
    }

    $order['column'] = $columns[$requestData['order'][0]['column']];
    $order['by'] = $requestData['order'][0]['dir'];


    $limit['start'] = $requestData['start'];
    $limit['length'] = $requestData['length'];

    $totalData = $this->Custom_auth_model->countUsers($common_filter_value, $specific_filters);

    if ($common_filter_value == true || $specific_filters == true) {
        $totalFiltered = $totalData;
    } else {
        $totalFiltered = $this->Custom_auth_model->countUsers($common_filter_value = false, $specific_filters = false);
    }

    $totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.

    $data['users'] = $this->Custom_auth_model->getUsers($common_filter_value, $specific_filters,$order,$limit);
    $last_query = $this->db->last_query();

    foreach ($data['users'] as $k => $user) {
        $data['users'][$k]->groups = $this->ion_auth->get_users_groups($user->id)->result();

    }

    $this->load->library('custom_datetime_library');


    $i = 0;
    foreach ($data['users'] as $a_user) {

        $data['users'][$i]->created_on_timestamp = $a_user->created_on;
        $data['users'][$i]->last_login_timestamp = $a_user->last_login;

        if ($a_user->created_on == 0) {
            $a_user->created_on = $this->lang->line('creation_time_unknown_text');
        } else {
            $a_user->created_on =
                $this->custom_datetime_library
                    ->convert_and_return_TimestampToDateAndTime($a_user->created_on);
        }

        if ($a_user->last_login == 0) {
            $a_user->last_login = $this->lang->line('never_logged_in_text');
        } else {
            $a_user->last_login =
                $this->custom_datetime_library
                    ->convert_and_return_TimestampToDateAndTime($a_user->last_login);
        }

        $i++;

    }

    $json_data['draw'] = intval($requestData['draw']);

    /* $totalData: for every request/draw by clientside ,
     they send a number as a parameter, when they recieve a response/data they first check the draw number,
     so we are sending same number in draw.*/
    $json_data['recordsTotal'] = intval($totalData); // total number of records after searching, if there is no searching then totalFiltered = totalData
    $json_data['recordsFiltered'] = intval($totalFiltered);

    $json_data['data'] = $data;

    // checking requests in console.log();
    $json_data['last_query'] = $last_query;
    $json_data['common_filter_value'] = $common_filter_value;
    $json_data['specific_filters'] = $specific_filters;
    $json_data['order_column'] = $order['column'];
    $json_data['order_by'] = $order['by'];
    $json_data['limit_length'] = $limit['length'];
    $json_data['limit_start'] = $limit['start'];


    echo json_encode($json_data);
}

Answer 1

Got solution 3 Days ago. Have to remove success(){} from Ajax request . It creates problem with the table rendering.Either have to pass data with numerical index or have to use 'orthogonal data method'. Here it is:

Php:

public function getUsersByAjax()
{
    $users = array();

    $requestData = $_REQUEST;
    //print_r($requestData);

    $columns[0] = 'first_name';
    $columns[1] = 'last_name';
    $columns[2] = 'email';
    $columns[3] = 'group';
    $columns[4] = 'active';
    $columns[5] = 'created_on';
    $columns[6] = 'last_login';
    $columns[7] = 'actions';

    $common_filter_value = false;
    $order_column = false;

    $specific_filters = array();
    $specific_filters = false;

    if (!empty($requestData['columns'][0]['search']['value'])) {
        $specific_filters['first_name'] = $requestData['columns'][0]['search']['value'];
    }

    if (!empty($requestData['columns'][1]['search']['value'])) {
        $specific_filters['last_name'] = $requestData['columns'][1]['search']['value'];
    }

    if (!empty($requestData['columns'][2]['search']['value'])) {
        $specific_filters['email'] = $requestData['columns'][2]['search']['value'];
    }

    if (!empty($requestData['columns'][3]['search']['value'])) {
        $specific_filters['group'] = $requestData['columns'][3]['search']['value'];
    }

    if (!empty($requestData['columns'][4]['search']['value'])) {
        $specific_filters['active'] = $requestData['columns'][4]['search']['value'];
    }


    if (!empty($requestData['search']['value'])) {
        $common_filter_value = $requestData['search']['value'];
    }

    if ($specific_filters == true || !empty($specific_filters)) {
        $common_filter_value = false;       //either search with specific filters or with common filter
    }

    $order['column'] = $columns[$requestData['order'][0]['column']];
    $order['by'] = $requestData['order'][0]['dir'];


    $limit['start'] = $requestData['start'];
    $limit['length'] = $requestData['length'];

    $totalData = $this->Custom_auth_model->countUsers(false, false);

    if ($common_filter_value == true || $specific_filters == true) {

        $totalFiltered = $this->Custom_auth_model->countUsers($common_filter_value, $specific_filters);
    } else {
        $totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.
    }


    $users = $this->Custom_auth_model->getUsers($common_filter_value, $specific_filters, $order, $limit);

    if ($users == false || empty($users) || $users == null) {
        $users = false;
    }

    $last_query = $this->db->last_query();

    if ($users) {
        foreach ($users as $k => $user) {
            $user_groups = $this->ion_auth->get_users_groups($user->id)->result();
            $users[$k]->groups = '';

            $iter = 0;
            foreach ($user_groups as $a_group) {

                if ($iter != 0) {
                    $users[$k]->groups .= '<br>';
                }

                $users[$k]->groups .= $a_group->name;

                $iter++;
            }
        }
    }

    $this->load->library('custom_datetime_library');


    if ($users) {
        $i = 0;
        foreach ($users as $a_user) {

            /*date time starts*/
            $users[$i]->cr_on = new stdClass();
            $users[$i]->lt_lg_in = new stdClass();

            $users[$i]->cr_on->timestamp = $a_user->created_on;
            $users[$i]->lt_lg_in->timestamp = $a_user->last_login;

            if ($a_user->created_on == 0) {
                $users[$i]->cr_on->display = $this->lang->line('creation_time_unknown_text');
            } else {
                $users[$i]->cr_on->display =
                    $this->custom_datetime_library
                        ->convert_and_return_TimestampToDateAndTime($a_user->created_on);
            }

            if ($a_user->last_login == 0) {
                $users[$i]->lt_lg_in->display = $this->lang->line('never_logged_in_text');
            } else {
                $users[$i]->lt_lg_in->display =
                    $this->custom_datetime_library
                        ->convert_and_return_TimestampToDateAndTime($a_user->last_login);
            }
            /*date time ends*/

            /*active - inactive starts*/
            $users[$i]->act = new stdClass();
            $users[$i]->act->int = $a_user->active;

            if ($a_user->active == 1) {

                $status_span = '<span class = "label label-primary">' . $this->lang->line('status_active_text') . '</span>';

                $status_tooltip = $this->lang->line('tooltip_deactivate_text');
                $status_url = base_url() . 'users/auth/deactivateUser/' . $a_user->id;
                $status_anchor_span = '<span class="label label-danger"><i class="fa fa-times" aria-hidden="true"></i></span>';
                $status_anchor =
                    '<a ' . ' title="' . $status_tooltip . '"' . ' href="' . $status_url . '">' . $status_anchor_span . '</a>';

            } else {
                $status_span = '<span class = "label label-default">' . $this->lang->line('status_inactive_text') . '</span>';

                $status_tooltip = $this->lang->line('tooltip_activate_text');
                $status_url = base_url() . 'users/auth/activateUser/' . $a_user->id;
                $status_anchor_span = '<span class="label label-success"><i class="fa fa-check" aria-hidden="true"></i></span>';
                $status_anchor =
                    '<a ' . ' title="' . $this->lang->line('tooltip_activate_text') . '"' . ' href="' . $status_url . '">' . $status_anchor_span . '</a>';
            }

            $users[$i]->act->html = $status_span . '&nbsp; &nbsp;' . $status_anchor;
            /*active - inactive ends*/

            /*action starts*/


            $view_tooltip = $this->lang->line('tooltip_view_text');
            $view_url = base_url() . 'user_profile_module/user_profile_overview/' . $a_user->id;
            $view_anchor =
                '<a ' . ' title="' . $view_tooltip . '" ' . ' href="' . $view_url . '" ' . ' style="color:#2b2b2b" ' . '>'
                . '<i class="fa fa-eye fa-lg" aria-hidden="true"></i>'
                . '</a>';

            $message_tooltip = $this->lang->line('tooltip_message_text');
            $message_url = base_url() . 'message' . $a_user->id; //undefined
            $message_anchor = '<a ' . ' title="' . $message_tooltip . '" ' . ' href="' . $message_url . '" ' . ' style="color:#2b2b2b" ' . '>'
                . '<i class="fa fa-envelope-o fa-lg" aria-hidden="true"></i>'
                . '</a>';

            $edit_tooltip = $this->lang->line('tooltip_edit_text');
            $edit_url = base_url() . 'users/auth/edit_user/' . $a_user->id;
            $edit_anchor = '<a ' . ' title="' . $edit_tooltip . '" ' . ' href="' . $edit_url . '" ' . ' style="color:#2b2b2b" ' . '>'
                . '<i class="fa fa-pencil-square-o fa-lg" aria-hidden="true"></i>'
                . '</a>';

            $delete_tooltip = $this->lang->line('tooltip_delete_text');
            $delete_url = base_url() . 'users/auth/deleteUser/' . $a_user->id;
            $delete_anchor = '<a ' . ' title="' . $delete_tooltip . '" ' . ' href="' . $delete_url . '" ' . ' class="confirmation" ' . ' style="color:#2b2b2b" ' . '>'
                . '<i class="fa fa-trash-o fa-lg" aria-hidden="true">'
                . '</a>';

            $users[$i]->action = $view_anchor . '&nbsp;&nbsp;' . $edit_anchor . '&nbsp;&nbsp;' . $delete_anchor; //message is not defined yet
            /*action ends*/

            $i++;

        }
    }


    $json_data['draw'] = intval($requestData['draw']);
    /* $totalData: for every request/draw by clientside ,
     they send a number as a parameter, when they recieve a response/data they first check the draw number,
     so we are sending same number in draw.*/
    $json_data['recordsTotal'] = intval($totalData); // total number of records after searching, if there is no searching then totalFiltered = totalData
    $json_data['recordsFiltered'] = intval($totalFiltered);

    //$users = $this->removeKeys($users); // converting to numeric indices.
    $json_data['data'] = $users;

    // checking requests in console.log() for testing starts;
    $json_data['last_query'] = $last_query;
    $json_data['common_filter_value'] = $common_filter_value;
    $json_data['specific_filters'] = $specific_filters;
    $json_data['order_column'] = $order['column'];
    $json_data['order_by'] = $order['by'];
    $json_data['limit_length'] = $limit['length'];
    $json_data['limit_start'] = $limit['start'];
    // checking requests in console.log() for testing ends;

    echo(json_encode($json_data));

}

Javascript:

<script>
$(document).ready(function () {

    var loading_image_src = '<?php echo base_url() ?>' + 'project_base_assets/base_demo_images/loading.gif';
    var loading_image = '<img src="' + loading_image_src + ' ">';
    var loading_span = '<span><i class="fa fa-refresh fa-spin fa-4x" aria-hidden="true"></i></span> ';
    var loading_text = "<div style='font-size:larger' ><?php echo lang('loading_text')?></div>";


    $('#user-table').DataTable({

        processing: true,
        serverSide: true,
        paging: true,
        pagingType: "full_numbers",
        lengthChange: true,
        searching: true,
        ordering: true,
        info: true,
        autoWidth: true,
        searchDelay: 500,
        infoEmpty:'<?php echo lang("no_user_found_text")?>',
        zeroRecords: '<?php echo lang("no_matching_user_found_text")?>',
        language: {
            processing: loading_image + '<br>' + loading_text
        },

        columns: [
            {data: "first_name"},
            {data: "last_name"},
            {data: "email"},
            {data: "groups"},
            {
                data: {
                    _: "act.html",
                    sort: "act.int"
                }
            },
            {
                data: {
                    _: "cr_on.display",
                    sort: "cr_on.timestamp"
                }
            },
            {
                data: {
                    _: "lt_lg_in.display",
                    sort: "lt_lg_in.timestamp"
                }
            },
            {data: "action"}

        ],

        columnDefs: [

            {
                'targets': 0,
                'createdCell': function (td, cellData, rowData, row, col) {
                    $(td).attr('title', cellData);
                }
            },
            {
                'targets': 1,
                'createdCell': function (td, cellData, rowData, row, col) {
                    $(td).attr('title', cellData);
                }
            },

            {
                'targets': 2,
                'createdCell': function (td, cellData, rowData, row, col) {
                    $(td).attr('title', cellData);
                }
            },

            {orderable: false, targets: [3, 7]} /*, { visible: false, targets: [3,5,6] }*/
        ],

        aaSorting: [[5, 'desc']],

        ajax: {
            url: "<?php echo base_url() . 'users/auth/get_users_by_ajax' ?>",                   // json datasource
            type: "post"

            //open succes only for test purpuses . remember when success is uncommented datble doesn't diplay data
            /*success: function (res) {

             console.log(res.last_query);
             console.log(res.common_filter_value);
             console.log(res.specific_filters);
             console.log(res.order_column);
             console.log(res.order_by);
             console.log(res.limit_start);
             console.log(res.limit_length);
             }*/
        }

    });
});