Results aren't paginated

[mndwn]

Hello, I have put in a pagination code to put 20 results per page, however it doesn't work because the results are still put into a long table. There is no error, and for some reason the Next and Previous links are displayed before the search form even has been submitted. Here is the code:

<?php
print "<table border='0' bgcolor='#CCCCCC' align='center'><tr><td>
			 <form method='get'> 
             <p>By Hub:
             <select name='search_hub' id='search_hub'>
             <option>SELECT</option>
             <option value='ypad'>Adeliade</option>
             <option value='ybbn'>Brisbane</option>
             <option value='ypdn'>Darwin</option>
             <option value='ymml'>Melbourne</option>
             <option value='ypph'>Perth</option>
             <option value='yssy'>Sydney</option>
             <option>---------------------</option>
             <option value='nzaa'>Auckland</option>
             <option value='nzch'>Christchurch</option>
             <option>---------------------</option>
             <option value='egll'>London</option>
             <option value='klax'>Los Angeles</option>
             <option value='wsss'>Singapore</option>
             <option value='rjaa'>Tokoyo</option>
             </select>
             <font color='#CCCCCC'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</font> 
             <input name='searchr' type='submit' value='Search'>
             </form></td></tr></table>"; // Form which user inputs search preference
			 $rowcolour = $colour1; // The beginning row colour
			 $page = $_GET['page']; // since register globals is off with 4.2+ this is the value of the $page varible
			 if(empty($page)){ $page = 1; } //
			 $limitvalue = $page * $limit - ($limit); // $limit value is stated on db.php 
			 $query = "SELECT *
                       FROM (routes INNER JOIN depart 
                       ON routes.departid = depart.departid) 
                       INNER JOIN arrive ON routes.arriveid = arrive.arriveid
					   LIMIT $limitvalue, $limit"; // selects all data from the tables
			 $queryresult = mysql_query($query) or die ("<p>Error: ".mysql_error()); 
			 if (mysql_num_rows($queryresult) == 0) { echo ("<p>Sorry, No result was found from your search"); } // reads how many rows are returned
			 if ($_GET["search_hub"]) { // parses this code if the search hub varible has been submitted in the form
                  $sql = "SELECT routes.routeid, depart.depart, depart.departcode, arrive.arrive, arrive.arrivecode 
                  FROM (routes INNER JOIN depart 
                  ON routes.departid = depart.departid) 
                  INNER JOIN arrive ON routes.arriveid = arrive.arriveid 
                  WHERE depart.departcode = '".$_GET['search_hub']."'"; // The search query with the varible which the user enters on the form
                  $result = mysql_query($sql, $db); // The value of the $result array
                  print "<table width='75%' border=0 align='center'>"; // The beginning of the table with the column headers
                  print "<tr><th><p><b>Route Code</b></th><th><p><b>Departure Airport</b></th><th><p><b>Code</b></th><th><p><b>Arrival Airport</b></th><th><p><b>Code</b></th></tr>"; 
               while($row = mysql_fetch_array($result)) { // This is how the results will be displayed on the page
				  print "<tr bgcolor='$rowcolour'><td align='center'><p>AR".$row['routeid']."</td>"; 
                  print "<td align='center'><p>".$row['depart']."</td>"; 
                  print "<td align='center'><p>".$row['departcode']."</td>"; 
                  print "<td align='center'><p>".$row['arrive']."</td>"; 
                  print "<td align='center'><p>".$row['arrivecode']."</td></tr>";
                  $rowcolour = ($rowcount%2)?$colour1:$colour2; // The code to have alternate coloured rows
                  $rowcount++; }
               print "</table>"; } 
			   print "<table border=0, align='center'><tr>";
			  if ($page != 1) { 
			     $pageprev = $page -1; // subtracts 1 from $page
				 echo("<td><p><a href='{$_SERVER['PHP_SELF']}?page=$pageprev'><p>Previous".$limit."</a>&nbsp;"); } //could either use the filename or $PHP_SELF
			  else echo("<td><p>Previous&nbsp;".$limit."&nbsp;"); // If we are on page one it won't be a link
			  $numofpages = $totalrows / $limit; // Division of toal rows by the limit per page
			  for($i = 1; $i <= $numofpages; $i++) { // For loop will add 1 to $i at he end until $i is greater then $numofpages
			     if ($i == $page) {echo("<td>".$i."&nbsp;</td>"); } // the current page number
				 else { echo("<td><p><a href='{$_SERVER['PHP_SELF']}?page=$i'><p>$i</a>&nbsp;"); } } // This ends the for loop
			  if(($totalrows % $limit) != 0) { // Finds the remainder so the amount of rows can be found to be shown on the last page
			     if ($i == $page) { echo("<td>".$i."&nbsp;</td>"); } 
				 else { echo ("<td><p><a href='{$_SERVER['PHP_SELF']}?page=$i'>$i</a>&nbsp;"); } } // Turns it into a link
			  if(($totalrows - ($limit * $page)) >= 0) { $pagenext = $page +1; // adding 1 to $page
			      echo("<td><p><a href='{$_SERVER['PHP_SELF']}?page=$pagenext'><p>Next".$limit."</a></td>"); }
			  else { echo("<td><p>Next&nbsp;".$limit."</td>"); } // if on last page, next will not be shown
			  print "</tr></table>"; //closes table which the Next and Previous page links are in
?>

Thanx for the help in Advance

[osusano]

i have a post where in u can grab the code and test it with you

http://www.phpbuilder.com/board/showthread.php?threadid=10242489&perpage=15&highlight=pagination&pagenumber=6

[osusano]

and still i already made a class for this so that u dont need to cut & paste it in every page with the same functions

here it goes.

filename: testQuery.php

<?
	include ("dbConnect.php");

if ($rstart == ''){$rstart=0;}
if ($pgptr == ''){$pgptr=1;}
$rowsPerPage = 10;
if($rstart > 10){ $toValue = $rstart + $rowsPerPage; }

include ("queryList.php");
$sql = "select * from users";
$makeQuery = new queryList( $sql, $rstart, $pgptr, $rowsPerPage, 10, 'testQuery.php' );
$res = mysql_query ($makeQuery->sqlQry);

# list elements in table with changing background on each row
?>
	<table cellpadding=0 cellspacing=0 width=600>
	<tr bgcolor=#808080 height=25>
		<td width=100>&nbsp;<b>Country Code</b></td>
		<td width=500><b>Country</b></td>
	</tr>
<?
  	while ( list ( $code, $country ) = mysql_fetch_row($res) ){
		if($bgcolor=='#C9C9C9'){ $bgcolor='#DFDFDF'; } else { $bgcolor='#C9C9C9'; }
		echo "<tr bgcolor=$bgcolor height=25>";
		echo "<td>&nbsp;$code</td>";
		echo "<td>$country</td>";
		echo "</tr>";
		if($rstart <= 10){ $toValue++; }
		if ($makeQuery->totalRows >= 1){ $message = "Displaying records <font color=red>$makeQuery->fRow</font> - <font color=red>$toValue</font> of <font color=red>$makeQuery->totalRows</font>."; }
	}
?>
	</table>
<?
	echo "<br>".$message;
	if($makeQuery->totalPages > 1){
		echo "<br>Pages ( $makeQuery->totalPages ): ".$makeQuery->pages;
	}
?>

filename: queryList.php

<?
	/**
	** Author: Oliver Susano ([e-mail])
	** Class queryList
	** Query with paginations and previous next links.
	** syntax: queryList($sql, $rstart, $pgptr, $rowsPerPage, $pageLimit, $link)
	**/
	class queryList {
		function queryList($sql, $rstart, $pgptr, $rowsPerPage, $pageLimit, $link) {
			$result = mysql_query( $sql );
			$totalRows = mysql_num_rows($result);
			$this->totalRows = $totalRows;
			$totalPages = intval($totalRows / $rowsPerPage);
			$excess = $totalRows % $rowsPerPage;
			if ($excess > 0){ $totalPages = $totalPages + 1; }

		$trstart = $rstart;
		$pages = "";
		if($totalPages > $pageLimit ) { $value = $pageLimit; } else {$value = $totalPages;}
		if($pgptr > $pageLimit){ $i = $pgptr - $pageLimit; $value = $pageLimit+$i;}

		// section for Previous Record
		if($pgptr > 1){
			$ppgptr = $pgptr - 1;
			$prstart = $rstart - $rowsPerPage;
			$pages = $pages."&nbsp;<b>[ <a href='".$link."?pgptr=$ppgptr&rstart=$prstart'>prev</a> ] &nbsp;&nbsp;</b>";
		}
		while ($i < $value){
			$trstart = $rowsPerPage * $i;
			$i++;
			if ($i == $pgptr){
				$pages = $pages."<font size=2><b>$i</b></font> ";
			} else { if($i <= $totalPages){ $pages = $pages."<a href='".$link."?pgptr=$i&rstart=$trstart'>$i</a> ";} }
		}
		// section for Next Record
		if($i <= $totalPages){
			if($totalPages != $pgptr){
				$nrstart = $rstart+$rowsPerPage;
				$npgptr = $pgptr + 1;
				$pages = $pages."&nbsp;<b>[ <a href='".$link."?pgptr=$npgptr&rstart=$nrstart'>next</a> ]</b>";
			}
		}
		$fRow = (($pgptr-1) * $rowsPerPage) + 1;
		$i = $fRow - 1;

		$this->pages = $pages;
		$this->fRow = $fRow;
		$this->totalPages = $totalPages;
		$this->sqlQry = $sql." LIMIT ".$rstart.",".$rowsPerPage;
	} // end of query()

} // end of Class
?>

filename: dbConnect.php

<?
	require("userVariable.inc");
	if (!mysql_connect($hostName, $dbUserName, $dbPassword)){ 
		displayErrorPage("Cannot connect to ".$hostName."!");
      	exit;
    }
	@mysql_select_db($dbName) or die (mysql_error());
?>

filename: userVariable.inc

<?
	/* Common MySQL connection variables */
	$hostName="localhost"; // server name or hostname of your database
	$dbUserName="";  // database username
	$dbPassword="";  // database password 
	$dbName="";  // database name used in your mysql
?>

by the way u dont need to edit the file queryList.php and dbConnect.php coz it is standard bu u can make some revisions if u want to make it better.

in your userVariable.inc aasign the values of it which defines your server and your database

and in testQuery.php just change the query and the list of the values you want to grab and u want to display..

try it men and enjoy 🙂

[mndwn]

Thankyou mate, I hope it will resolve my pagination problem. Guess We'll find out in the next few hrs 🙂