DataTables recordsTotal: 0 while recordsFiltered: more than 0

DataTables recordsTotal: 0 while recordsFiltered: more than 0

CtlnLCtlnL Posts: 4Questions: 1Answers: 0

I'm having a bit of trouble, I'm using DataTables but some tables doesn't seen to fetch the results from the DB. A couple of tables show this in the PHP: {draw: 1, recordsTotal: 0, recordsFiltered: 3, data: []} data: [] draw: 1 recordsFiltered: 3 recordsTotal: 0

While the DataTable itself show this: Showing 1 to 3 of 3 entries (filtered from 0 total entries)

I don't know why those two tables have issues while the other work, I checked plenty of times if there is a wrong variable but is all fine.

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    Looks like you have server side processing enabled, ie, serverSide: true. Your server script is in control of the data returned and responsible for providing the proper information ins recordsTotal, recordsFiltered, etc as described in the Server Side Processing docs. The values of recordsTotal: 0, recordsFiltered: 3 aren't correct as recordsTotal should be all the records in your DB table.

    Are you using a Datatables provided server side script?

    Kevin

  • CtlnLCtlnL Posts: 4Questions: 1Answers: 0

    Yes, this is what I have in the JS

    var eventdataTable = $('#clase_data').DataTable({
            "processing":true,
            "serverSide":true,
            "order":[],
            "ajax":{
                url:"clase_fetch.php",
                type:"POST"
            },
            "columnDefs":[
                {
                    "targets":[1,2,3],
                    "orderable":false,
                },
            ],
            "pageLength": 10
        });
    

    Like I said, I don't have problem with my other tables so I don't understand the error here.

    And here is the PHP:

    <?php
    
    
    include('db.php');
    
    
    $query = '';
    
    $output = array();
    $query .= "
        SELECT * FROM clase 
        INNER JOIN demografica ON demografica.idDemografica = clase.Demografica_idDemografica   
        INNER JOIN tipo_clase ON tipo_clase.idTipo_Clase = clase.Tipo_Clase_idTipo_Clase    
        ";
    
    if(isset($_POST["search"]["value"]))
    {
        $query .= 'WHERE idClase.NombreClase LIKE "%'.$_POST["search"]["value"].'%" ';
        $query .= 'OR demografica.NombreDemografica LIKE "%'.$_POST["search"]["value"].'%" ';
        $query .= 'OR tipo_clase.NombreTipoClase LIKE "%'.$_POST["search"]["value"].'%" ';
        
    }
    
    if(isset($_POST['order']))
    {
        $query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
    }
    else
    {
        $query .= 'ORDER BY idClase DESC ';
    }
    
    if($_POST['length'] != -1)
    {
        $query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
    }
    $statement = $connect->prepare($query);
    $statement->execute();
    $result = $statement->fetchAll();
    $data = array();
    $filtered_rows = $statement->rowCount();
    foreach($result as $row)
    {
        
    
    $sub_array = array();
     $sub_array[] = $row['idClase'];
     $sub_array[] = $row['NombreClase'];
     $sub_array[] = $row['NombreDemografica'];
     $sub_array[] = $row['NombreTipoClase'];
     $sub_array[] = '<button type="button" name="view" id="'.$row["idClase"].'" class="btn btn-info btn-xs view">Ver</button>';
     $sub_array[] = '<button type="button" name="update" id="'.$row["idClase"].'" class="btn btn-warning btn-xs update">Actualizar</button>';
     $sub_array[] = '<button type="button" name="delete" id="'.$row["idClase"].'" class="btn btn-danger btn-xs delete">Eliminar</button>';
     $data[] = $sub_array;
    
    }
    
    function get_total_all_records($connect)
    {
        $statement = $connect->prepare('SELECT * FROM clase');
        $statement->execute();
        return $statement->rowCount();
    }
    
    $output = array(
        "draw"              =>  intval($_POST["draw"]),
        "recordsTotal"      =>  $filtered_rows,
        "recordsFiltered"   =>  get_total_all_records($connect),
        "data"              =>  $data
    );
    
    echo json_encode($output);
    
    ?>
    
  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    Just guessing but it looks like these are backwards:

        "recordsTotal"      =>  $filtered_rows,
        "recordsFiltered"   =>  get_total_all_records($connect),
    

    Seems like it should be this:

        "recordsTotal"      =>  get_total_all_records($connect),
        "recordsFiltered"   =>  $filtered_rows,
    

    Kevin

  • CtlnLCtlnL Posts: 4Questions: 1Answers: 0

    Thank you Kevi, but I still have the problem just in the opposite. The DT show now : Showing 0 to 0 of 0 entries (filtered from 3 total entries)
    While the PHP show:
    draw: 1, recordsTotal: 3, recordsFiltered: 0, data: []}
    data: []
    draw: 1
    recordsFiltered: 0
    recordsTotal: 3

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    Answer ✓

    Looks like you aren't returning any data. I would start by debugging the SQL statement built in your script to validate it.

    Kevin

  • CtlnLCtlnL Posts: 4Questions: 1Answers: 0

    Thank you, I will do that.

  • mayla15mayla15 Posts: 1Questions: 0Answers: 0

    did you fix this problem already bro? im having this problem also

  • laurenzmelolaurenzmelo Posts: 1Questions: 0Answers: 0

    Is this already solved? I'm also looking for the answer of this.

This discussion has been closed.