HOW TO Pagination with Ajax

anuelicon

Hi, compliments for this Plugin ... I have started to test it yesterday and today I decided to use in my production server !! :)

Only one question, I'm trying to implement an ajax pagination, my result table is the result of mysql query, but the query has LIMIT 0,10 on page 1 ...
If I run that query and post the result with DataTable, no arrows link will are shown, and the total is 10!

Can I pass to Datatable only the total numrows for draw the pagination links and wirte the total correctly (Showing 1 to 10 of 2334 entries)??
When the user click on new pag (es > or 2) an ajax call sets the new LIMIT for the same query.

With this method, every call generate one light query with only 10 (or little more) results.

Any suggestion ?

Thanks,

allan

Hi anuelicon,

Yes indeed this can be done. The server-side processing isn't fully document yet since it is in a beta state, but basically DataTables expects the following data to be returned:

iTotalRecords - Total records, after filtering (not just the records on this page, all of them)
iTotalDisplayRecords - Total records, before filtering
aaData - The data in a 2D array

See this post for a few more details: http://datatables.net/forums/comments.php?DiscussionID=53&page=1#Item_1

You can also see an example of how this works, including the server-side processing script here: http://datatables.net/1.5-beta/examples/data_sources/server_side.html

Hope this helps,
Allan

korsakov

Hi Allan, I have 2 questions for you:
1st question:
I am interested in DataTables server-side processing. How can I change
"sAjaxSource": "../examples_support/server_processing.php" on the fly since my PHP receives changing values in order to put out data?
I haven't checked out a long table but could dataTables display data while it keeps receiving data from the server?

2nd question:
I would like to read a table directly with the javascript code as to be seen in one of the examples. How do I mark up items with links. Can HTML be included to the tabel items? Any suggestion?

Thank you!!

allan

Hi korsakov,

1. You can change the address from which DataTables gets it's data from by changing sAjaxSource in the table's settings object. For example:

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

oTable.fnSettings().sAjaxSource = "/json2.php";
} )
[/code]

2. Yes indeed you can include html in the cells. This is the best way to put a link in if you are just looking for a plain simple link :-) - see this example: http://datatables.net/examples/example_html_sort.html

Allan

korsakov

Thank you, Allan! Your support is really great. With regards to question 1: Do I need a variable for the "sAjaxSource" field since the PHP-URL's are constantly changing?

allan

Hi korsakov,

I'm not quite sure what you mean. You can use a variable if you want, I've just used an absolute string in my example. Perhaps the easiest for you would be to use a function like this:

[code]
function fnChangeSource( sSource )
{
oTable.fnSettings().sAjaxSource = sSource;
}

/* Example call */
var test = "json3.txt";
fnChangeSource( sSource );
[/code]

Then on the next table draw the table will update (note it depends on a global variable at the moment...). If you need to force a redraw, then just call oTable.fnDraw().

Regards,
Allan

korsakov

Thank you Allen!
Imagine this to be the link in my HTML:
PHP-Link
---------------------------------------------------------
And this is the javascript in the header:

var aTable = $('#table-test').dataTable( {
"aoColumns": [
{ "sType": "html" },{ "sType": "html" },{ "sType": "html" },null,null
],
               "bProcessing": true,
               "bServerSide": true,
               "sAjaxSource": "test"
       } );

function fnChangeSource( sSource )
{
       aTable.fnSettings().sAjaxSource = sSource;
}

var test = $("#table-test").click(function() {
fnChangeSource( sSource );
});
--------------------------------------------------------------------
I am sorry to take your time but at this time the table shows up without dataTables. Do you see a mistake in the javascript? It woud be great if i could make this work! Thank you!!

korsakov

Correction of former code:
var aTable = $('#table-test': --!> wrong! must be the id of table of course! <-- ).dataTable( {

Problem remains unfortunately..

allan

Hi korsakov,

Your initialisation looks good (with the exception of the id which you pointed out) - are you wrapping the $().dataTable() call in $(document).ready( function () { ... } ); ? If not this is problem the issue. If you are... could you provide a link to the page with the problem?

Thanks,
Allan

korsakov

Hello Allan, Thanks for your patience! I used $(document).ready( function () although I didn't include it in my recent message. Could you please email me directly because of secrecy issues? Thank you!

korsakov

By the way the rest of my javascript breaks with: "bProcessing"

Error message:

invalid property id
[Break on this error] "bProcessing": true,\n

Could this be an indicator for a problem?

allan

Hi korsakov,

Yes a Javascript break would probably not be good here. Although I can't see anything wrong with your initialisation, so I don't see why Javascript would be complaining about that. You can contact me directly here: http://www.datatables.net/contact - it would be great if you could indeed send me an example link (even if it's a cut down version).

Allan

hertensteinr

hi All,

I am having some problems with pagination in my Ajax code. I can get the search and sort part to work, but for some reason, the pagination is not working. One thing that I noticed is that the start position is not updating.

here is the JavaScript code
[code]
$('#eventSearchResultsTable').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "/siteAdmin/calendar/eventAJAXResults.php"
});

[/code]

Here is the PHP Code
[code]
<?

include($_SERVER['DOCUMENT_ROOT'] . "/customApps/includes/authentication/loginClass.php");

$db = new DB_Sql();

// Connect all of the GET variables to their values
foreach($_GET as $k => $v) {
$$k = $v;
} // end of foreach($_GET as $k => $v) {

/***********************************************
* This function translates the column number from the table to
* the field name of the dateabase
**********************************************/
function fnColumnToField( $i ) {
switch ($i) {
case 0:
return "evntTitle";
break;
case 1:
return "evntStartDate";
break;
case 2:
return "evntStartTime";
break;
case 3:
return "catMajorName";
break;
default:
return "evntTitle";
break;
}
} // end of function fnColumnToField( $i )


$query = "SELECT
e.evntID, e.evntTitle, DATE_FORMAT(e.evntStartDate, '%m-%d-%Y'), e.evntStartTime, c.catMajorName, c.catMinorName
FROM
cal_event AS e
INNER JOIN cal_eventCategory AS ec ON e.evntID = ec.evcaEventID
INNER JOIN cal_category AS c ON c.catID = ec.evcaCategoryID
WHERE
e.evntTitle LIKE '%$sSearch%'
OR (e.evntStartDate LIKE '%$sSearch%')
OR (e.evntStartDate LIKE '%$sSearch%')
OR (c.catMajorName LIKE '%$sSearch%')
OR (c.catMinorName LIKE '%$sSearch%')
";

$db->query($query);
$totalRows = $db->num_rows();

if ($iSortCol_0 != "") {
for ( $i=0 ; $i < $iSortingCols; $i++ ) {
$query .= " ORDER BY " . fnColumnToField($_GET['iSortCol_'.$i]) . " " .$_GET['sSortDir_'.$i] .", ";
}

// knock off the extra two characters at the end of the query
$query = substr($query, 0, strlen($query) - 2);
}

// Let's make sure we don't have bad data coming in. Let's protect the SQL
if ($iDisplayStart == "") {
$iDisplayStart = 0;
}

// Once again, protecting the SQL, as well as making sure we don't go over the limit
if ($iDisplayLength == "") {
$iDisplayLength = $totalRows - $iDisplayStart;
}

$query .= " LIMIT $iDisplayStart, $iDisplayLength ";


$db->query($query);
$numRows = $db->num_rows();

$sOutput = '{';
$sOutput .= '"sEcho": '.intval($_GET['sEcho']).', ';
$sOutput .= '"iTotalRecords": '.$totalRows.', ';
$sOutput .= '"iTotalDisplayRecords": '.$numRows.', ';
$sOutput .= '"aaData": [ ';

for ($i = 0; $i < $numRows; $i++) {
$db->next_record();
$evntID = $db->Record[0];
$evntTitle = $db->Record[1];
$evntStartDate = $db->Record[2];
$evntStartTime = $db->Record[3];
$catMajorName = $db->Record[4];
$catMinorName = $db->Record[5];

$sOutput .= "[";
// Column 1 -- Title/Name
$sOutput .= '"'.addslashes($evntTitle).'",';
// Column 2 -- Event Date
$sOutput .= '"'.addslashes($evntStartDate).'",';
// Column 3 -- Event Time
$sOutput .= '"'.addslashes($evntStartTime).'",';
// Column 4 -- Category
$sOutput .= '"'.addslashes($catMajorName).' '.addslashes($catMinorName).'",';
// Column 5 -- View
$sOutput .= '"",';
// Column 6 -- Edit
$sOutput .= '"",';
// Column 7 -- Delete
$sOutput .= '""';
$sOutput .= "],";
}

$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= '] }';

echo $sOutput;

?>
[/code]

Any help with this would be appreciated.

Thanks

allan

The problem is that you only have one query - so the iTotalRecords and iTotalDisplayRecords are likely to be incorrect. I'd suggest using something like the script shown here: http://datatables.net/examples/data_sources/server_side.html . It's got three queries I know, but it's fairly optimised :-)

Allan

hertensteinr

Actually, I have two queries that are run -- one unfiltered (which returns 13), and then the filtered [Line 51, 52], which returns 10 [Line 76, 77]. Here is the screenshot of what I am seeing

http://screencast.com/t/MDg4YmViMjk

I still need to stylize the table, but that's a minor thing at this time :)

Thanks for the help

allan

Sorry - I missed that. However, still a long the same lines, you have a limit on the query for the data, but no way to get the full data set count (I think...).

$totalRows should be the total number of rows in the dataset without filtering and with out paging
$numRows should be the total number of rows in the dataset with filtering and with out paging

There is no need to tell DataTables that there are 10 records in the array - it can see that. So those two numbers tell it what it can't see. Does that help?

Allan

hertensteinr

ok... I see what you are saying now. I have made the changes so that I run the query 3 times

First time --> Total Records
Second Time --> Filtered Records
Third Time, --> Pagination

That seems to work (with a couple of quirky things, but that's something I can work out).

Thanks for the help, it's much appreciated.