Server-side Auto Refresh Table

matthttam

Hello!
First, thanks for this awesome tool! I am using it to create a server-side generated table of student workorders, assignments, and computer information. I built this originally in javascript but after I hit 10,000 entries my page started loading A LOT slower.

I am virtually brand new to HTML, PHP, Javascript, mySQL, and AJAX. That being said I have learned so much over the past few months while building this solution.

I have several tabs using JQUERY UI Tabs. 3 of which have datatables in them. I'm looking for a way to simply auto refresh the tables every 10 seconds or so.

I found this post: http://datatables.net/forums/discussion/75/is-it-posible-to-refresh-table/p1
But I couldn't discern a usable answer out of it.

Here is the work orders initialization. I am using some methods I found in the forums to make text field searching and dropdown searching. I also have these search fields restoring on refresh of the page.

[code]
$(document).ready(function() {
var asInitVals = new Array();
var isReloadedFromCookies;
var oTable = $('#WO').dataTable({
"bJQueryUI": true,
"bProcessing": true,
"bServerSide": true,
"aaSorting": [[ 3, "asc"]],
"bStateSave": true,
"sAjaxSource": "dt/scripts/WO_processing.php",
"fnDrawCallback": function(oSettings) {
if (isReloadedFromCookies == true)
{
isReloadedFromCookies = false;
restoreFilters(oSettings);
}
return true;
},
"fnStateLoadCallback": function(oSettings) {
isReloadedFromCookies = true;
return true; // if we don't return true here, the reload is cancelled.
},"aoColumns": [
/* WOID */ null,
/* Status */ null,
/* Submitted */ null,
/* Completed */ null,
/* First Name */ null, //fnRender function gets passed oObj
/* Last Name */ null,
/* Laptop */ null,
/* Problem */ null,
/* Grad Yr */ null,
/* School */ null],


"fnServerData": function ( sSource, aoData, fnCallback ) {
/* ... additional variables ... */
$.getJSON( sSource, aoData, function (json) {
/* Create the select elements on the first run */
if ( json.sEcho == 1 )
{
/*var fieldDisplayValue = array("WO #","First Name","Last Name","Laptop");
var fieldDisplayName = array("WO","firstname","lastname","laptop");*/
$("tfoot th").each( function (i) {
/* Insert the select menu */
if ((i == 1) || /*(i == 2) || (i==3) ||*/ (i==7) || (i==8) || (i==9)){
this.innerHTML = fnCreateSelect(json.select[i], i);

$('select', this).change( function () {
oTable.fnFilter( $(this).val(), i );
} );
} /*else {
this.innerHTML = fnCreateInput(fieldDisplayName[i],fieldDisplayValue[i]);

}*/
} );
}

/* DataTables callback */
fnCallback(json)
} );
}

});

// Since the text fields are generated in the HTML I kept the column ID's straight by giving each text field an ID related to that column.
// This live funciton filters that column on key up.
$("#WO tfoot input").live("keyup", function () {
oTable.fnFilter( this.value, $(this).attr("name") );

//If it is a datepicker hide the date picker
$(this).datepicker("hide");
} );
// Setup each input field with class Date to use a date picker
$("#WO tfoot .Date").datepicker({
onSelect: function(dateText, inst){
oTable.fnFilter( this.value, $(this).attr("name") );
}
//altFormat: 'm/o/yy';
});


// This is where the custom filters at the bottom are restored to their correct values after a reload.
function restoreFilters(oSettings)
{
$('#WO tfoot input').each(function(index) {
if (oSettings.aoPreSearchCols[$(this).attr("name")].sSearch.length > 0) {
$(this).val(oSettings.aoPreSearchCols[$(this).attr("name")].sSearch);
$(this).removeClass('search_init');
//$(this).attr("id")
}
});
//alert(oSettings.aoPreSearchCols[3].sSearch);
$('#WO tfoot select').each(function(index) {
if (oSettings.aoPreSearchCols[$(this).attr("id")].sSearch.length > 0) {
$(this).find("option:contains('" + oSettings.aoPreSearchCols[$(this).attr("id")].sSearch + "')").prop("selected", "selected");
}
});

}
/*
* Support functions to provide a little bit of 'user friendlyness' to the textboxes in
* the footer
*/
$("#WO tfoot input").each( function (i) {
asInitVals[i] = this.value;
} );

$("#WO tfoot input").live("focus", function () {
if ( this.className == "search_init" )
{
this.className = "";
this.value = "";
}
} );

$("#WO tfoot input").live("blur", function (i) {
if ( this.value == "" )
{
this.className = "search_init";
this.value = asInitVals[$("#WO tfoot input").index(this)];
}
} );

} );
[/code]

Example code will help me the most as I am a visual learner when it comes to code.
Thanks for the help!
Bil

allan

To redraw a table which is using server-side processing, all you need to do is call fnDraw(). This will send a request out to the server for the current data and then display it. To do it every 10 seconds, just it that call into a setInterval(). Watch out with such a short time period though, as some browsers will leak memory over time.

Allan

matthttam

Thank you for the prompt reply. I will try to put this together tonight. Like I said I really am new to these languages. I will look up setInterval and try to use an example with fnDraw.

We are exclusively using firefox. I suppose I can bump it up to 30 seconds or even 1 minute. I'm hoping to make an overlay page that can be used to modify the workorder that is clicked on. Then once submitted it simply redraws the table but I haven't quite determined the best method for an overlay. Especially an overlay that doesn't have an X button on it that will simply close when a button is clicked.

Thanks again!
Bil

GregP

Most of the overlays out there which have the "x" are just binding that "x" to a function. You can usually call the same function from any other button click, but some libraries make it easier than others.

jQuery UI has pretty useful overlays, but I wouldn't use jQuery UI just for that. We're using it as the UI basis of our application so it made sense to use that portion of it.

--

Regarding polling, we are doing this with our application. Firefox is really bad for memory leaks (its garbage collector doesn't seem to be doing a great job) but 10 seconds is what we consider fairly generous to control those leaks.

The other thing we did is implement an 'away' timer that clears the polling interval if no user input is detected for X# milliseconds; at that point we show an overlay dialog warning the user that updates are paused, along with a button to allow them to continue. This way if the user does happen to just leave the page running, you can miminize the chances of a memory leak ruining your party.

Finally, there's a manual "pause" button to clear the timer. You will need to implement a pause function anyhow (for when someone is working inside your overlay) so you could just call the same function from a pause button.

matthttam

Great ideas! I'll be honest, this part of my task seems daunting since I am still learning ajax and jquery. I will need to sit down over the next few days and try to see what I can make work and what I can't.

Thanks again for these ideas. They make sense and I think I will eventually get a nice outcome.

I wish I could show you a link to the page but it will be password protected soon and it contains names of children so I am not allowed by law to expose this link to anyone :-P. I'll work on this tonight.

Bil

matthttam

Thanks again for your help! I now have my tables refreshing every 1 minute. Do you think 1 minute could cause a memory leak? The less I can put in this that can confuse the end user the better so a pop-up saying that its not auto refreshing isn't going to happen. I just need to make it solid enough that it most likely won't crash. Plus if it does have a memory leak and stop responding, wouldn't a restart of firefox fix it again?

Here is the code I used to get it to work in case anyone finds this looking for the same answers:
I disabled caching the server-side data by doing this:
[code]
"fnServerData": function ( sSource, aoData, fnCallback ) {
//Disable cache
$.ajax( {
dataType: 'json',
type: "GET",
cache: false,
url: sSource,
data: aoData,
success: fnCallback
} )
})
[/code]
Which I got from here:
http://www.datatables.net/forums/discussion/5886/trying-to-reload-data-at-setinterval-but-failing-miserably/p1

I then used the standing table redraw to prevent pagination from resetting. This is an API plugin

[code]
var newtimer = setInterval('wTable.fnStandingRedraw()', 60000);
[/code]
Which I got from here:
http://datatables.net/plug-ins/api#fnStandingRedraw


By the way my variable has changed from oTable to wTable for generally no reason; so ignore this.
I did have to get my variable to be global however. This is easy to do... just remove the var from a variable and it will be a global variable. I suppose this is why i changed the name.

Thanks again for the help. Now I get to look up overlay methods.

Bil

GregP

Awesome!

1 minute should be heaps of time. Keep your eye on it, but I really wouldn't worry much with that large of an interval.

Like Bil, I needed the timer variable to be "global", but I use object notation so make it quasi-global instead:

[code]
var myApp = myApp || {};

....
myApp.oTable = $('#theTable').dataTable({ ... });

myApp.polling = setInterval('myApp.oTable.fnDraw(false)', 5000);
[/code]

I have a lot more to it than that, simply because my application has some specialized needs (the pausing and resuming, the away/idle detection, each table needing a different interval, multiple tables on one page, etc) but this is the gist of it.

On a side note, thanks for mentioning the fnStandingRedraw() -- I think that's a better way for me to go with my application as well!

matthttam

Glad a solution to my problem could help others as well :-).

I have no idea what var myApp = myApp || {}; actually does. I'm guessing it makes an object or something but I don't recognize the || syntax.

Everything on my tables is working well. I made another post on making each table load on demand instead of on load. Maybe you could check it out.

Thanks,
Bil

GregP

Yes, it's just making an object. Chances are extremely high that var myApp = {} would work fine for you; frankly I don't understand how an "or" operator fits into this kind of shorthand either, I just took someone's word for it when they explained that the line translates to "Make a new object called myApp as long as there's not already a global variable called myApp."

Shorthand operators do my head in, but sometimes I just take people's words for it. ;-)

redips

[quote]allan said: To redraw a table which is using server-side processing, all you need to do is call fnDraw(). This will send a request out to the server for the current data and then display it.[/quote]

I post my question in another post, I don't want to do cross-posting but here maybe is the right place as the discussion is more focused:
How do I refresh data but resetting all filters on a table? I would attach this behavior to a button to "reload ALL data".
If I want to save actual filters and apply them by pressing another button (e.g. the label:"re-apply filters") ? How do I save the filters? How do I apply and remove them as wanted?

Thanks in advance.
Lorenzo

EDIT:
Found the solution using fnFilter or the plugin api fnFilterClear.
To be honest I didn't resolve with that, as i changed the search_xxx parammeters in the fnServerData, then introducing a js variable set to 1 (reload all) or 0 (filter) I set the serach criteria on the columns or not. I hoe my explanation is clear, if not I can send a PM with some code I used.

You can close the discussion. Thanks anyway and thanks for DataTables: a great piece of software!

etsm

Hi to all members!!!
Forgive me, my English is not good.
I'm a newbie in PHP, MySQL, Ajax and Datatables as well.
I have been asked to by my boss to design a Flight Information Display System (FIDS) which will show Departure and Arrival of airlines in a real time basis.
I have come across Datatables recently and I think it can serve the purpose.

I want the datatable to refresh after every 30minutes with the latest data from Mysql Datatabase.

I have started the project from code obtained from this url
http://www.sharemycode.com/item/view/95/jquery-datatables-plugin-example-with-php-mysql

Comment, tutorial or any helpful material will be aprreciated.

Here are my script.
<?php
$con = mysql_connect("localhost", "root", "");

if (!$con) {
die("Error: " . mysql_error());
}

mysql_select_db("FIDS", $con);

$result = mysql_query("SELECT * FROM arrival");
?>
<!DOCTYPE html>


DataTables






@import "media/css/demo_table_jui.css";
@import "media/themes/smoothness/jquery-ui-1.8.4.custom.css";



*{
font-family: arial;
}


var myApp = myApp || {};
$(document).ready(function(){
myApp.oTable = $('#datatables').dataTable({
"sPaginationType":"full_numbers",
"aaSorting":[[2, "desc"]],
"bJQueryUI":true,
"fnServerData": function ( sSource, aoData, fnCallback ) {
//Disable cache
$.ajax( {
dataType: 'json',
type: "GET",
cache: false,
url: sSource,
data: aoData,
success: fnCallback
} )
}
});
});
myApp.polling = setInterval('myApp.oTable.fnDraw(false)', 5000);










Airline
Flightno
Scheduled Time
Origin City
Status
Logo



<?php
while ($row = mysql_fetch_array($result)) {
?>

<?=$row['airline']?>
<?=$row['flightno']?>
<?=$row['sched']?>
<?=$row['origin']?>
<?=$row['status']?>
<?=""?>

<?php
}
?>