Table very slowly using Datatable

Question

I’m bringing in 3,000 records from the MYSQL database using a connection to PDO and PHP. The time to be taken costs around 40 seconds to present all the data. Is Datatable unable to bring data by parts ?

Below follows all my code:

-HTML-

        <table class="m-datatable" width="100%">
                                <thead>
                                    <tr>
                <th>Código</th>
                <th>Nome do produto</th>
                <th>Complemento</th>
                <th>Situação</th>
                <th>Score</th>
                <th>Ação</th>


                                    </tr>
                                </thead>
                                <tbody>
                      <?php 
                      $sql = $db->prepare("SELECT pro.cod_produto, pro.nome, pro.status, pro.cod_a, pro.p_uni FROM produto pro");          
                      $sql->execute();   $a = 0  ;        
                                      while($row=$sql->fetch(PDO:: FETCH_ASSOC)){
                                        $a++; ?>
                                    <tr>
                  <td><? echo $row['cod_produto']?></td>
                                        <td><? echo $row['nome']?></td>
                                         <td data-field="CompanyAgent" class="m-datatable__cell">
                    <span>
                      <div class="m-card-user m-card-user--sm">                                                         
                        <div class="m-card-user__details">                                  
                          <span class="m-card-user__name">Cod Atuacao: <? echo $row['cod_a']?></span>                                   
                          <p class="m-card-user__email"><? echo $row['p_uni']?></p>                         
                        </div>                          
                      </div>
                    </span>
                  </td>
                                        <td><?php                                              
                        switch ($row['status']) {
                            case "A":
                                echo "<span style='width: 100px;'><span class='m-badge  m-badge--secondary m-badge--wide'>Ativo</span></span>";
                                break;
                            case "I":
                                echo "<span style='width: 100px;'><span class='m-badge  m-badge--danger m-badge--wide'>Inativo</span></span>";
                                break;
                        }
                        ?></td>
                  <td>

                      <?php

                      $c_produto = $row['cod_produto'];

                      $sql_score = $db->prepare(" SELECT * FROM pagamento rec WHERE rec.cod_produto = '$c_produto' ORDER by rec.vencimento DESC LIMIT 1");  
                      $sql_score->execute();     

                      while($row_vencimento = $sql_score->fetch(PDO::FETCH_ASSOC)){
                      $data =  $row_vencimento['vencimento'];

                       $_40 = strtotime("-40 days");
                       $_40 = date('Y-m-d', $_40);
                       $_60 = strtotime("-60 days");
                       $_60 = date('Y-m-d', $_60);
                       $_90 = strtotime("-90 days");
                       $_90 = date('Y-m-d', $_90);
                       $_180 = strtotime("-180 days");
                       $_180 = date('Y-m-d', $_180);

                        if ($data >= $_40) {
                          echo "<span style='width: 100px;'><span class='m-badge  m-badge--success m-badge--wide'>#A</span></span>";
                        } elseif (($data < $_40) && ($data >= $_60)){
                           echo "<span style='width: 100px;'><span class='m-badge  m-badge--info m-badge--wide'>#B</span></span>";
                      }elseif (($data < $_60) && ($data >= $_90)){
                           echo "<span style='width: 100px;'><span class='m-badge  m-badge--warning m-badge--wide'>#C</span></span>";
                        }elseif (($data < $_90) && ($data >= $_180)){
                         echo "<span style='width: 100px;'><span class='m-badge  m-badge--danger m-badge--wide'>#D</span></span>";
                       } elseif ($data < $_180) {
                         echo "<span style='width: 100px;'><span class='m-badge  m-badge--primary m-badge--wide'>#E</span></span>";                    
                        }


                     } ?>

                  </td>
                    <td>

                  B
                  </td>


                                    </tr>
                    <?php }?>
                                </tbody>
                            </table>

Below follows the JS file

var DatatableHtmlTableDemo={
  init:function(){
    var e;e=$(".m-datatable").mDatatable({
      data:{saveState:{
        cookie:!1}},search:{
          input:$("#generalSearch")}})}};jQuery(document).ready(function(){DatatableHtmlTableDemo.init()});

What can be done to automate the code and not take 40 seconds to display the 3 thousand items?

Answer 1

You need the server-side processing option offered by Datatables. Here you can see an example of server side Processing and here a class to simplify communication between Datatables and PHP.