DataTable,PHP,Sqlite & Ajax - Request only returns the contents of the first column of each rec
DataTable,PHP,Sqlite & Ajax - Request only returns the contents of the first column of each rec
mjesus
Posts: 13Questions: 4Answers: 0
The html side: ...
var dataTable = $('#nv1_table').DataTable( {
"processing": true,
"serverSide": true,
"ajax":{
url :"BF_acessoBdSqlite_nv1.php", // json datasource
type: "post", // method , by default get
error: function(){ // error handling
$(".nv1_table_error").html("");
$("#nv1_table").append('<tbody class="nv1_table_error"><tr><th colspan="4">Nenhum dado encontrado no servidor</th></tr></tbody>');
$("#nv1_table_processing").css("display","none");
}
}
} );
} );
</script>
<style>
div.container {
margin: 0 auto;
width:96%;
}
div.header {
margin: 10px auto;
color: green;
line-height:30px;
width:96%;
}
body {
background: #f7f7f7;
color: #333;
font: 90%/1.45em "Helvetica Neue",HelveticaNeue,Verdana,Arial,Helvetica,sans-serif;
}
</style>
</head>
<body>
<div class="header"><h1>Entidade NV1</h1></div>
<div class="container">
<table id="nv1_table" cellpadding="0" cellspacing="0" border="0" class="display" width="100%">
<thead>
<tr>
<th>ID </th>
<th>CNF </th>
<th>ORDEM </th>
<th>TN1 </th>
<th>CONFIG </th>
<th>CMT </th>
<th>MRID </th>
</tr>
</thead>
</table>
</div>
</body>
</html>
the php side...
$requestData= $_REQUEST;
$columns = array(
0 => 'id',
1 => 'cnf',
2 => 'ordem',
3 => 'tn1',
4 => 'config',
5 => 'cmt',
6 => 'mrid'
);
$sql = "SELECT count(*) as x FROM nv1 ";
$query = $sqlite_db->query($sql) or die("BF_acessoBdSqlite_nv1.php: get nv1");
$aux = $query->fetch(PDO::FETCH_ASSOC); // para resgatar todo o array
$totalData = $aux["x"];
$totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.
$filter = "";
if( !empty($requestData['search']['value']) ) {
$filter .=" AND (id LIKE '" . $requestData['search']['value'] . "%' ";
$filter .=" OR cnf LIKE '" . $requestData['search']['value'] . "%' ";
$filter .=" OR ordem LIKE '" . $requestData['search']['value'] . "%' ";
$filter .=" OR tn1 LIKE '" . $requestData['search']['value'] . "%' ";
$filter .=" OR config LIKE '" . $requestData['search']['value'] . "%' ";
$filter .=" OR cmt LIKE '" . $requestData['search']['value'] . "%' ";
$filter .=" OR mrid LIKE '" . $requestData['search']['value'] . "%' ) ";
$sql = "SELECT count(*) as x FROM nv1 WHERE 1 = 1 ";
$sql .= $filter;
$query = $sqlite_db->query($sql) or die("Com o search: BF_acessoBdSqlite_nv1.php: get nv1");
$aux = $query->fetch(PDO::FETCH_ASSOC);
$totalFiltered = $aux["x"];
}
$sql = "SELECT * FROM nv1 WHERE 1 = 1 ";
$sql .= $filter;
$sql .=" ORDER BY " . $columns[$requestData['order'][0]['column']] . " " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . " ";// adding length
$query = $sqlite_db->query($sql) or die("Erro no sql: BF_acessoBdSqlite_nv1.php: get nv1");
$data = array();
foreach ($sqlite_db->query($sql) as $row) { // preparing an array
$nestedData=array();
$nestedData[] = $row["ID"];
$nestedData[] = $row["CNF"];
$nestedData[] = $row["ORDEM"];
$nestedData[] = $row["TN1"];
$nestedData[] = $row["CONFIG"];
$nestedData[] = $row["CMT"];
$nestedData[] = $row["MRID"];
$data[] = $nestedData;
...
Thank you for your time!
Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.
Answers
If you could:
that would let us provide some assistance. For example, I'm not clear if the issue is client-side or server-side. Being able to see the JSON returned in the example will help me to narrow it down. The forum rules clearly ask for a test case with each post, as does the template text for a new question.
Thanks,
Allan
I noticed that this correlation
$columns = array(
0 => 'id',
1 => 'cnf',
2 => 'ordem',
3 => 'tn1',
4 => 'config',
5 => 'cmt',
6 => 'mrid'
);
was not accepted.
and when replacing the literal
$nestedData[] = $row["ID"];
$nestedData[] = $row["CNF"];
$nestedData[] = $row["ORDEM"];
$nestedData[] = $row["TN1"];
$nestedData[] = $row["CONFIG"];
$nestedData[] = $row["CMT"];
$nestedData[] = $row["MRID"];
with a number
$nestedData[] = $row[0];
$nestedData[] = $row[1];
$nestedData[] = $row[2];
$nestedData[] = $row[3];
$nestedData[] = $row[4];
$nestedData[] = $row[5];
$nestedData[] = $row[6];
All the data was displayed,... Does anyone know why?
Please re-read Allan's post.
Have you tried:
When you're referring to the fields in the rest of your code, you're using lowercase. How is your DB set up... uppercase field names or lowercase field names? I believe you generally want to be consistent in how you're referring to them based on their case.
This works because you're simply referencing the position of the field within the results of the query. It's only lining up with your
$columnsif your table in the DB has the columns in the same order. I'm not seeing where you're actually referencing the$columnsin your query. I seeSELECT * FROM nv1..., so I assume the nv1 table has the columns in the same order as your$columnsarray.TL;DR...if you're referencing the actual field name in your foreach loop, make sure the case is the same as your table in the DB.
Use the browser's network inspector tool to get the JSON response. Use the steps in this technote.
Kevin