Datatable Pagination not working properly on server side

cyrus_manatad

I am using datatable since my first time developing web application. But I have just some issue in my project this past week and I can't figure it out until now. I've generated my table in server side and I was able to get the data from my DB and it works perfect but when click the pagination button it turns out my pagination button not working properly. I don't know what did I missed. Here is my client side and server side code. Your help will be very much appreciated. Sorry with my english btw.

Client Side...

  $('#tbl-employees-server-side').DataTable( {
      "dom" : "Bfrtip",
      "pagingType": "full_numbers",
      "responsive": true,
      "processing": true,
      "serverSide": true,
      "ajax": {
        "url" : "controller/crud_manager.php",
        "type": "POST",
        "data": {"action":"server_side"}
      },      
      "buttons": [
      {
          "extend" : 'excelHtml5',
          "exportOptions" : {
              "columns" : ':visible'
          }
      },
      {
          "extend" : 'pdfHtml5',
          "exportOptions" : {
              "columns" : ':visible'
          }
      },
      {
          "extend" : 'print',
          "exportOptions" : {
              "columns" : [ 0,1,2,3,4 ]
          }
      },
      "colvis"
    ]
  } );

Server Side

  $sql = "";
  $sql .= "SELECT * FROM master_list ";
  $count = "SELECT count(*) FROM master_list ";
  $column= array(
        0 => 'emp_num',
        1 => 'name',
        2 => 'hire_date',
        3 => 'emp_status',
        4 => 'position',
        5 => 'emp_num',
    ); //create column like table in database

  if(isset($_POST['search']['value'])){

    $sql .= 'WHERE emp_num LIKE "%'.$_POST['search']['value'].'%" ';
    $count .= 'WHERE emp_num LIKE "%'.$_POST['search']['value'].'%" ';
    $sql .= ' OR name LIKE "%'.$_POST['search']['value'].'%" ';
    $count .= ' OR name LIKE "%'.$_POST['search']['value'].'%" ';
    $sql .= ' OR emp_status LIKE "%'.$_POST['search']['value'].'%" ';
    $count .= ' OR emp_status LIKE "%'.$_POST['search']['value'].'%" ';
    $sql .= ' OR position LIKE "%'.$_POST['search']['value'].'%" ';
    $count .= ' OR position LIKE "%'.$_POST['search']['value'].'%" ';

  }

  if (isset($_POST['order'])) {
    if ($column[$_POST['order']['0']['column']] != 5) {
        $sql .= ' ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
        $count .= ' ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
    }
  }else{
    $sql .= ' ORDER BY name ASC ';
    $count .= ' ORDER BY name ASC ';
  }

  if ($_POST['length'] !=-1) {
    $sql .= 'LIMIT '.$_POST['start'].','.$_POST['length'];
    $count .= 'LIMIT '.$_POST['start'].','.$_POST['length'];
  }

  $data = $this->connection->query($sql)->fetchAll(PDO::FETCH_ASSOC);

  $filtered_rows = $this->connection->query($count)->fetchColumn();

  $getData = array();
  foreach ($data as $value) {
    $subArray   = array();
    $subArray[] = $value['emp_num'];
    $subArray[] = $value['name'];
    $subArray[] = date('M. d, Y',strtotime($value['hire_date']));
    $subArray[] = $value['emp_status'];
    $subArray[] = $value['position'];
    $subArray[] = "<button class='btn btn-xs btn-success btn-view-record' data-emp_num=".$value['emp_num'].">
                <i class='fa fa-eye'></i></button>
            <button class='btn btn-xs btn-warning btn-edit-record' data-emp_num=".$value['emp_num'].">
                <i class='fa fa-edit'></i></button>
            <button class='btn btn-xs btn-danger btn-delete-record' data-emp_num=".$value['emp_num'].">
                <i class='fa fa-trash'></i></button>";

    $getData[] = $subArray;

  }

  $total_row = $this->connection->query("SELECT count(*) FROM master_list")->fetchColumn();

  $result = array(
    'draw'              =>  intval($_POST['draw']),
    'recordsTotal'      =>  $total_row,
    'recordsFiltered'   =>  $filtered_rows,
    'data'              =>  $getData
  );
  return $result;

colin

Hi @cyrus_manatad ,

"turns out my pagination button not working properly"

Can you give us some more clues? Does it return the wrong page, the wrong data, are errors seen on the client or the server? Is the JSON in the correct format?

We're happy to take a look. As per the forum rules, if you could link to a running test case showing the issue we can offer some help. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

cyrus_manatad

Hi @colin ,

Actually the first load of table has no problem it really works fine including pagination button, but when times I click the pagination button the pagination button itself are not working properly, but the data in the table works fine it follows after pagination button is click, so no problem about the data that is being loaded in the table only the pagination button and seems it gives me additional button or some kind of endless button as well as the table info, it gives me "Showing 0 to 0 of 000 entries" but then my table has data. So I think there is just something I've missed to my queries in my server side and I don't know where and how to figure it out...

Thanks for the response Colin, I'm looking forward that we can fixed my issue.

Regards,

Cyrus

colin

Hi @cyrus_manatad ,

Those counts come back from the server when serverSide is enabled, so my guess is that your server script isn't returning the correct information. Take a look at this page for protocol, and this for an example.

If that doesn't work, please post a link to a running example, instructions are in my first reply above.

Cheers,

Colin