Datatables (AJAX+PHP), Search, Sorting and Pagination do not work (Table freeze)

Question

The Datatables is working, all the Buttons work and call the table, I am using load to open the table by ajax, does the Reload normally, but the other actions do not, as:

• Pagination, click on page 2, change the descriptions below and the page number, but do not change the lines;

• Search does not work, but using an external search with myFunction from a column, it works;

• Sort, does not work, the icon even changes down or up...

What I’m understanding that the values pulled by Ajax from the list generated by PHP are static and nothing changes.

When using the foreach inside the page and listed everything, it works correctly... I wanted to separate so I could work with Reload and insert and edit with modals without updating the page.

equipAll.php

<?php

include '../datasourceGestao.php';

$datasource = new datasource();

$result = $datasource->getAllEquip();

$row = array();	

foreach ($result as $array) {
    $row[] = array('admin_id' => $array['admin_id'],
	    'first_name' => $array['first_name'],
	    'last_name' => $array['last_name'],
	    'email' => $array['email'],
	    'gender' => $array['gender'],
	    'birth_date' => $datasource->invertDate($array['birth_date']),
	    'telephone' => $array['telephone'],
	    'mobile' => $array['mobile'],
	    'nivel' => $array['nivel'],
	    'status' => $array['status']
    );
}

$output = array(
	"draw"       =>  intval($_POST["draw"]),
	"iTotalRecords" => count($row),
	"iTotalDisplayRecords" => count($row),
	"aaData" => $row
);

$response = json_encode($output);
echo $response;

?>

$(document).ready(function () {

  var table = $('#myTable').DataTable( {
        dom: 'Bfrtip', 
        processing:true,
        serverSide:true,
        ajax:{
          "url": "php_action/equipAll.php",
          "type":"POST"
        },
        autoWidth: false,
        responsive: true,
        orderCellsTop: true,
        bFilter: true,
        fixedHeader: false,
        deferRender: true,
        pagingType: "full_numbers", 
        lengthMenu: [
            [ 10, 25, 50, -1 ],
            [ '10 linhas', '25 linhas', '50 linhas', 'Todos' ]
        ],
        searching: true,
        select: true,        
        select: {
            style: 'multi+shift',
        },    
        columns: [
          { data: 'admin_id' },
          { data: 'action' },
          { data: 'first_name'},
          { data: 'last_name' },
          { data: 'email' },
          { data: 'gender',
              "defaultContent": "",
              "fnCreatedCell": function (nTd, sData, oData, iRow, iCol) {
                if("1"==sData){
                  $(nTd).html("<i class='fas fa-venus ze-icon-m'></i>");
                } 
                else if("2"==sData){
                  $(nTd).html("<i class='fas fa-mars ze-icon-m'></i>");
                }                  
                else{
                  $(nTd).html("<i class='fas fa-venus-mars ze-icon-m'></i>");
                }  
              }
            },
            { data: 'birth_date' },
            { data: 'telephone' },
            { data: 'mobile' },
            { data: 'nivel',
                "defaultContent": "",
                "fnCreatedCell": function (nTd, sData, oData, iRow, iCol) {
                  if("1"==sData){
                    $(nTd).html("<i class='fas fa-user-tie ze-icon-m'></i>");
                  }  
                  else{
                    $(nTd).html("<i class='fas fa-user ze-icon-m'></i>");
                  }  
                }
            },
            { data: 'status',
                "defaultContent": "",
                "fnCreatedCell": function (nTd, sData, oData, iRow, iCol) {
                  if("2"==sData){
                    $(nTd).html("<i class='fas fa-toggle-on ze-icon-m'></i>");
                  }  
                  else{
                    $(nTd).html("<i class='fas fa-toggle-off ze-icon-m'></i>");
                  }  
                }  
            }            
        ],
        columnDefs: [
            {
                "targets": [ 0 ],
                "visible": true,
                "searchable": true,
                "data": "admin_id"
            },
            {
                "targets": [ 1 ],
                "data": null,
                "defaultContent": "<div class='btn-group' role='group' aria-label='Button group with nested dropdown'><div class='btn-group' role='group'><button id='btnGroupDrop1' type='button' class='btn btn-secondary dropdown-toggle' data-toggle='dropdown' aria-haspopup='true' aria-expanded='false'><i class='fas fa-cogs ze-icon-m' aria-hidden='true'></i></button><div class='dropdown-menu' aria-labelledby='btnGroupDrop1'><button type='button' class='btn btn-link' id='Detail' name='Detail'><i class='fas fa-eye ze-icon-m' aria-hidden='true'></i></button><button type='button' class='btn btn-link' id='Edit' name='Edit'><i class='fas fa-edit ze-icon-m'></i></button><button type='button' class='btn btn-link' id='confirmDelete' name='confirmDelete'><i class='fas fa-trash-alt ze-icon-m'></i></button><button type='submit' class='btn btn-link' id ='confirmStatus' name='confirmStatus'><i class='fas fa-toggle-on ze-icon-m'></i></button></div></div></div> "
            },            
            {
                "targets": [ 2 ],
                "visible": true,
                "searchable": true,
                "data": "first_name"
            },
            {
                "targets": [ 3 ],
                "visible": true,
                "searchable": true,
                "data": "last_name"
            },
            {
                "targets": [ 4 ],
                "visible": true,
                "searchable": true,
                "data": "email"
            },
            {
                "targets": [ 5 ],
                "visible": false,
                "searchable": true,
                "data": "gender",
            },
            {
                "targets": [ 6 ],
                "visible": false,
                "searchable": true,
                "data": "birth_date"
            },
            {
                "targets": [ 7 ],
                "visible": false,
                "searchable": true,
                "data": "telephone"
            },
            {
                "targets": [ 8 ],
                "visible": false,
                "searchable": true,
                "data": "mobile"
            },
            {
                "targets": [ 9 ],
                "visible": false,
                "searchable": true,
                "data": "nivel"
            },
            {
                "targets": [ 10 ],
                "visible": false,
                "searchable": false,
                "data": "status"
            }    
        ],
        buttons: [
            {
              extend:    '',
              text:      '<i class="fa fa-user-plus ze-icon-gg" style="color:blue;"></i>',
              titleAttr: 'Novo Registro',
              action: function (e, node, config){
                $('#myModalInsert').modal('show')
              }
            },
            {
              extend:    'pageLength',
              text:      '<i class="fas fa-list-ol ze-icon-gg" style="color:purple;"></i>',
              titleAttr: 'Mostrar',                   
            },   
            {
              extend:    'copyHtml5',
              text:      '<i class="fas fa-copy ze-icon-gg" style="color:gray;"></i>',
              titleAttr: 'Copiar',       
              key: {
                key: 'c',
                altKey: true
              },
              exportOptions: {
                modifier: {
                  page: 'current',
                  columns: ':visible'              
                }
              }  
            },
            {
	            extend:    'excelHtml5',
              text:      '<i class="fas fa-file-excel ze-icon-gg" style="color:green;"></i>',
              titleAttr: 'Excel',
              exportOptions: {
	              modifier: {
	                page: 'current',
                  columns: ':visible'              
	              }
	            }            	
            },            
            {
              extend:    'pdfHtml5',
              text:      '<i class="far fa-file-pdf ze-icon-gg" style="color:red; background-color:transparent;"></i>',
              titleAttr: 'PDF',
              orientation: 'A4', //portrait or landscape
              alignment: 'center',     
              pageSize:  'LEGAL',
              exportOptions: {
                  columns: ':visible'                 
              }        
            },
            {
              extend:    'print',
              text:      '<i class="fas fa-print ze-icon-gg" style="color:orange;"></i>',
              titleAttr: 'Print',
              exportOptions: {
                columns: ':visible'              
              }          
            },
            {
              extend:    'colvis',
              text:      '<i class="fas fa-columns ze-icon-gg"></i>',
              titleAttr: 'Alterar Colunas Visiveis',
              "columns": ':not(.noVis)'         
        	  },
            {
              extend:    'selectNone',
              text:      '<i class="fas fa-th ze-icon-gg"></i>',
              titleAttr: 'Remover todos os selecionados'
            },
            {
              extend:    'selectRows' ,
              text:      '<i class="fas fa-minus ze-icon-gg"></i>',
              titleAttr: 'Selecionar linha(s)'
            },           {
              extend:    'selectColumns',
              text:      '<i class="fas fa-th-large ze-icon-gg"></i>',
              titleAttr: 'Selecionar coluna(s)'
            },
            {
              extend:    'selectCells',
              text:      '<i class="fas fa-object-group ze-icon-gg"></i>',
              titleAttr: 'Selecionar celula(s)'
            }                                           
        ],
        language: {
          "decimal":        "",
          "emptyTable":     "Não avaliação deste registro",
          "info":           "Mostrar de _START_ a _END_ de _TOTAL_ registros",
          "infoEmpty":      "Mostrar 0 de 0 de 0 registros",
          "infoFiltered":   "(Total de _MAX_ registros de entradas)",
          "infoPostFix":    "",
          "thousands":      ",",
          "lengthMenu":     "Mostrar _MENU_ registros",
          "loadingRecords": "Carregando...",
          "processing":     "Processando...",
          "search":         "Buscar:",
          "zeroRecords":    "Não há registros",
          "paginate": {
              "first":      "<<",
              "last":       ">>",
              "next":       ">",
              "previous":   "<" 
          },
          "loadingRecords": '&nbsp;',
          "processing": 'Processando...',        
          "aria": {
            "sortAscending":  ": Coluna ativa em ordem ascendente",
            "sortDescending": ": Coluna ativa em ordem descendente"
          },
          "select": {
              "rows": {
                "_": "(Selecionado %d linhas)",
                "0": "(Nenhuma linha selecionada)",
                "1": "(Selecionado 1 linha)"
              },
              "columns": {
                "_": "(Selecionado %d colunas)",
                "0": "(Nenhuma coluna selecionada)",
                "1": "(Selecionado 1 coluna)"
              }
          },
          "buttons": {
              "copy": "Copiar para a área de transferência",
              "copyTitle": "Cópia bem sucedida",
              "copySuccess": {
                  "1": "Uma linha copiada com sucesso",
                  "_": "%d linhas copiadas com sucesso"
              }
          }                      
        }

  });

<table id="myTable" class="table table-striped table-hover display nowrap dataTable" cellspacing="0">
   <thead class="thead-dark">
    <th class="ze-center" data-priority="1">ID</th>
   	<th class="ze-center" data-priority="1">Opções</th>
   	<th class="w-50" data-priority="1">Nome</th>
    <th class="w-50" data-priority="1">Sobrenome</th>
    <th class="ze-center">E-mail</th>
    <th class="ze-center">Gênero</th>
    <th class="ze-center">Dt Nasc.</th>
    <th class="ze-center">Telefone</th>
    <th class="ze-center">Mobile</th>
    <th class="ze-center">Nivel</th>
    <th class="ze-center">Status</th>
   </thead>
   <tbody>

<?php
    if (count($result) == 0) {
?>
      <td colspan="4">Não há Membros Cadastrados</td>
<?php
    }
    else {   	

	foreach ($result as $array) {
?>
    <tr>
      <td><?php echo utf8_encode($array['admin_id']); ?></td>
  	  <td>
	    </td>
	  <td class="ze-left">
      <?php echo utf8_encode($array['first_name']); ?>
	  </td>
    <td><?php echo utf8_encode($array['last_name']); ?></td>   
    <td><?php echo utf8_encode($array['email']); ?></td>
    <td><?php echo ($array['gender'] == 1 ? "Masculino" : "Feminino"); ?></td>
    <td><?php echo utf8_encode($array['birth_date']); ?></td>
    <td><?php echo utf8_encode($array['telephone']); ?></td>
    <td><?php echo utf8_encode($array['mobile']); ?></td>
    <td><?php echo utf8_encode($array['nivel']); ?></td>
    <td><?php echo utf8_encode($array['status']); ?></td>
	</tr>
  <?php }
  } ?>
	</tbody>
   <tfoot class="thead-dark">
    <th class="ze-center">ID</th>
   	<th class="ze-center">Opções</th>
   	<th>Nome</th>
    <th>Sobrenome</th>
    <th class="ze-center">E-mail</th>
    <th class="ze-center">Gênero</th>
    <th class="ze-center">Dt Nasc.</th>
    <th class="ze-center">Telefone</th>
    <th class="ze-center">Mobile</th>
    <th class="ze-center">Nivel</th>
    <th class="ze-center">Status</th>
   </tfoot>
</table>

Answer 1

The page needs to be worked on in PHP, so you can browse, search and sort...

Follow the code done and working:

    <?php

include '../datasourceGestao.php';

$datasource = new datasource();

## Recebendo os $_POST

$draw = $_POST['draw'];

$row = $_POST['start'];

$row_per_page = $_POST['length']; // Linha por página

$column_index = $_POST['order'][0]['column']; // Column index

$column_name = $_POST['columns'][$column_index]['data']; // Column name

if($column_name=="admin_id" or $column_name=="action"){ $column_name = "first_name"; }

$column_sort_order = $_POST['order'][0]['dir']; // asc or desc

$search_value = $_POST['search']['value']; // Search value

## Resultados

if(!empty($column_index)){ $order = $column_name ." " . $column_sort_order . " "; }
else{ $order = "first_name ASC "; }

if($row_per_page != -1){ $pages = $row . ", " . $row_per_page; }
else{ $pages = "1, 10";  }

if(!empty($search_value)){

    $result = $datasource->getAllEquipSearch($search_value,$order,$pages);
}
else{

    $result = $datasource->getAllEquip($order,$pages);
}

$data = array();    

foreach ($result[0] as $array) {
    $data[] = array('admin_id' => $array['admin_id'],
        'first_name' => $array['first_name'],
        'last_name' => $array['last_name'],
        'email' => $array['email'],
        'gender' => $array['gender'],
        'birth_date' => $datasource->invertDate($array['birth_date']),
        'telephone' => $array['telephone'],
        'mobile' => $array['mobile'],
        'nivel' => $array['nivel'],
        'status' => $array['status']
    );
}

$output = array(
    "draw"       =>  intval($_POST["draw"]),
    "iTotalRecords" => $result[1],
    "iTotalDisplayRecords" => count($result[0]), // Total de Filtrados
    "aaData" => $data
);

echo json_encode($output);

?>

How I use datasource.php for uploads, follow:

public function getAllEquip($order,$pages) {
        try {
            $stmt = $this->conn->prepare("SELECT admin_id, first_name, last_name, email,
                                            gender, birth_date, telephone, mobile, nivel, status  
                                            FROM tbl_admins
                                            ORDER BY $order
                                            LIMIT $pages");
            $stmt->execute();

            $stmt2 = $this->conn->prepare("SELECT admin_id, first_name, last_name, email,
                                            gender, birth_date, telephone, mobile, nivel, status  
                                            FROM tbl_admins");
            $stmt2->execute();

            if ($stmt->rowCount() > 0) {
                $result = array (
                    $stmt->fetchAll(),
                    $stmt2->rowCount(),
                );
                return $result;         
            }
        }
        catch(PDOExeption $e) {
            http_response_code(500);
            sql_error("MYSQL ERROR: " . $e->getMessage() . "<br/>");
        }
    }

    public function getAllEquipSearch($search_value,$order,$pages) {
        try {
            $stmt = $this->conn->prepare("SELECT admin_id, first_name, last_name, email,
                                            gender, birth_date, telephone, mobile, nivel, status  
                                            FROM tbl_admins
                                            WHERE first_name LIKE '%$search_value%' 
                                            OR last_name LIKE '%$search_value%' 
                                            OR email LIKE '%$search_value%' 
                                            OR telephone LIKE '%$search_value%' 
                                            OR mobile LIKE '%$search_value%'
                                            ORDER BY $order
                                            LIMIT $pages");
            $stmt->execute();

            $stmt2 = $this->conn->prepare("SELECT admin_id, first_name, last_name, email,
                                            gender, birth_date, telephone, mobile, nivel, status  
                                            FROM tbl_admins
                                            WHERE first_name LIKE '%$search_value%' 
                                            OR last_name LIKE '%$search_value%' 
                                            OR email LIKE '%$search_value%' 
                                            OR telephone LIKE '%$search_value%' 
                                            OR mobile LIKE '%$search_value%'");
            $stmt2->execute();

            if ($stmt->rowCount() > 0) {
                $result = array (
                    $stmt->fetchAll(),
                    $stmt2->rowCount(),
                );
                return $result;
            }
          }
        catch(PDOExeption $e) {
            http_response_code(500);
            sql_error("MYSQL ERROR: " . $e->getMessage() . "<br/>");
        }
    }