Form to get data, datatables to display it?

sbirnie

I'm having a tough time trying to explain what I'm looking for, so please bear with me.

Here's an example - say I have a million records of data. I don't want all that data going to the client's browser. So I create a page with a form that let's them filter which ones they want to see. Let's say that gets down to 100 records and I have DataTables process those results - the sorting, pagination and filtering.

I'd like to have the form and the DataTable on the same page, so that if the user wants to change the form values and hit's submit, an ajax call is made that could return a different set of records.

What is the best way to do that?

I see that DataTables can make server-side or ajax calls, but I don't want it to do that for filtering, paging, or sorting - it's good at that. I just want it to do what it does best on a subset of data returned from the form search.

I also see that you can use javascript functions to add and delete rows, but I'm not sure if deleting all the rows and repopulating the table when the ajax returns is the best idea either.

Any ideas or suggestions? Thanks.

fbas

You can use AJAX-sourced data (the data is grabbed by AJAX, but sorting will be done on client side) with a data file that takes parameters and dynamically creates the JSON in the file. This would be sort of a AJAX-source / Server Side hybrid.

What you will do is when the user searches with your form is destroy the old table, and call a new AJAX source with parameters added.

Your AJAX source should be dynamically created with the parameters and should make sure not to cache the file

Here is an example:
[code]
var sAjaxSource = "sources/arrays.php";

// add a function to the DataTables API for convenience.
$.fn.dataTableExt.oApi.fnRedraw = function(oSettings) {
var src = sAjaxSource + "?arg="+Math.floor(Math.random()*10); // <-- instead of a random number, include search terms from your form

// destroy current table and redraw with new AJAX
oTable = $('#example').dataTable( {
"bProcessing": true,
"sAjaxSource": src, // <-- rerun AJAX call for new data
"bDestroy": true // <-- destroy the old table so a new one is created
} );

};


var oTable;
$(document).ready(function() {
oTable = $('#example').dataTable( {
"bProcessing": true,
"sAjaxSource": sAjaxSource
} );
} );
[/code]

And attach the oTable.fnRedraw() call to some handler in your search form.

Your server side AJAX creating script should obviously be more sophisticated than this, but as an example here it is:
[code]
<?php
header("Cache-Control: no-cache, must-revalidate");

// a real script would check parameters to query the database with. this just returns static JSON data
?>{ "aaData": [
["Trident","Internet Explorer 4.0","Win 95+","4","X"],
["Trident","Internet Explorer 5.0","Win 95+","5","C"],
["Trident","Internet Explorer 5.5","Win 95+","5.5","A"]
] }
[/code]