php - 分页，搜索和排序在使用ajax的codeigniter中的jquery数据表中不起作用

Question

目前我正在电子商务网站上工作，因为我必须显示从数据库到数据表的所有数据。数据表中的数据正常，但搜索、排序和分页不起作用。如果有人知道解决方案，请提供帮助。下面是我的代码 Controller.php

Public function transaction_data()
    {
        $id =  $this->session->Vendordetail['id'];
        $transactionData = $this->Mdl_vendor_payment->get_order_list($id);

        $requestData= $_REQUEST;

        $totalData = count($transactionData);
        $totalFiltered = count($transactionData);

        $return_days = $this->Mdl_common->get_setting('shipping_charge');

        foreach($transactionData as $transaction){
            $product = $this->Mdl_common->product_id($transaction['prodouct_id']);

            $data['order_id'] = $transaction['order_id'];
            $data['created'] = $transaction['created'];
            if($transaction['status'] == 5){
                $data['status'] = '<h5><span class="label label-danger">Cancelled</span></h5>';
            }else if($transaction['status'] == 4){
                $data['status'] = '<h5><span class="label label-default">Return</span></h5>';
            }else if($transaction['status'] == 3){
                $data['status'] = '<h5><span class="label label-success">Success</span></h5>';
            }else if($transaction['status'] == 2){
                $data['status'] = '<h5><span class="label label-primary">Dispatch</span></h5>';
            }else if($transaction['status'] == 1){
                $data['status'] = '<h5><span class="label label-primary">Ready To Dispatch</span></h5>';
            }else{
                $data['status'] = '<h5><span class="label label-primary">Approve</span></h5>';
            }
            $data['price'] = '<i class="fa fa-rupee"></i> '.$transaction['price']*$transaction['qty'];
            $data['settlement_price'] = '<i class="fa fa-rupee"></i> '.$transaction['settlement_price']*$transaction['qty'];
            $shipping_charge = $return_days['value'] * ($transaction['qty'] * $product['weight']);
            $data['shipping_charge'] = '<i class="fa fa-rupee"></i> '.$shipping_charge;

            $data['commission_fee'] = ($transaction['price']*$transaction['qty'])-($transaction['settlement_price']-$transaction['qty']);

            $data['payable_amount'] = ($transaction['settlement_price']*$transaction['qty'])-$shipping_charge;

            $data['order'] = $transaction;
            $data['product'] = $product;


            $assignment_datas[] = $data;
        }

        $json_data = array(
            "draw"            => intval( $requestData['draw'] ), 
            "recordsTotal"    => intval( $totalData ), 
            "recordsFiltered" => intval( $totalFiltered ), 
            "data"            => $assignment_datas 
        );
        echo json_encode($json_data);
        die;
    } 

下面是视图文件 View.php 的代码

$(document).ready(function() {
    var dt = $('#datatable_example').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url":"<?php echo base_url(); ?>index.php/Vendor_payment/transaction_data",
            "type":"POST",
            },
        "columns": [
            {
                "class":          "details-control",
                "orderable":      false,
                "data":           null,
                "defaultContent": ""
            },
            { "data": "order_id" },
            { "data": "created" },
            { "data": "status" },
            { "data": "price" },
            { "data": "settlement_price" },
            { "data": "shipping_charge" }
        ],
        "order": [[0, 'asc']]
    } );

    // Array to track the ids of the details displayed rows
    var detailRows = [];

    $('#datatable_example tbody').on( 'click', 'tr td.details-control', 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.details-control').trigger( 'click' );
        } );
    } );
} );

标签： phpajaxcodeigniterdatatables

Answer 1

这段代码对我来说很好......

在视图页面中：

<table id="user_datatable" class="table table-bordered table-striped">
                <thead>
                    <tr>
                        <th style="width: 5%;">ID</th>
                        <th>Client name</th>
                        <th>Mobile</th>
                        <th>Email</th>
                        <th class="col-md-2">Action</th>
                    </tr>
                </thead>
                <tbody></tbody>
            </table>

<script type="text/javascript">
    $(document).ready(function () {
        $('#user_datatable').DataTable({
            'pageLength': 1,
            'processing': true,
            'serverSide': true,
            'serverMethod': 'post',
            'ajax': {
                'url': '<?php echo base_url(); ?>user/userList'
            },
            'columns': [{
                    data: 'id'
                },
                {
                    data: 'name'
                },
                {
                    data: 'mobile'
                },
                {
                    data: 'email'
                },
                {
                    data: 'action'
                },
            ]
        });
    });
</script>

在控制器页面

public function userList() {
        $postData = $this->input->post();
        $data = $this->Data_model->getUserList($postData);
        echo json_encode($data);
    }

在模型页面

 Set the table name in $table variable
// $table = 'clients';

Add column name whatever you add in datatable 

$searchQuery = " (name like '%".$searchValue."%' or mobile like '%".$searchValue."%' or email like'%".$searchValue."%' ) ";

function getUserList($postData=null) {
    $response = array();
    $table_name = 'clients';

    ## Read value
    $draw = $postData['draw'];
    $start = $postData['start'];
    $rowperpage = $postData['length']; // Rows display per page
    $columnIndex = $postData['order'][0]['column']; // Column index
    $columnName = $postData['columns'][$columnIndex]['data']; // Column name
    $columnSortOrder = $postData['order'][0]['dir']; // asc or desc
    $searchValue = $postData['search']['value']; // Search value

     ## Search 
    $searchQuery = "";
    if($searchValue != ''){
        $searchQuery = " (name like '%".$searchValue."%' or mobile like '%".$searchValue."%' or email like'%".$searchValue."%' ) ";
    }

    ## Total number of records without filtering
    $this->db->select('count(*) as allcount');
    $records = $this->db->get($table_name)->row();
    $totalRecords = $records->allcount;

     ## Total number of record with filtering
    $this->db->select('count(*) as allcount');
    if($searchValue != '')
        $this->db->where($searchQuery);
        $records = $this->db->get($table_name)->row();
        $totalRecordwithFilter = $records->allcount;

    ## Fetch records
    $this->db->select('*');
    if($searchValue != '')
        $this->db->where($searchQuery);
        $this->db->order_by($columnName, $columnSortOrder);
        $this->db->limit($rowperpage, $start);
        $records = $this->db->get($table_name)->result();

    $data = array();

    foreach($records as $key => $record) {
        $data[] = array( 
            "id"=> ($key + 1),
            "name"=>$record->name,
            "mobile"=>$record->mobile,
            "email"=>$record->email,
            "action"=>"<a href='".base_url()."user/update/".$record->id."' type='button' class='btn btn-success'>Update</a><a href='".base_url()."user/delete/".$record->id."' type='button' class='btn btn-danger' style='margin-left: 5px;'>Delete</a>",
        ); 
    }

     ## Response
    $response = array(
        "draw" => intval($draw),
        "recordsTotal" => $totalRecordwithFilter,
        "recordsFiltered" => $totalRecords,
        "data" => $data
    );

    return $response; 
}