Welcome Guest, Not a member yet? Register   Sign In
Datatables on Codeigniter 2.x using MSSQL LIMIT not working
#1

I have a problem on my datatables pagination on codeigniter 2, First page is okay but when I click on Next Its using Select top 20 so It shows 20 rows instead of rows 11 - 20

Here's my code:

web_user_model.php

private function _get_datatables_query($term=''){
    $column = array('a.empcode','a.emailadd','a.user_level','a.isactive' ,'b.emp_last +\', \'+ b.emp_first + \' \' + b.emp_mid');
    $this->db->select('*');
    $this->db->from('web_user as a');
    $this->db->join('emp_data as b', 'b.emp_code = a.empcode','left');
    $this->db->like('a.empcode', $term);
    $this->db->or_like('a.emailadd', $term);
    $this->db->or_like('a.user_level', $term);
    $this->db->or_like('a.isactive', $term);
    $this->db->or_like('b.emp_last +\', \'+ b.emp_first + \' \' + b.emp_mid', $term);
    if(isset($_REQUEST['order']))
    {
      $this->db->order_by($column[$_REQUEST['order']['0']['column']], $_REQUEST['order']['0']['dir']);
    }
    else if(isset($this->order))
    {
      $order = $this->order;
      $this->db->order_by(key($order), $order[key($order)]);
    }
}

function get_datatables(){
  $term = $_REQUEST['search']['value']; 
  $this->_get_datatables_query($term);
  if($_REQUEST['length'] != -1)
  $this->db->limit($_REQUEST['length'], $_POST['start']);
  $query = $this->db->get();
  return $query->result();
}

function count_filtered(){
  $term = $_REQUEST['search']['value'];
  $this->_get_datatables_query($term);
  $query = $this->db->get();
  return $query->num_rows(); 
}

public function count_all(){
  $this->db->from($this->table);
  return $this->db->count_all_results(); 
}
Web_user_controller.php

public function ajax_list()
{
    $this->load->model('webuser_model');
    $list = $this->webuser_model->get_datatables();
    $data = array();
    $no = $_POST['start'];
    foreach ($list as $emp) {
        $no++;
        $row = array();
        $row[] = $emp->emp_code;
        $row[] = $emp->emp_last .", ". $emp->emp_first ." ". $emp->emp_mid;
        $row[] = $emp->emailadd;
        $row[] = $emp->user_level;
        $row[] = $emp->isactive;
        //add html for action
        $row[] = '<a data-toggle="modal" name="btnEditUser" class="btn btn-primary btn-xs" href="javascript:void(0)" title="Edit" onclick="edit('."'".$emp->emp_code."'".')"><i class="glyphicon glyphicon-pencil"></i></a>';

        $data[] = $row;
    }
    $output = array(
                    "draw" => $_POST['draw'],
                    "recordsTotal" => $this->webuser_model->count_all(),
                    "recordsFiltered" => $this->webuser_model->count_filtered(),
                    "data" => $data,
        );
    //output to json format
    echo json_encode($output);
}
View

<table id="table1" class="table table-order-column" cellspacing="0" style="width: 100%">
            <thead>
                <tr>
                    <th>Employee No.</th>
                    <th>Employee Name</th>
                    <th>SPI Email Address</th>
                    <th>Role</th>
                    <th>IsActive</th>
                    <th>Action</th>
                </tr>
            </thead>
            <tbody>

            </tbody>
            <tfoot>
                <tr>
                    <th>Employee No.</th>
                    <th>Employee Name</th>
                    <th>SPI Email Address</th>
                    <th>Role</th>
                    <th>IsActive</th>
                    <th>Action</th>
                </tr>
            </tfoot>
        </table>

      <script>       
            $(document).ready(function() {
                table = $('#table1').DataTable({
                        "processing": true,
                        "serverSide": true,
                        "order": [[0, 'desc']], //Initial no order.
                        // Load data for the table's content from an Ajax source
                        "ajax": {
                            "url": "<?php echo site_url(); ?>/web_user_controler/ajax_list",
                            "type": "POST"
                        },
                        //Set column definition initialisation properties.
                        "columnDefs": [
                        {
                            "targets": [ -1 ], //last column
                            "orderable": true, //set not orderable
                        },
                        ],

                    });
                });
      </script>
Chrome Result Page 1

{draw: "1", recordsTotal: 12, recordsFiltered: 12,…}
data:[["999999999 ", "JJJJJ, JJJJJ JJJJJ", "[email protected]", "employee ", "Yes ",…],…]
0:["999999999 ", "JJJJJ, JJJJJ JJJJJ", "[email protected]", "employee ", "Yes ",…]
1:["99999999 ", "GUILOREZA, JAY B", "[email protected]", "admin ", "Yes ",…]
2:["40009194 ", "LLAMES, ROGER PAUL MALINIS", "[email protected]", "employee ", "Yes ",…]
3:["40009193 ", "ALGENTERA, ORLANDOO ORLY", "[email protected]", "employee ", "Yes ",…]
4:["40009192 ", "TAPIA, ALEXANDER ALEX", "[email protected]", "employee ", "Yes ",…]
5:["40009190 ", "TABIRAO, CORA SANTOS", "[email protected]", "admin ", "Yes ",…]6:["20149377 ", "DALOCANOG, ROMEO VALENTINO", "[email protected]", "employee ",…]
7:["146247 ", "QUITANEG, JOHN RICHARD SISON", "[email protected]", "admin ",…]
8:["123451234 ", "QUITANEG, JJJJJ SISON", "[email protected]", "employee ", "Yes ",…]
9:["12344 ", "JJJJJ, TEST SISON", "[email protected]", "employee ", "Yes ",…]
draw:"1"
recordsFiltered:12
recordsTotal:12
Chrome Result Page 2

{draw: "2", recordsTotal: 12, recordsFiltered: 12,…}
data:[["999999999 ", "JJJJJ, JJJJJ JJJJJ", "[email protected]", "employee ", "Yes ",…],…]
0:["999999999 ", "JJJJJ, JJJJJ JJJJJ", "[email protected]", "employee ", "Yes ",…]
1:["99999999 ", "GUILOREZA, JAY B", "[email protected]", "admin ", "Yes ",…]
2:["40009194 ", "LLAMES, ROGER PAUL MALINIS", "[email protected]", "employee ", "Yes ",…]
3:["40009193 ", "ALGENTERA, ORLANDOO ORLY", "[email protected]", "employee ", "Yes ",…]
4:["40009192 ", "TAPIA, ALEXANDER ALEX", "[email protected]", "employee ", "Yes ",…]
5:["40009190 ", "TABIRAO, CORA SANTOS", "[email protected]", "admin ", "Yes ",…]
6:["20149377 ", "DALOCANOG, ROMEO VALENTINO", "[email protected]", "employee ",…]
7:["146247 ", "QUITANEG, JOHN RICHARD SISON", "[email protected]", "admin ",…]
8:["123451234 ", "QUITANEG, JJJJJ SISON", "[email protected]", "employee ", "Yes ",…]
9:["12344 ", "JJJJJ, TEST SISON", "[email protected]", "employee ", "Yes ",…]
10:["123111111 ", "TESTLAST, TEST TESTMID", "[email protected]", "employee ", "Yes ",…]
11:["11111 ", "JJJJJ, JJJJJ SISON", "[email protected]", "employee ", "Yes ",…]
draw:"2"
recordsFiltered:12
recordsTotal:12
Profiler Page 1

declare @p1 int
set @p1=180150003
declare @p3 int
set @p3=8
declare @p4 int
set @p4=1
declare @p5 int
set @p5=10
exec sp_cursoropen @p1 output,N'SELECT  TOP 10 *
FROM web_user as a
LEFT JOIN emp_data as b ON b.emp_code = a.empcode
WHERE  a.empcode  LIKE ''%%'' ESCAPE ''!''
OR  a.emailadd  LIKE ''%%'' ESCAPE ''!''
OR  a.user_level  LIKE ''%%'' ESCAPE ''!''
OR  a.isactive  LIKE ''%%'' ESCAPE ''!''
OR  b.emp_last +'', ''+ b.emp_first + '' '' + b.emp_mid  LIKE ''%%'' ESCAPE ''!''
ORDER BY a.empcode desc ',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5
Profiler Page 2

declare @p1 int
set @p1=180150003
declare @p3 int
set @p3=8
declare @p4 int
set @p4=1
declare @p5 int
set @p5=10
exec sp_cursoropen @p1 output,N'SELECT  TOP 20 *
FROM web_user as a
LEFT JOIN emp_data as b ON b.emp_code = a.empcode
WHERE  a.empcode  LIKE ''%%'' ESCAPE ''!''
OR  a.emailadd  LIKE ''%%'' ESCAPE ''!''
OR  a.user_level  LIKE ''%%'' ESCAPE ''!''
OR  a.isactive  LIKE ''%%'' ESCAPE ''!''
OR  b.emp_last +'', ''+ b.emp_first + '' '' + b.emp_mid  LIKE ''%%'' ESCAPE ''!''
ORDER BY a.empcode desc ',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5
Reply




Theme © iAndrew 2016 - Forum software by © MyBB