$db->sql() Returning requested unknown parameter error.

doctorjnupe

I am attempting to use $db->sql(), but I am getting a requested unknown parameter error(code below):
server side:
$data = $db->sql("SELECT family, ctrl_id, rmf_name,assessment_procedure_number, cci_number,
cci_definition, implementation_guidance,validation_procedure, compelling_evidence,
control_not_applicable, comments, priority_control
FROM SYSTEM_ANNOTATION
WHERE report_source = 'SAPNIST' AND system_name = '$current_system'
AND system_id = '$systemid'")->fetchAll();
echo json_encode($results);

client side
$(document).ready(function() {
var current_system = <?php echo $current_system ?>;
var systemid = "<?php echo $systemid ?>";
$('#datatable-table-baseline').DataTable( {
dom: 'Blfrtip',
ajax: {
url: "server/processing_baseline.php",
type: "POST",
data: {current_system: current_system, systemid: systemid
}
},
pageLength: 10,
lengthMenu: [ [10, 25, 50, 75, 100, -1], [10, 25, 50, 75, 100, "All"] ],
scrollX: true,
scrollY: true,
serverSide: true,
columns: [
{ data: "family" },
{ data: "ctrl_id" },
{ data: "rmf_name" },
{ data: "assessment_procedure_number" },
{ data: "cci_number" },
{ data: "cci_definition" },
{ data: "implementation_guidance" },
{ data: "validation_procedure" },
{ data: "compelling_evidence" },
{ data: "control_not_applicable" },
{ data: "comments" },
{ data: "priority_control" }
],
buttons: [
{
extend: 'collection',
text: 'Export',
buttons: [
'copy',
'excel',
'csv',
'pdf',
'print'
]
}
]
});
});

doctorjnupe

Error: Requested unknown parameter 'family' at row 0, column 0.

doctorjnupe

Is anyone able to help with this question? I think that I'm very close. I see that the json response is being returned, but my datatables are not displaying.

allan

Can you show me the JSON that your server/processing_baseline.php script is returning please?

Allan

doctorjnupe

Post results as a document. I stripped about 20k lines because the file was to big. I hope you know the solution. I know that the answer is close, but there is something incorrect in my configuration.

allan

{
  "data": [
    [{

Its an array inside an array. Ideally you would want it to be:

{
  "data": [
    {

If you can't fix that on the server-side, then use ajax.dataSrc and set it to be data.0. However, if you can modify your sever-side script so it just returns an array of objects for the data property, that would be the best option.

Allan

doctorjnupe

So ignore my previous question. I figured out that
$results['data'] = $data;
echo json_encode($results);

will provide the desired array format, however, from a datatables perspective the updated format of
{
"data": [
{

display all results in a single page. I loose all ability to display count, change the number of items per row, etc. Basically all of the benefits of using datatables.

allan

Try just:

$results['data']= $data;

As you say. $data being an array is okay (you want it to be an array), but $results['data'][] would assign it to a new inner array element.

Allan

doctorjnupe

This issue with using $results['data']= $data; is that it displays all of the results in a single paged datatable. Basically, I the datatable loses its ability to count, changes number of results per page, etc. Initially, you lose all of the benefits of using datatables.

allan

Can you show me the JSON that is returned when you use that?

Allan