Not able to load ajax json data into DataTables

BernardA

I AM UPDATING THIS: I FOUND OUT THAT I MISSED THE json_encode on my filter_range.php file and also I named the array as aaData.

What is happening now is that whenever I apply a filter I get only the first line of the table updated, nothing else.

Thanks!

I am new to DataTables and not quite an expert in javascript/jquery either.

My table is populated with the original php/html file and it works fine with DataTables (DT for short). So, we are not talking here about initialization of DT, or so I believe.

Now,once the table is visible to the user I need to give him/her the possibility to apply filters to the table. Note that the filter parameters are NOT within the table data, so it is not the case of using the sorting/filtering tools from DT. The filtering with ajax/php was working fine prior to the use of DT, but after implementing DT I do not manage to repopulate it with the new data coming from the Ajax call.
I did manage to empty the table using fnClearTable(), so all I get now once I apply any filter is an empty table.
Here is myTable as from the original php file, starting withe the filter section:

[code]


item1

item2

item3





data1
data2
data3
data4
data5
Compare



<?php
$sql = mysql_query ("SELECT * FROM vprice_range ORDER BY price");

while($r= mysql_fetch_assoc($sql)){
?>


<?php echo $r["data1"] ; ?>
<?php echo $r["data2"] ; ?>
<?php echo $r["data3"] ; ?>
<?php echo $r["data4"] ; ?>
<?php echo $r["data5"] ; ?>
Select:


<?php
}
unset ($sql);
unset ($r);
?>


[/code]

Here is my JQuery/Ajax so far ( placed at the section of the above php file.

[code]
$(document).ready(function(){
$("#filter").change(function()
{
var str = $("#filter").serialize();
var oTable = $('#myTable').dataTable();
// Immediately 'nuke' the current rows (perhaps waiting for an Ajax callback...)
oTable.fnClearTable();
$.ajax({
url: "filter_prange.php",
type: "POST",
dataType:"json",
data: str,
success: function fnClickAddRow(data) {
$('#myTable').dataTable().fnAddData(data);
}
});
});
});
[/code]

This is the filter_prange.php file.
[code]
$connect = mysql_connect("localhost", "root", "root");
mysql_select_db("mydb");
$trimids = array();
$trimids = $_REQUEST['hook'];
$qty = count($trimids);
$trimids = implode(",", $trimids);


if($qty == 0){
$sql = mysql_query ("SELECT * FROM vprice_range ORDER BY price");
}
else {
mysql_query("CREATE OR REPLACE VIEW vrange AS (
SELECT ................)
$sql = mysql_query ("SELECT * FROM vrange ORDER BY price");
}
$aaData=array();
while($r= mysql_fetch_assoc($sql)){
$aaData[] = '' . $r["data1"] .'' ;
$aaData[] = '' . $r["data2"] . '' ;
$aaData[] = '' . $r["data3"] . '' ;
$aaData[] = '' . $r["data4"] . '' ;
$aaData[] = '' . $r["data5"] . '' ;
$aaData[] = '' . 'Select:' . '' . '' ;
}
$aaData=json_encode($aaData);
echo $aaData;
unset ($sql);
unset ($row);
mysql_query("DROP VIEW vrange");
[/code]

And this is an example of the JSON I am getting back from the filter_prange.php file ( as verified by Firebug).
[code]
["data1<\/td>","data2<\/td>","data3<\/td>","data4<\/td>","data5<\/td>","Select:<\/td>", etc....
[/code]

As you can see in my jQuery I am trying to used fnAddData(), but it is not clear to me if that's the right plug in nor which parameters, if any, I should provide.
Thanks for any assistance.

rage10940

Why not use sAjaxSource and bProcessing ?


Also if you need to use another name for your JSON Array name then use sAjaxDataProp

this is to change from the expected aaData and use w/e name you think of, allowing your to have for example one model / controller echo 1 JSON array but with different query results in it.


Good luck.

BernardA

Thanks for your input, rage10940. I was under the impression that sAjaxSource was only applicable when one utilizes Ajax starting from initialization of the table, which is not my case. Also, I need to pass the filtering parameters to the php file using Ajax.
Are you saying that I should place the sAjaxSource into the 'success' handler of the Ajax query? That sounds a bit odd as at that point in time the data has already been received from the server.
Please advise.
Thanks

rage10940

Keep in mind I', quite new to DataTables, and I also hate JavaScript / Jquery (its hell) (for me)

But to the best of my knowledge (again which is very small on this subject) :

You can also use fnReloadAjax to have it refresh your data if that is what you mean? A sample would be like what I have :

[code]
$(document).ready(function()
{
$('#waiting').dataTable(
{
"sPaginationType":"full_numbers",
"bJQueryUI":true,
"iDisplayLength": 10,
"aLengthMenu": [5, 10],
"bProcessing": true,
"sAjaxDataProp": "waiting",
"sAjaxSource": 'https://www.finaidtest.com/index.php/studentqueue_controller/ajaxcall',
"bDeferRender": true,

[/code]

This sample only retrieves the data on first initialization but to have Ajax calls dynamically, like at a interval you would use :

[code]
var oTable = $('#waiting').dataTable( { YOUR CODE W/E it is } );

setInterval( function () {
oTable.fnReloadAjax();
}, 1000 ); <- - -- - - - AJAX calls every second

[/code]

for more on this though read into this :

http://datatables.net/plug-ins/api#fnReloadAjax

[quote]BernardA said: Also, I need to pass the filtering parameters to the php file using Ajax. [/quote]

what do you mean when you say that? The filtering will either be done via Client-Side (Datatables will do this) or by the server side in which you would use : bServerSide and then just use the php script Allan has created.

Hopefully that helps ?

BernardA

Not quite, rage10940, I believe I have a very straight forward Ajax call initiated by the user by selecting filters as per the #filter tag on top of my initial php file. This is AFTER initialization, so the table exists and at that point in time and DT is working fine. That's what I mean by passing filtering parameters.
Again, the filtering CANNOT be done by DT as the filtering parameters are EXTERNAL to the DT table data.
I am not interested and I do not believe I need to do server-side processing for the filtering/sorting that are normal to DT. I have a rather small table, anyways.
What is not happening properly is that the new set of data coming from the Ajax call and from the filter_prange.php file does not populate DT. As I said in my update ( see the very top of the initial post) I am only getting the first line populated. Nothing else.

BernardA

After many more hours on this, I found out that one of the issues is the format of the aaData object.
But again I run into trouble. I do not seem to be able to create the object in the required format.

This is one of many attempts to get this to work (with some external help ), but to no avail.

http://codepad.viper-7.com/gk2DO3

As I try to pass a JSON object in similar format thru fnReloadAjax it gives me a "type error oSettings is NULL"

Any ideas, please?

allan

If you could link to a test case we might be able to tell you what is going wrong. It sounds like the reference to the table is being lost.

Should also be noted that DataTables can handle almost any JSON format through the use of sAjaxDataProp and mData / mRender .

Allan

BernardA

Hi Allan

I am not sure how I can provide a link, this is still at localhost, and I am at a loss on how simulate any such issues on jsfiddle or similar stuff.
Let me give you some of the latest:
This is last version of the js
[code]

$(document).ready(function(){
$("#filter").change(function()
{
var str = $("#filter").serialize();
$.ajax({
url: "filter_prange.php",
type: "POST",
dataType:"json",
data: str,
success: function fnReloadAjax(){
$("myTable").dataTable().fnReloadAjax(null,"filter_prange.php",true);
}
});
});
});

[/code]

And here is a sample of the data coming from filter_prange.php, checked with Firebug. I am giving you the real stuff, not some edited version.

{"aaData":[
["Ssangyong","Kyron","Luxe BVA","304200","diesel","123019","431719","Select:"],["Citroen","DS4","2.0 HDi 160 ch Sport Chic","315000","diesel","88032","407532","Select:"],
etc...

Those are the heading of the table, which of course was already initialized and worked fine:

[code]



Brand
Model
Version
Price
Fuel
Total
Total5yr
Compare


......
[/code]

BTW, the error (TypeError: oSettings is null) is coming on line 8 of the fnReloadAjax plug-in:
[code]
if ( oSettings.oFeatures.bServerSide ) {
[/code]
Hope that helps. I will try to find a way to put that into a link, but do not hold your breath.

Thanks by the way for the excellent plug in. I am sure my difficulties are solely of my own doing.
BernardA

BernardA

Allan

To the best of my abilities, which is not much, I tried to place things in live.datatable.net. I do not seem to be able to trigger the fnReload Ajax, but at the least this gives you the baseline.
At
http://live.datatables.net/osumef/edit#source
I placed the html and the js with fnReloadAjax and then, following the video instructions from that site, place the json object in a separate file;
http://live.datatables.net/agojam/edit#javascript,html.
Thanks

allan

> $("example").dataTable()

I think you mean:

[code]
$("#example").dataTable()
[/code]

and the same in the above code with `myTable` .

Allan

BernardA

Hi Allan

Sorry it took a while to respond. I was self-flagellating for being so stupid. Of course I know that there should be a "#" there, but after so many changes I just messed it up.

So, no more errors given, but I still have a problem.

The GET generated by fnReloadAjax is not taking into consideration the filter parameters passed on to filter_prange.php by my Ajax function. So I am getting the data, but it is always the whole set that returns, because as no parameter is passed the filter_prange.php treats as is was a RESET and returns the original set back.
For each filter that is selected I get 2 set of data back from the server: one POST generated by the Ajax Query with the correct set of data and another GET generated by fnReloadAjax always with the full original set.

The data in "str" need to be passed on to filter_prange.php to get the correct data set.

The question is how can this be done within the fnReloadAjax?

The #filter can be found on the very top of this post.

[code]

$(document).ready(function(){
$("#filter").change(function()
{
var str = $("#filter").serialize();
$.ajax({
url: "filter_prange.php",
type: "POST",
dataType:"json",
data: str,
success: function fnReloadAjax(){
$("#myTable").dataTable().fnReloadAjax("filter_prange.php",true);
}
});
});
});

[/code]

BernardA

I am showing the latest filter_prange.php:

[code]$connect = mysql_connect("localhost", "root", "root");
mysql_select_db("cars");
$trimids = array();
$trimids = $_REQUEST['hook'];
$qty = count($trimids);
$trimids = implode(",", $trimids);


if($qty == 0){
$sql = mysql_query ("SELECT * FROM vprice_range ORDER BY price");
}
else {
mysql_query("CREATE OR REPLACE VIEW vrange AS (
SELECT vprice_range.*
FROM vprice_range
WHERE EXISTS (SELECT versiontrim.version_id
FROM versiontrim
WHERE versiontrim.trim_id IN ($trimids)
AND vprice_range.version_id = versiontrim.version_id
GROUP BY versiontrim.version_id
HAVING COUNT(distinct versiontrim.trim_id) = $qty))");

$sql1=mysql_query("SELECT COUNT(*) FROM vrange");
while($r1=mysql_fetch_assoc($sql1)){
$ct=$r1["COUNT(*)"];
}
$sql = mysql_query ("SELECT * FROM vrange ORDER BY price");
}


$aaData["aaData"] = array();
while($r= mysql_fetch_assoc($sql)){
$arr=array();
$arr[] = $r["brand"];
$arr[] = $r["model"] ;
$arr[] = $r["version"] ;
$arr[] = $r["price"] ;
$arr[] = $r["fuel"] ;
$arr[] = $r["Totalop"] ;
$arr[] = $r["Total5yr"] ;
$arr[]= 'Select:' . '' ;
array_push($aaData["aaData"],$arr);
}
$aaData=json_encode($aaData);
echo $aaData;
[/code]

allan

Right - if you want to send extra parameters to the server in the Ajax request, use fnServerParams - example: http://datatables.net/release-datatables/examples/server_side/custom_vars.html (this works for client-side processing, which you are using, as well as server-side processing, which the example is using).

Allan

BernardA

Hi Allan

Sorry, but I have not been able to make it work after looking at the documentation you indicated.

I am not getting to pass the parameters in "str" to DT, and also I am getting a warning "Cannot initialize DT" and something about passing no arguments and bRetrieve and bDestroy .

In fact I realized that it does not seem to make sense having fnReloadAjax wrapped by a Ajax call, as all parameters are redundant. Isn't that the case?
Thanks!

[code]

$(document).ready(function(){
$("#filter").change(function()
{
var str = $("#filter").serialize();
$.ajax({
url: "filter_prange.php",
type: "POST",
dataType:"json",
data: str,
success: function fnReloadAjax(){
$("#myTable").dataTable({
"fnServerParams": function ( aoData ) {
aoData.push({ "name": "str", "value": "str" });}
}).fnReloadAjax("filter_prange.php");
}
});
});
});

[/code]

GregP

If you want to initialize DT with a set of data rendered out by your PHP, you can do that with a combination of iDeferLoading and sAjaxSource. Then you're not trying to "manually" manage your Ajax calls.

Since you do in fact want the server to do some processing (even if it's using your custom parameters rather than the built-in ones), you should also bServerSide to true, which causes fnServerParams to behave differently (as a callback, you are adding your name-values to the returned data if I'm not mistaken).

While DT will eventually convert these to name-value pairs even for server-side, you should pass them in fnServerParams as key-value. So instead of { "name": "myName", "value": "myValue" } you would push {"myName":"myValue"}.

BernardA

Hi GregP,
Thanks for your post.
Somehow I find it all unnecessarily complicated. I liked it very much when I applied DT to my original table and everything worked well without any fuss.
I was kind of expecting the same behavior here. After all this is a straightforward Ajax call to replace the contents of a given table id. All there is to know is within that Ajax call. That's why it was set up that way. Also, no changes in format or structure of the table. I am no programming maven, but in a ideal scenario this should happen without any further set up dealings with DT. I would expect it to recognize whatever parameters are in that Ajax call and that a new set of data is coming to the table id where DT is applied to, process it and render it accordingly.
I am sure there are many reasons why it is not set up that way, but simplicity suffered here.
Well, that's just me rumbling. I will get this to work somehow.

GregP

Haha! I totally understand. The thing is, you've introduced complexity by design. It doesn't "have" to be complicated, but you have introduced information that you want filtering on. A no-parameters setup is still easy. :)

You can do it a different way that might make more sense to you. I don't have a code sample, but the outline should go like this:

1. Make your own custom Ajax call to your URL and have the data returned as JSON
2. Store this data to a JavaScript variable in the success callback
3. Render the dataTable using the "JS variable data source" method.

I don't see why that wouldn't work. There are a few housekeeping items that I'm probably forgetting or glossing over (for example, you can't just call oTable.dataTable() in the success callback since it will already be initialized) but it shouldn't be all that tough.

pjchavaux

sorry...

BernardA

Thanks GregP, I appreciate your input.

BernardA

Ok, I still did not give up on this.

I keep on trying with fnReloadAjax as there is more documentation to the proposed alternative.

Two things are happening ( or not happening ):

- fnServerParams is not passing parameters to php file. As before correct data is returned to the 'regular' Ajax call and the GET from fnReloadAjax gets always the full set, as it should be the case when no filtering parameters are passed.

- getting error 'Cannot reinitialise datatables'. So the table as such does not change except for the fact that somehow it gets resorted by another column. Now that I think about it, may be it is receiving the returned data from the GET call, but somehow throws the error and resorts the data.

Here is the latest attempt:

By the way, "str" looks like in source format as per Firebug: hook%5B0%5D=115 . Being 115 the actual value that should be passed on to filter_range.php

[code]

var oTable ;
$(document).ready(function(){
$("#filter").change(function(){
var str = $("#filter").serialize();
oTable = $("#myTable").dataTable({
"bServerSide": true,
"bProcessing":true,
"fnServerParams": function (aoData) {
aoData.push({'str': $('#filter').serialize() });}
});
$.ajax({
url: "filter_prange.php",
type: "POST",
dataType:"json",
data: str,
success: function ReloadAjax(){
oTable.fnReloadAjax("filter_prange.php");
}
});
});
});

[/code]

allan

1. You cannot reinitialise the DataTable multiple tables - as the warning is indicating. Just initialise it once - i.e. not in the change event. I don't really understand why you'd want to do that?

2. I also don't really understand why fnReloadAjax is being calling inside another Ajax callback? That will make two calls to filter_prange.php . The first one, obviously won't be DataTables modified at all since you are making the call, not DataTables.

3. And finally, look at the documentation for fnServerParams - you need to use name value pairs (i.e. it must have a `name` and a `value` property in the object).

Allan

BernardA

Thanks Allan.
1 - I am not sure what you mean by reinitialise DT multiple tables (times, I guess ). The code I included is the one I can find in multiple examples of fnServerParams and fnReloadAjax in the forum.
It is within the change event, because that's what triggers the filter of #filter. I am not sure I saw any other way of calling fnReloadAjax other than oTable.fnReload Ajax(). The #filter code can be found on the very top of this post.

2 - You are absolutely right. It is redundant. I left it like that so that I could compare the returned data and also the parameters sent with both calls in Firebug. I took the Ajax call out now.

3 - Concerning fnServerParams, I tried 100's of different ways of the combined name/value property, including latest GregP suggestion. I also tried to place a hard number there, as shown in the code below. In all cases no parameters are being passed to filter_prange.php. Checked with Firebug.

IMPORTANT = Though I am still getting the error message = Cannot reinitialise DT, I have tested and verified that the data returned from filter_prange.php with the GET call from fnReloadAjax IS NOW PLACED WITHIN THE TABLE. This is of course not the correct data set, lacking the filtering, but still it is being taken in.

Latest code:

[code]

$(document).ready(function(){
$("#filter").change(function(){
var str = $("#filter").serialize(); //left here for now till finding a solution.
oTable = $("#myTable").dataTable({
"bServerSide": true,
"bProcessing":true,
"fnServerParams": function (aoData) {
aoData.push({name:'str', value : 107 });}
});
oTable.fnReloadAjax("filter_prange.php");
});
});

[/code]

BernardA

For whatever this is worth, I am showing the code placed at the section of the 'main' php file, where #myTable is.

[code]

@import "DataTables/media/css/demo_table.css";




<!-- js to initiate the dataTable-->

$(document).ready(function() {
$("#myTable").dataTable();
});

[/code]

allan

> 1 - I am not sure what you mean by reinitialise DT multiple tables (times, I guess ).

Oops - yes :-).

Okay question for you - what happens when you change the value of #filter ? The change function runs and tries to re-initialise the DataTable. Hence the problem. Just initialise it once - outside of the change callback.

Allan

BernardA

Allan

I am at a loss. I need to call fnServerParameters and fnReloadAjax on the change event, in order to send parameters and fetch new data to refresh the table.

How can it be done other then the way I did it?

allan

fnServerParam is called automatically when you call fnReloadAjax . You've passed DataTables a reference to a function, and it will call it for you when it needs to. You don't need to call that function yourself, just tell DataTables what the function is.

Allan

BernardA

Allan
That's quite cryptic, isn't it? I am not sure when I passed DT a reference to a function.
Up to this point all it seems I did was to call DT at document.ready and refer it to myTable.
You probably realized by now that I am no javascript/jquery expert, which apparently is required in order to be able to deal with this.
Thanks

allan

> That's quite cryptic, isn't it? I am not sure when I passed DT a reference to a function.

Not really. You've done:

[code]
fnServerParam: function ( aoData ) {
...
}
[/code]

i.e. you've assigned a function to the fnServerParam variable. Just like in the examples. You haven' called or otherwise executed that function.

> You probably realized by now that I am no javascript/jquery expert, which apparently is required in order to be able to deal with this.

In fairness, yes, I do assume that if you are going to be using a Javascript library that you have some knowledge of Javascript. There are much better sites than this one to teach you Javascript programming - indeed, that isn't a stated goal of this project. That's not meant to be a criticism at all - we all start somewhere and when we stop asking questions we stop learning! It just that learning Javascript is beyond the scope of the DataTables project, the stated goal of which is:

> To enhance the accessibility of data in HTML tables.

What you want is:

[code]
$(document).ready( {
// Initialise the table
oTable = $('#example').dataTable( {
// ...
} );

$("#filter").change(function(){
oTable.fnReloadAjax();
} );
} );
[/code]

Allan

BernardA

Thanks for your patience Allan. I know I've been a pain.
Yes this works. Well, almost.
There is a small hiccup I am trying to solve, but may be you did not intend it this way.
The value that is passed to $_GET is like below ( this is a var_dump($_GET) done at filter_prange.php):

This would normally be just 107 within the key 'hook' ( for a comparable $_POST example ). Apparently because there are 2 names. One coming from the form #filter serialization(hook[2]) and another given within fnServerParams.

Only solution to unravel this so far is regular expression (preg_match). I will make it work this way, but I was wondering if there was nothing within DT's set up to handle this more cleanly.

Thanks again!

[code] for var_dump($_GET)
array(2) {
["hook"]=>
string(15) "hook%5B2%5D=107"
["_"]=>
string(13) "1364929885549"

[/code]

[code]

$(document).ready(function() {
var str;
oTable = $("#myTable").dataTable({
"fnServerParams": function (aoData) {
aoData.push({name : 'hook', value : str });},
});

$("#filter").change(function(){
str = $("#filter").serialize();
oTable.fnReloadAjax("filter_prange.php");
});
});

[/code]

allan

Nothing to do with fnReloadAjax or DataTables that one - it is your call to $().serialize() that is doing that. Just use `str = $('#filter').val()` no?

Better yet would be to not use `str` at all, and just put the val call in fnServerParams. Otherwise the first time it is submitted it would be undefined, since you haven't got a default value.

Allan

BernardA

Allan

You are assuming there is value in #filter, but the values are in the checkboxes. Doing $('#filter').val() returns empty. As far as I know serialize() is the proper way to get the values out of a form and actually this works just fine with a regular Ajax call, which was the setup prior to DT.
One may need to a different script to get the values of the checked check-boxes within the framework of DT, but that would be a bit hacky.

[code]





[/code]