incorporate json object into datatables

Question

I have json data that's being imported to a page where I am trying to create a datatable.

the console.log(jsonReturn):

 {"1":{"dateReceived":"1905-07-04","cost":"2.7200","number":"757991"},"2":{"dateReceived":"1905-07-04","cost":"2.7200","number":"757991"}}

Below is my var jsonReturn:

Array
(
    [1] => Array
        (
            [dateReceived] => 1905-07-04            
            [cost] => 2.7200
            [number] => 757991

        )

    [2] => Array
        (
            [dateReceived] => 1905-07-04
            [cost] => 2.7200
            [number] => 757991

        )

    [3] => Array
        (
            [dateReceived] => 1905-07-04
            [cost] => 2.7200
            [number] => 757991

        )

)

I've read through every example on the DataTables website and I can't find anything on how to utilize this variable to fill my table. Apart from initializing the table:

$(document).ready(function(){    
    var table = $('#ltc-table').dataTable( {
    "aaData" : jsonReturn,   
});

Setting aaData to jsonReturn loads something into my table, but it's just gibberish. I need to know how to tell DataTables exactly where to find the data I want it to use to populate the table, and which rows I want to display where, etc.

I arrive at this javascript var by doing the following:

The user searches with a text-box, which goes off to an ajax call.

$.ajax({
      type: 'POST',
      url: 'quoteManagerSearch.php',      
      data: {searchBy:searchBy, searchValue:searchValue, options:options},        
        success:function(data){                
        jsonReturn = data;
        $("#quoteReturn").load("return.php");
        //$("#quoteReturn").html(data);                                            
        }
  }); //close ajax call


$searchQuery  = "SELECT * FROM alldata where $searchBy like '%$searchValue%'" ;
$searchResult = mysqli_query($con, $searchQuery);

$x = 0;
while ($row = mysqli_fetch_row($searchResult)) {

$x = $x + 1;


    $output[$x]["dateReceived"] = convertDate($row[1]);
    $output[$x]["cost"] = $row[15];
    $output[$x]["number"] = $row[16];
    }


echo json_encode($output);

on success, return.php is loaded into a div, and var jsonReturn is created.

Then, in return.php I try to use my invalid object.

Update

In response to @davidkonrad's post:

I'm getting the following error now:

DataTables warning: JSON data from server could not be parsed.  This is caused by a JSON formatting error.

From what I can see (minus the quotation marks,) my JSON string looks exactly like yours.

{"aaData":[ 
  {"dateReceived":"1905-07-04","cost":"2.7200","number":"757991"},
  {"dateReceived":"1905-07-04","cost":"2.9200","number":"337995"}
  ]
}

I also changed DataTable to dataTable since I'm using 1.9.4 for this test.

Update #2

I figured out what's going wrong!

The process, as I saw it, is as follows:

1. User loads quoteManager.php, and enters options in a textbox, then hits search. This value is converted into a javascript var, and sent off in this ajax call:

   $.ajax({
   type: 'POST',
   url: 'quoteManagerSearch.php',      
   data: {searchBy:searchBy, searchValue:searchValue, options:options},        
     success:function(data){                
     jsonReturn = data;
     $("#quoteReturn").load("quoteManagerReturn.php");
     }
   

   }); //close ajax call

2. quoteManagerSearch.php is the actual query against the database. This outputs a valid JSON string.

in quoteManager.php, there is a <div> at the bottom of the page for this response to load into:

<div id="quoteReturn">
</div>

3. I do something wrong here.

4. quoteManagerReturn.php is loaded into the #quoteReturn div on the quoteManager.php page - but without any of the data that quoteManagerSearch.php returned, because quoteManagerReturn.php is running its own ajax to quoteManagerSearch.php - not using the one I ran in the beginning, and this string, $searchBy = $_POST['searchBy']; is empty - so I get no results. This is why it's telling me my JSON is invalid, because it's empty!

5. What I need to do (which I'm not sure how to do...)

   1. Use jsonReturn in quoteManagerReturn.php - as this is the valid JSON response I want, instead of making a new ajax request to quoteManagerSearch.php, or
   2. Pass these variables over from quoteManager.php to quoteManagerReturn.phpand send them in the ajax request I'm making from quoteManagerReturn.php to quoteManagerSearch.php.

Update 2++

I sorted this out by just adding the variables into my ajax call on the second page:

var table = $('#ltc-table').DataTable( {    
      ajax : { 
       url : 'quoteManagerSearch.php' ,       
       dataSrc : 'aaData' ,
       type : 'POST',
       data: {searchBy:searchBy, searchValue:searchValue, options:options},
      },    

I've taken the original ajax call out of my first page (since there's no reason for the duplication...) I believe this is the solution!

Answer 1

your json is a javascript object with numbered properties:

{
  "1":{"dateReceived":"1905-07-04","cost":"2.7200","number":"757991"},
  "2":{"dateReceived":"1905-07-04","cost":"2.7200","number":"757991"}
}

should be an array of javascript objects like this:

[
  {"dateReceived":"1905-07-04","cost":"2.7200","number":"757991"},
  {"dateReceived":"1905-07-04","cost":"2.7200","number":"757991"}
]

Answer 2

I assume you are using dataTables 1.9.4 or similar, based on the reference to aaData. dataTables 1.9.x wants a JSON response that looks like

{ aaData : [ 
   { item }, 
   { item }, 
   ...
  ]
}

So in order to get the right format of the JSON, change the last part of your quoteManagerSearch.php script to this :

...
$output = array();
while ($row = mysqli_fetch_row($searchResult)) {
    //create { item }
    $item = array();
    $item["dateReceived"] = convertDate($row[1]);
    $item["cost"] = $row[15];
    $item["number"] = $row[16];
    //add { item } to [] 
    $output[] = $item;
}
//add [] array to aaData
$result = array('aaData' => $output);
echo json_encode($result);

Now you can initialise the dataTable and define "which values to read and where to put them" :

var table = $('#ltc-table').dataTable( {
    sAjaxSource : 'quoteManagerSearch.php',
    aoColumns : [
        { mDataProp : 'dateReceived' },
        { mDataProp : 'cost' },
        { mDataProp : 'number' }
    ]
});

---

As for dataTables 1.10.7, I would say that the above initialization would work well too. I tried it out with no errors. dataTables maps option names and so on in pairs with the old hungarian notation names and the new API naming standard, and even aaData works as default, even though dataTables now first looks for a data object. If you want to make the code fully "1.10.7 compliant" do this instead :

var table = $('#example').DataTable( {
    ajax : {
        url: 'quoteManagerSearch.php',
        dataSrc: 'aaData',
    },
    columns : [
        { data : 'dateReceived' },
        { data : 'cost' },
        { data : 'number' }
    ]
});

Answer 3

In case you're pulling data from a server side REST API, usually in newer versions of datatables you would do something like:

    $('#myTable').DataTable({
                    "language": language,               
                    "ajax": {
                        "url" : "/myservice/rest/" + params,
                        "error": function(reason) {
                            $('#myTable').html(errorMessage);
                            }
                        }
                    },
"columns": [
                    {"data": "orderNum"},
                    {"data": "orderDate"},
                    {"data": "customerName"},
// other column samples ...
                ],
    // other params ...
    });