Login

TeetharKehar · 01-23-2023, 12:36 AM

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 ome glz echat 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