Problems with AJAX Pagination and PHP

hertensteinr

hi All,

I am having some trouble with getting the pagination to work for this tool. I was able to get through the excellent documentation and get the sorting, and layout and such working well. The only thing that isn't working for me is the pagination. When I display the GET vars, it is showing that the starting variable is set to zero each time. Unfortunately, with this being the case, you can't really paginate :) Here is the code. I am using a database class to connect and manage all fo the database stuff, so only the SQL is needed there. Thanks in advance

From the Javascript:

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

[/code]


From the PHP

[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]

allan

This looks like a duplicate of http://datatables.net/forums/comments.php?DiscussionID=83&page=1#Item_13 . An answer to the issue is provided in that thread.