php - Pagination, searching and sorting is not working in jquery datatable in codeigniter using ajax

Solution:

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

In Controller page

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

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

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.