Return an Empty Set
Return an Empty Set
Hello,
First off thank you for the great jQuery plugin. This has been very helpful for me on many projects.
The question I have is probably very simple, but I do not understand how to correct it. Here is the scenario.
I have a table that I first populate via the code below:
[code]
var oTable;
jQuery.ajax({
type: "GET",
url: "inc-tickets-ajax.php",
data: "techID=" + jQuery("#techID").val() + "&clientID=" + jQuery("#clientID").val() + "&status=" + jQuery("#status").val() + "&beg_date=" + jQuery("#beg_date").val() + "&end_date=" + jQuery("#end_date").val(),
dataType: "json",
success: function(html) {
oTable = jQuery('#ticketstable').dataTable({
bProcessing: true,
"aaData": html
});
}
});
[/code]
That works without any issues. Next, I have some options that a user can choose to further refine the results (filter). They can choose from a variety of drop downs, and eventually click a link. The code that handles the onClick is below.
[code]
jQuery("#ajaxCall").click(function(){
jQuery.ajax({
type: "GET",
url: "inc-tickets-ajax.php",
data: "techID=" + jQuery("#techID").val() + "&clientID=" + jQuery("#clientID").val() + "&status=" + jQuery("#status").val() + "&beg_date=" + jQuery("#beg_date").val() + "&end_date=" + jQuery("#end_date").val(),
dataType: "json",
success: function(html) {
alert(html);
oTable.fnClearTable();
oTable.fnAddData(html);
}
});
});
[/code]
The problem is that this works without any issues when results are returned from the server, but when an empty set is returned, I get a warning stating that the added data does not match the known number of columns. Once you click ok, the table loads fine, but I am looking for a way to stop the message from appearing. I looked through the forum, but did not really find anything that could help.
Just in case, here is the snippet of php code:
[code]
$json_array = "[ ";
if(mysql_num_rows($tickets_query) > 0){
while($row = mysql_fetch_array($tickets_query)) {
$json_array .= "[\"" . $row['ticket_id'] . "\", \"" . date_long($row['ticket_date'], "n/j/Y g:i a") . "\", \"" . $row['submitted_name'] . "\", \"" . substr($row['description'], 0, 350) . "\", \"" . $row['status_title'] . "\", \"" . $row['invoice'] . "\"], ";
}
$json_array = substr_replace($json_array, "", -2 );
$json_array .= ']';
}
echo $json_array;
[/code]
If no results are found, I return a [].
It may be possible that I am trying to acheive this in a totally wrong way. Any guidence would be greatly appreciated.
Thanks in advance for any help provided.
-David
First off thank you for the great jQuery plugin. This has been very helpful for me on many projects.
The question I have is probably very simple, but I do not understand how to correct it. Here is the scenario.
I have a table that I first populate via the code below:
[code]
var oTable;
jQuery.ajax({
type: "GET",
url: "inc-tickets-ajax.php",
data: "techID=" + jQuery("#techID").val() + "&clientID=" + jQuery("#clientID").val() + "&status=" + jQuery("#status").val() + "&beg_date=" + jQuery("#beg_date").val() + "&end_date=" + jQuery("#end_date").val(),
dataType: "json",
success: function(html) {
oTable = jQuery('#ticketstable').dataTable({
bProcessing: true,
"aaData": html
});
}
});
[/code]
That works without any issues. Next, I have some options that a user can choose to further refine the results (filter). They can choose from a variety of drop downs, and eventually click a link. The code that handles the onClick is below.
[code]
jQuery("#ajaxCall").click(function(){
jQuery.ajax({
type: "GET",
url: "inc-tickets-ajax.php",
data: "techID=" + jQuery("#techID").val() + "&clientID=" + jQuery("#clientID").val() + "&status=" + jQuery("#status").val() + "&beg_date=" + jQuery("#beg_date").val() + "&end_date=" + jQuery("#end_date").val(),
dataType: "json",
success: function(html) {
alert(html);
oTable.fnClearTable();
oTable.fnAddData(html);
}
});
});
[/code]
The problem is that this works without any issues when results are returned from the server, but when an empty set is returned, I get a warning stating that the added data does not match the known number of columns. Once you click ok, the table loads fine, but I am looking for a way to stop the message from appearing. I looked through the forum, but did not really find anything that could help.
Just in case, here is the snippet of php code:
[code]
$json_array = "[ ";
if(mysql_num_rows($tickets_query) > 0){
while($row = mysql_fetch_array($tickets_query)) {
$json_array .= "[\"" . $row['ticket_id'] . "\", \"" . date_long($row['ticket_date'], "n/j/Y g:i a") . "\", \"" . $row['submitted_name'] . "\", \"" . substr($row['description'], 0, 350) . "\", \"" . $row['status_title'] . "\", \"" . $row['invoice'] . "\"], ";
}
$json_array = substr_replace($json_array, "", -2 );
$json_array .= ']';
}
echo $json_array;
[/code]
If no results are found, I return a [].
It may be possible that I am trying to acheive this in a totally wrong way. Any guidence would be greatly appreciated.
Thanks in advance for any help provided.
-David
This discussion has been closed.
Replies
Interesting - I hadn't really considered passing an empty array to fnAddData. As a little bit of background, fnAddData is overloaded (or at least as much as a function can be in Javascript), so it accepts either a 1D or 2D array. You are passing a 1D array when your result set is empty, and DataTables is trying to add that as a new row - finding that there are no elements to the array and complaining about it...
The easy work around is to check if you have an empty array before calling fnAddData:
[code]
jQuery("#ajaxCall").click(function(){
jQuery.ajax({
type: "GET",
url: "inc-tickets-ajax.php",
data: "techID=" + jQuery("#techID").val() + "&clientID=" + jQuery("#clientID").val() + "&status=" + jQuery("#status").val() + "&beg_date=" + jQuery("#beg_date").val() + "&end_date=" + jQuery("#end_date").val(),
dataType: "json",
success: function(html) {
alert(html);
oTable.fnClearTable();
if ( html.length != 0 )
{
oTable.fnAddData(html);
}
}
});
});
[/code]
I'm not entirely sure what DataTables should do with this data set. It can either:
1. Do as it is doing - which is a bit rubbish
2. Do a check like that above, but then it might have been returning an empty result set in error, and you'd want to know this
3. Use a different alert to say that there is an empty result set
1 and 3 would require the developer to do something like the if() in the above code, while 2 wouldn't... I think I prefer option 2, and I think this does need to change. Sound reasonable?
Regards,
Allan
Thank you for the advice. Sometimes a simple solution is the best.
All is working now.
Once again, thank you for the wonderful plugin. I greatly appreciate all of the work and time you put into developing and supporting DataTables.
Once again, thank you!
-David
So I have a question that "aaData" is an option in dataTable?
By the way , I use servlet as my server-side,I reutrn the string that coded as json type to client.
PS: I just find my fault, the aaData is formated as [[aaa],[bbb]]. But I reurn the data to success option is as "{aaData[[aaa],[bbb]]}". So it's my mistake!