Too slow table using Datatable

2

I am bringing 3,000 MYSQL database records using a connection to PDO and PHP. The time to be taken costs around 40 seconds until all the data is presented. Is Datatable unable to bring data in pieces?

Below is my entire 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 is 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 the 40 seconds to display the 3 thousand items?

    
asked by anonymous 01.06.2018 / 14:46

1 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.

    
01.06.2018 / 22:01