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)
Skuiz
Posts: 5Questions: 4Answers: 0
in DataTables
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
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
A few things I can see off the bat, but nothing that would explain the zero records:
1)
For security make sure you do:
It prevents injection attacks.
2) You don't use
$query1anywhere.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
$queryand 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