Problems with AJAX Pagination and PHP
Problems with AJAX Pagination and PHP
![hertensteinr](https://secure.gravatar.com/avatar/5d630e3494d73104186fd4ae65739351/?default=https%3A%2F%2Fvanillicon.com%2F5d630e3494d73104186fd4ae65739351_200.png&rating=g&size=120)
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]
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]
This discussion has been closed.
Replies