Data tables not populating Showing 0 to 0 of 0 entries (filtered from 16,126 total entries)

Data tables not populating Showing 0 to 0 of 0 entries (filtered from 16,126 total entries)

SkuizSkuiz Posts: 5Questions: 4Answers: 0

Good Afternoon.

Im using data tables to get a responsive editing table.
However, the table its not filling at all.
Its reading the info from database but not displaying on the table, just showing no matching records found.

Here is my server response
{"draw":"1","recordsTotal":16126,"recordsFiltered":0,"data":[]}

Fetch Code

<?php



include('guardaMedidasPrestashop.php');

$column = array("id_product", "name", "reference", "width", "height", "depth", "weight", "volumen", "categoria");

$query = "SELECT ppl.id_product, 
ppl.name,
psa.id_stock_available,
psa.reference, 
psa.width, 
psa.height, 
psa.depth, 
psa.weight, 
psa.volumen, 
psa.categoria 
from prstshp_stock_available as psa left join prstshp_product_lang ppl on psa.id_product = ppl.id_product ";



if(isset($_POST["search"]["value"]))
{
    $query .= '
    WHERE psa.reference LIKE "%'.$_POST["search"]["value"].'%" 
    OR ppl.id_product = "%.$_POST["search"]["value"].%" 
    OR ppl.name LIKE "%'.$_POST["search"]["value"].'%" 
    ';
}

if(isset($_POST["order"]))
{
    $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
    $query .= ' ORDER BY psa.id_stock_available ASC ';
}

$query1 = '';

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



$statement = $connect->prepare($query);



$statement->execute();

$number_filter_row = $statement->rowCount();



$result = $connect->query($query);

$data = array();



foreach($result as $row)
{
    $sub_array = array();
    $sub_array[] = $row['id_product'];
    $sub_array[] = $row['name'];
    $sub_array[] = $row['reference'];
    $sub_array[] = $row['width'];
    $sub_array[] = $row['height'];
    $sub_array[] = $row['depth'];
    $sub_array[] = $row['weight'];
    $sub_array[] = $row['volumen'];
    $sub_array[] = $row['categoria'];
    $data[] = $sub_array;


}



function count_all_data($connect)
{
    $query = " SELECT ppl.id_product, 
ppl.name,
psa.id_stock_available,
psa.reference, 
psa.width, 
psa.height, 
psa.depth, 
psa.weight, 
psa.volumen, 
psa.categoria 
from prstshp_stock_available as psa left join prstshp_product_lang ppl on psa.id_product = ppl.id_product ";



    $statement = $connect->prepare($query);

    $statement->execute();

    return $statement->rowCount();


}



$output = array(
    'draw'      =>  $_POST['draw'],
    'recordsTotal'  =>  count_all_data($connect),
    'recordsFiltered'   =>  $number_filter_row,
    'data'      =>  $data,

);


echo json_encode($output);


?>

Page Code

<html>
    <head>
        <title>Dimensiones de Productos Prestashop</title>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
        <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
        <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>  
        <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
        <link href="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.1/bootstrap3-editable/css/bootstrap-editable.css" rel="stylesheet">
        <script src="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.1/bootstrap3-editable/js/bootstrap-editable.js"></script>
    </head>
    <body>
        <div class="container">
            <h3 align="center">Dimensiones de Productos Prestashop</h3>
            <br />
            <div class="panel panel-default">
                <div class="panel-heading">Productos Prestashop</div>
                <div class="panel-body">
                    <div class="table-responsive">
                        <table id="sample_data" class="table table-bordered table-striped">
                            <thead>
                                <tr>
                                    <th>ID Producto</th>
                                    <th>Nombre</th>
                                    <th>Referencia</th>
                                    <th>Ancho</th>
                                    <th>Largo</th>
                                    <th>Profundidad</th>
                                    <th>Peso</th>
                                    <th>Volumen</th>
                                    <th>Categoria</th>
                                </tr>
                            </thead>
                        </table>
                    </div>
                </div>
            </div>
        </div>
        <br />
        <br />
    </body>
</html>



<script type="text/javascript" language="javascript">

$(document).ready(function(){
    var dataTable = $('#sample_data').DataTable({

        "processing": true,
        "serverSide": true,
        "order":[],
        "ajax":{
            url:"dataProductosPrestashop.php",
            type:"POST",
     data :[]
        },
        createdRow:function(row, data, rowIndex)
        {
            $.each($('td', row), function(colIndex){

                if(colIndex == 1)
                {
                    $(this).attr('data-name', 'id_product');
                    $(this).attr('class', 'id_product');
                    $(this).attr('data-type', 'text');
                    $(this).attr('data-pk', data[0]);
                }


                if(colIndex == 2)
                {
                    $(this).attr('data-name', 'name');
                    $(this).attr('class', 'name');
                    $(this).attr('data-type', 'text');
                    $(this).attr('data-pk', data[0]);
                }
                if(colIndex == 3)
                {
                    $(this).attr('data-name', 'reference');
                    $(this).attr('class', 'reference');
                    $(this).attr('data-type', 'text');
                    $(this).attr('data-pk', data[0]);
                }
                if(colIndex == 4)
                {
                    $(this).attr('data-name', 'width');
                    $(this).attr('class', 'width');
                    $(this).attr('data-type', 'text');
                    $(this).attr('data-pk', data[0]);
                }
                if(colIndex == 5)
                {
                    $(this).attr('data-name', 'height');
                    $(this).attr('class', 'height');
                    $(this).attr('data-type', 'text');
                    $(this).attr('data-pk', data[0]);
                }
                if(colIndex == 6)
                {
                    $(this).attr('data-name', 'depth');
                    $(this).attr('class', 'depth');
                    $(this).attr('data-type', 'text');
                    $(this).attr('data-pk', data[0]);
                }
                if(colIndex == 7)
                {
                    $(this).attr('data-name', 'weight');
                    $(this).attr('class', 'weight');
                    $(this).attr('data-type', 'text');
                    $(this).attr('data-pk', data[0]);
                }

                if(colIndex == 8)
                {
                    $(this).attr('data-name', 'volumen');
                    $(this).attr('class', 'volumen');
                    $(this).attr('data-type', 'text');
                    $(this).attr('data-pk', data[0]);
                }
                if(colIndex == 9)
                {
                    $(this).attr('data-name', 'categoria');
                    $(this).attr('class', 'categoria');
                    $(this).attr('data-type', 'text');
                    $(this).attr('data-pk', data[0]);
                }
            });
        }
    });


        $('#sample_data').editable({
        container:'body',
        selector:'td.width',
        url:'EditarMedidasPrestashop.php',
        title:'Ancho',
        type:'POST',
        validate:function(value){
            if($.trim(value) == '')
            {
                return 'Se Necesita Campo';
            }
        }
    });
            $('#sample_data').editable({
        container:'body',
        selector:'td.height',
        url:'EditarMedidasPrestashop.php',
        title:'Largo',
        type:'POST',
        validate:function(value){
            if($.trim(value) == '')
            {
                return 'Se Necesita Campo';
            }
        }
    });

        $('#sample_data').editable({
        container:'body',
        selector:'td.depth',
        url:'EditarMedidasPrestashop.php',
        title:'Profundidad',
        type:'POST',
        validate:function(value){
            if($.trim(value) == '')
            {
                return 'Se Necesita Campo';
            }
        }
    });

        $('#sample_data').editable({
        container:'body',
        selector:'td.weight',
        url:'EditarMedidasPrestashop.php',
        title:'Peso',
        type:'POST',
        validate:function(value){
            if($.trim(value) == '')
            {
                return 'Se Necesita Campo';
            }
        }
    }); 

        $('#sample_data').editable({
        container:'body',
        selector:'td.volumen',
        url:'EditarMedidasPrestashop.php',
        title:'Volumen',
        type:'POST',
        validate:function(value){
            if($.trim(value) == '')
            {
                return 'Se Necesita Campo';
            }
        }
    });

        $('#sample_data').editable({
        container:'body',
        selector:'td.categoria',
        url:'EditarMedidasPrestashop.php',
        title:'Categoria',
        type:'POST',
        validate:function(value){
            if($.trim(value) == '')
            {
                return 'Se Necesita Campo';
            }
        }
    });


}); 
</script>

Answers

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

    I'm not familiar with PHP but the problem is occurring in your server script. It is not returning any data, ie "data":[]. I would start by debugging the SQL query that is being built and the result in $result = $connect->query($query);. That is what you are building the returned dataset from.

    Kevin

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    A few things I can see off the bat, but nothing that would explain the zero records:

    1)

    'draw' => $_POST['draw']

    For security make sure you do:

    'draw'      =>  intval($_POST['draw'])
    

    It prevents injection attacks.

    2) You don't use $query1 anywhere.

    3) $result = $connect->query($query); seems a little odd after you've just done a similar query to get the row count.

    As I say though, I don't think any of them explain the actual issue. I would suggest logging out $query and seeing what is being built.

    You might also want to consider using our demo SSP class as a library to build up your own script.

    Allan

Sign In or Register to comment.