automatic pagination problem
I have a working database and query but now I am trying to put an pagination system on the search results.
PHP Code:
$numrows=25;
$rowsperpage=5;
$totalpages = ceil($numrows / $rowsperpage);
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage']))
{
$currentpage = (int) $_GET['currentpage'];
}
else
{
$currentpage = 1;
}
if ($currentpage > $totalpages)
{
$currentpage = $totalpages;
}
if ($currentpage < 1)
{
$currentpage = 1;
}
$state=($_POST['state']);
$state2=($_POST['state2']);
$state3=($_POST['state3']);
$ANYSTATE=($_POST['AnyState']);
$NCAAtype=($_POST['NCAAtype']);
$IHSAtype=($_POST['IHSAtype']);
$OTHERtype=($_POST['OTHERtype']);
$westernDisicipline=($_POST['westernDisicipline']);
$englishDisicipline=($_POST['englishDisicipline']);
$fencesDisicipline=($_POST['fencesDisicipline']);
$stateQ = "SELECT DISTINCT * FROM Schools WHERE State= '$state' OR State= '$state2' OR State= '$state3'";
if(empty($westernDisicipline))
{
if(empty($englishDisicipline))
{
if(empty($fencesDisicipline))
{
$westernDisicipline = "YES";
$englishDisicipline = "YES";
$fencesDisicipline = "YES";
}
}
}
if(empty($NCAAtype))
{
if(empty($IHSAtype))
{
if(empty($OTHERtype))
{
$NCAAtype = "YES";
$IHSAtype = "YES";
$OTHERtype = "YES";
}
}
}
if(!empty($ANYSTATE))
{
$state = "Please Choose";
$stateQ = "SELECT DISTINCT * FROM Schools WHERE State <> '$state'";
}
$offset = ($currentpage - 1) * $rowsperpage;
$results= mysql_query("SELECT DISTINCT Schools1.* FROM
(SELECT DISTINCT *FROM Schools WHERE Western= '$westernDisicipline' OR English= '$englishDisicipline' OR Fences='$fencesDisicipline') Schools1
INNER JOIN
(SELECT DISTINCT * FROM Schools WHERE NCAA= '$NCAAtype' OR IHSA='$IHSAtype' OR OTHER= '$OTHERtype') Schools2
INNER JOIN
($stateQ) Schools3
ON
Schools1.Western = Schools2.Western and Schools1.Western = Schools3.Western and Schools2.Western = Schools3.Western
and
Schools1.English = Schools2.English and Schools1.English = Schools3.English and Schools2.English = Schools3.English
and
Schools1.Fences = Schools2.Fences and Schools1.Fences = Schools3.Fences and Schools2.Fences = Schools3.Fences
and
Schools1.IHSA = Schools2.IHSA and Schools1.IHSA = Schools3.IHSA and Schools2.IHSA = Schools3.IHSA
and
Schools1.State = Schools2.State and Schools1.State = Schools3.State and Schools2.State = Schools3.State
and
Schools1.NCAA = Schools2.NCAA and Schools1.NCAA = Schools3.NCAA and Schools2.NCAA = Schools3.NCAA
and
Schools1.OTHER = Schools2.OTHER and Schools1.OTHER = Schools3.OTHER and Schools2.OTHER = Schools3.OTHER LIMIT $rowsperpage OFFSET $offset");
if(mysql_num_rows($results)>0)
{
echo '<table>';
echo '<th>School Name</th>';
echo '<th align=left>State</th>';
while($array = mysql_fetch_assoc($results))
{
echo '<tr>';
echo '<td>';
print $array['School Name'];
$Name=$array['School Name'];
echo " ";
echo '</td>';
echo '<td>';
print $array['State'];
echo " ";
echo '</td>';
echo '<td>';
echo "<a href='Display.php?Name=$Name'>View Profile</a>";
echo '</td>';
echo'</tr>';
}
echo '</table>';
}
else
{
//header('Location: CollegeEquestrianNoResults.htm');
}
$range = 3;
if ($currentpage > 1)
{
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
$prevpage = $currentpage - 1;
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
}
for ($x = (($currentpage - $range) - 1); $x < (($currentpage + $range) + 1); $x++)
{
if (($x > 0) && ($x <= $totalpages))
{
if ($x == $currentpage)
{
echo " [<b>$x</b>] ";
}
else
{
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
}
}
}
if ($currentpage != $totalpages)
{
$nextpage = $currentpage + 1;
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
}
echo $offset;
echo $rowsperpage;
mysql_close();
I think the $offset and $rowsperpage are getting the correct values. It gets the correct # of links but when you click off the first page no results are shown and when you go back only 2 different results are shown. I don't think I am getting the LIMIT clause in the query right, I don't know how to do it with all the INNER JOINS.
