Json format for dataTables

classic12

Sorry if doubles posted....
I am using NSBasic which implements dataTables.

I am struggling to understand how to get data from MYSQL into the dataTable.

I think it is a formatting issues.

Test at http://www.toolfolks.com/AjaxPost/
I use the following PHP to get the results ( working okay )

$query5 = "SELECT invoice_no, product_name, delivery_status,pin_code ";  //intensionaly * to fetch all columns
$query5.=" FROM order_product";


if ($result = mysqli_query($mysqli, $query5)) {
  $out = array();

  while ($row = $result->fetch_assoc()) {
    //$out[] = $row;
    $out[] = $row['invoice_no'];
    $out[] = $row['product_name'];
    $out[] = $row['delivery_status'];
    //$out[] =  "]";
    //$out[] = $row['pin_code'];
  }
}
  /* encode array as json and output it for the ajax script*/
  echo json_encode($out);
  //var_dump($result);

I am using the following code:

```

Button1.onclick=function(){
req = Ajax("http://www.toolfolks.com/techy/someData.php", "GET", txtSend.value, done);
}
function done() {
if (req.status == 200) { //success
alert('data back');
htmResponse.innerHTML = req.responseText;
console.log('This is the data'+req.responseText);
// add to grid
var table = $("#DataTable1").DataTable();
table.clear();
var dataJson2 = JSON.stringify(htmResponse.innerHTML);
DataTable1.settings.data = req.responseText ;//JSON.parse(dataJson2);
setTimeout(loadTable, 50);
}
else {//'failure
htmResponse.innerHTML=req.err;
}
}
function loadTable() {
var table = $("#DataTable1").DataTable();
table.rows.add(DataTable1.settings.data).draw();
}
Button1Copy.onclick=function(){
$('#DataTable1').DataTable( {
"ajax": 'http://www.toolfolks.com/techy/someData.php'
} );
}

'''

Any help appreciated.
Be gentle with me as this is all new and 'brain hurty' time.
Cheers

Steve Warby

allan

Hi Steve,

Your PHP script is just returning one massive array of data - there is no separation into rows:

 ["704523213-6","Thyroidinum,","0","873879067-X","OCTINOXATE and TITANIUM DIOXIDE","0","888174546-1","coffee bean","0","742900466-0",...]

If you have a look at this section of the manual you'll see how the data should be structured (an array of arrays or array of objects).

The PHP needs to be modified - I'd simply use:

if ($result = mysqli_query($mysqli, $query5)) {
  $out = array();
 
  while ($row = $result->fetch_assoc()) {
    $out[] = $row;
  }
}
  /* encode array as json and output it for the ajax script*/
  echo json_encode($out);

Probably even better would be to drop the while loop:

$out = array();

if ($result = mysqli_query($mysqli, $query5)) {
  $out = $result->fetch_all(MYSQLI_ASSOC);
}

echo json_encode( $out );

This will use the object data form, so you'll need to make sure you use columns.data to tell DataTables which property to get the data from to display in each column.

Allan

classic12

Thanks for the info.
I have now changed the php and getting the data back in this format:

[{"Name":"Tom","Position":"Upright","Salary":"5555"},{"Name":"Andy","Position":"Upright","Salary":"5555"},{"Name":"John","Position":"Upright","Salary":"5555"},{"Name":"Paul","Position":"Upright","Salary":"5555"}]

I have hard wired this into the following:

    var localData = [{"Name":"Tom","Position":"Upright","Salary":"5555"},{"Name":"Andy","Position":"Upright","Salary":"5555"},{"Name":"John","Position":"Upright","Salary":"5555"},{"Name":"Paul","Position":"Upright","Salary":"5555"}]
    $("#DataTable1").dataTable().fnDestroy();
    //var table = $("#DataTable1").DataTable();
    //table.clear();
    $('#DataTable1').DataTable( {
    data: localData,
    columns: [
        { title: "Name" },
        { title: "Position" },
        { title: "Upright" }
                  ]
    } );

I get the error:

DataTables warning: table id=DataTable1 - Requested unknown parameter '0' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4

So I am destroying the table okay.

I presume it is the 'mapping' that is at fault but I cannot see what is wrong.

Cheers

Steve Warby.

classic12

Sorry the code is
$("#DataTable1").dataTable().fnDestroy();
//var table = $("#DataTable1").DataTable();
//table.clear();
$('#DataTable1').DataTable( {
data: localData1,
columns: [
{ title: "Name" },
{ title: "Position" },
{ title: "Salary" }
]
} );

ie the title matches each database field

allan

It should be columns.data you are using, not columns.title. Documentation for it is available here.

columns.title will set the text in the header cell for the column. You can use it if you want, but you do need to use columns.data.

Allan

classic12

Thanks allan. All new so I get bogged down sometimes...

I have the project here www.toolfolks.com/getTest

If I click on local this code runs okay:

var localData1 = [{"Name":"Tom","Position":"Upright","Salary":"5555"},{"Name":"Andy","Position":"Upright","Salary":"5555"},{"Name":"John","Position":"Upright","Salary":"5555"},{"Name":"Paul","Position":"Upright","Salary":"5555"}];
var localData2 = txtSend.value;
$("#DataTable1").dataTable().fnDestroy();
//var table = $("#DataTable1").DataTable();
//table.clear();
$('#DataTable1').DataTable( {
data: localData1,
columns: [
{ data: "Name" },
{ data: "Position" },
{ data: "Salary" }
]
} );

However when I click ajax button get the data back from the server I still get an error:

Button1.onclick=function(){
req = Ajax("http://www.toolfolks.com/techy/getTest.php", "GET", txtSend.value, done);
}

function done() {
if (req.status == 200) { //success
//alert('data back');
dataReturned = req.responseText;
htmResponse.innerHTML = req.responseText;
console.log('This is the data'+req.responseText);
// add to grid
var table = $("#DataTable1").DataTable();
table.clear();
var dataJson2 = JSON.parse(htmResponse.innerHTML);
DataTable1.settings.data = req.responseText ;//JSON.parse(dataJson2);
setTimeout(loadTable, 50);

}
      else {//'failure
      htmResponse.innerHTML=req.err;
      }

}

I have tried JSON.parse etc to correct the problem. I am presuming the returned data ,( although it looks the same ) is not in the correct format.

Sorry for what is probably a simple problem but I am struggling to get past the issue.

Cheers

Steve Warby

classic12

Okay fresh eyes this morning....

I was not using the same format of adding the data so this works:

'''
Button1Copy.onclick=function(){
req = Ajax("http://www.toolfolks.com/techy/getTest.php", "GET", txtSend.value, done2);
}

function done2() {
if (req.status == 200) { //success
alert('data back');
dataReturned = JSON.parse(req.responseText);
htmResponse.innerHTML = req.responseText;
$("#DataTable1").dataTable().fnDestroy();
//var table = $("#DataTable1").DataTable();
//table.clear();
$('#DataTable1').DataTable( {
data: dataReturned,
columns: [
{ data: "Name" },
{ data: "Position" },
{ data: "Salary" }
]
} );

}
      else {//'failure
      htmResponse.innerHTML=req.err;
      }

}
'''

allan

Cool. So are you happy that everything is working now?

Allan

classic12

Hi guys,

I'm struggling on something that is probably quite simple.

I am trying to populate a new datable with the following data;

I output the data to the console using

console.log('dealDetails = '+ JSON.stringify(dealDetails));

which gives me

{"quoteID":"521","quoteTitle":"TEST","notes":"<p><span style=\"color:null\"><span style=\"background-color:null\">NOTES TEST</span></span></p>\n\n<p>&nbsp;</p>\n\n<p><span style=\"color:null\"><span style=\"background-color:null\"><img alt=\"\" src=\"http://www.surplusanywhere.com/upload/413.jpg\" style=\"height:800px; width:800px\" /></span></span></p>\n","web_path":"http://www.surplusanywhere.com/upload/413.jpg"}

When I use the following it fails.

Button4.onclick=function(){
       $("#dtDetails").DataTable().destroy();
          $('#dtDetails').empty();
          console.log(dealDetails);
        tableDetails =  $('#dtDetails').DataTable( {
        data : dealDetails,
                 columns: [
            { data: "quoteID" },
            { data: "quoteTitle" },
            { data: "notes" }
                      ]
        
        });
}

So I tried var dealDetails2 = '['+JSON.stringify(dealDetails)+']';

which gives me

[{"quoteID":"521","quoteTitle":"TEST","notes":"<p><span style=\"color:null\"><span style=\"background-color:null\">NOTES TEST</span></span></p>\n\n<p>&nbsp;</p>\n\n<p><span style=\"color:null\"><span style=\"background-color:null\"><img alt=\"\" src=\"http://www.surplusanywhere.com/upload/413.jpg\" style=\"height:800px; width:800px\" /></span></span></p>\n","web_path":"http://www.surplusanywhere.com/upload/413.jpg"}]

if I hard wire this using

var consoleData = [{"quoteID":"521","quoteTitle":"TEST","notes":"<p><span style=\"color:null\"><span style=\"background-color:null\">NOTES TEST</span></span></p>\n\n<p>&nbsp;</p>\n\n<p><span style=\"color:null\"><span style=\"background-color:null\"><img alt=\"\" src=\"http://www.surplusanywhere.com/upload/413.jpg\" style=\"height:800px; width:800px\" /></span></span></p>\n","web_path":"http://www.surplusanywhere.com/upload/413.jpg"}]

This populates the datatable okay.

What am I not understanding here on the formats?

kthorngren

The last option you show is an array containing a single object which is correct. It is shown in the docs:
https://datatables.net/manual/data/#Objects

The first option is not correct because Datatablees expects and array of objects even if just one. The second is a string.

Kevin

classic12

Is there a method to convert the first option into the expected format ?

allan

dealDetails2 = '['+JSON.stringify(dealDetails)+']';

Makes it into a string. use:

dealDetails2 = [ JSON.stringify(dealDetails) ];

to place JSON.stringify(dealDetails) into an array.

The MDN documentation has information about arrays in it.

Allan