Json format for dataTables

Json format for dataTables

classic12classic12 Posts: 228Questions: 60Answers: 4

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

Replies

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    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

  • classic12classic12 Posts: 228Questions: 60Answers: 4

    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.

  • classic12classic12 Posts: 228Questions: 60Answers: 4

    Sorry the code is :smile:
    $("#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

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    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

  • classic12classic12 Posts: 228Questions: 60Answers: 4

    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

  • classic12classic12 Posts: 228Questions: 60Answers: 4

    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;
          }
    

    }
    '''

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

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

    Allan

  • classic12classic12 Posts: 228Questions: 60Answers: 4

    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?

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

    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

  • classic12classic12 Posts: 228Questions: 60Answers: 4

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

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    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

This discussion has been closed.