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

Question

Currently i am working on eCommerce website in that i have to display all data from database to datatable. Data is coming properly in datatable but searching, sorting and pagination is not working.If any body know solution than please help. Below is my code 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;
    } 

Below is the code for view file 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' );
        } );
    } );
} );

Answer 1

This code is work fine for me...

In view page:

<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>

In Controller page

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

In Model page

 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; 
}