#native_company# #native_desc#
#native_cta#

Previous, Next and page numbers for a database listing.

By Hurgh
on May 8, 2003

Version: 1.00

Type: Sample Code (HOWTO)

Category: Databases

License: GNU General Public License

Description: I could not find a script that i liked here that would suit what i needed, so i wrote my own and decided to put it here for anyone else that might need some help with this.
It uses the MySQL LIMIT function so if you dont have a MySQL database or one that has the LIMIT function then this will not work for you.

Please leave my little credit note in the code. Thanks

If you would like to discuss this code you can do so at my forums, just make a topic in the Chit Chat….

-|- Hurgh -|-

________________
Website: http://www.hurgh.org/
Forums: http://forums.hurgh.org/

<?php
	/*
		This script was written by Hurgh (http://www.hurgh.org/)
		You can modify it and do what you want with it, all i ask is that
		you leave this header in here, and if you feel like it, drop past
		my website and leave me a note telling me that you used it and
		where so that i can go and check it out. I always like to see
		other people's ideas and how they use code.

		Enjoy
		-|- Hurgh -|-
	*/
?>

<?php
	/*
		Database Variables used for the Staff address list
	*/
	
	global $dbServer, $dbUser, $dbPass, $dbDatabase;
	
	$dbServer				=	"localhost";
	$dbUser					=	"user";
	$dbPass					=	"PASS";
	$dbDatabase				=	"database";
?>

<?php
	function ConnectToDatabase()
	{
		global $dbServer, $dbUser, $dbPass, $dbDatabase;
		global $svrConn, $dbConn;
		
		$svrConn = mysql_connect($dbServer, $dbUser, $dbPass) or die("Couldn't Connect to server " . $dbServer . "!");
		$dbConn = mysql_select_db($dbDatabase, $svrConn) or die("Couldn't connect to database " . $dbDatabase . "!");
	} /* function ConnectToDatabase() */
?>

<?php
	global $svrConn;
	$TotalToShow = 3;
	$StartLimit = 0;
	/*
		Setup the $Start Limit and the $EndLimit so that the SQL statment works
	*/
	if( isset($_GET['Total']) )
		$TotalToShow = $_GET['Total'];
		
	if( isset($_GET['Start']) )
		If( $_GET['Start'] < 0 )
			$StartLimit = 0;
		else
			$StartLimit = $_GET['Start'];
	
	
	/* 
		this is just a hack to make something work the way i needed for the application. 
		it will probably not help you any, but you do need the line
		-- $Limit = $StartLimit . ", " . $TotalToShow; --
		otherwise the sqlstatment will break.
	*/
	if( $TotalToShow == 1 )
	{
		if( $StartLimit > 0 )
			$Limit = $StartLimit;
		else
			$Limit = 1;
	} else {
		$Limit = $StartLimit . ", " . $TotalToShow;
	}
	
	
	// Open a Connection to the database
	ConnectToDatabase();
	
	// Query the database to find out how many records there is all up
	$qStr = "SELECT ID FROM " . $dbStaffListingTable . ";";
	$NumRecords = mysql_num_rows(mysql_query($qStr));
	
	// divide the total number of records with the number to show per page
	$TotalPages = (int)($NumRecords / $TotalToShow);
	
	// Check for any remaning records and if there is one the add
	// another page to the list
	$NumRemaning = $NumRecords % $TotalToShow;
	if( $NumRemaning >= "1" )
		$TotalPages++;
	
	
	// New query to select all the data but only the number that was requested
	$qStr = "SELECT * FROM <TableName> ORDER BY LastName, FirstName DESC LIMIT " . $Limit . ";";
	
	// Get the results from the query
	$result = mysql_query($qStr);
	// count how many results were received
	$NumRows = mysql_num_rows($result);
	
	// Print some useful info so that we can make sure it is working
	echo "SQL = " . $qStr . "<br>";
	echo "Records = "" . $NumRecords . ""<br>";
	echo "Rows = "" . $NumRows . ""<br>";
	echo "Total To Show = "" . $TotalToShow . ""<br>";
	echo "Start Limit = "" . $StartLimit . ""<br>";
	echo "Total Pages = "" . $TotalPages . ""<br>";
	echo "Number Remaning = "" . $NumRemaning . ""<br>";
	
	
	// Print out the results now
	echo "<br><h1>Results</h1>";
	
	
	/* 
		these results are just junk stuff, change this to what you
		will be using
	*/
	while( $row = mysql_fetch_array($result))
	{
		echo "ID = " . $row['ID'] . "<br>";
		echo "Last Name = " . $row['LastName'] . "<br>";
		echo "First Name = " . $row['FirstName'] . "<br>";
		echo "Home Phone= " . $row['HomePhone'] . "<br><br>";
	}
	
	// close the database connection (not needed but i like to do it anyway
	mysql_close($svrConn);
	
	
	// Show the previous Link
	if( $StartLimit != 0 )
	{
?>
<a href="<?= $_SERVER['SCRIPT_NAME'] ?>?Total=<?= $TotalToShow ?>&Start=<?= ($StartLimit-$TotalToShow)?>">Previous</a>
<?php
	} else {
		echo "Previous ";
	} /* if( $StartLimit != 0 ) */
	
	// show the page numbers
	$NextStartPage = 0;
	echo " ";
	for( $x=0; $x < $TotalPages; $x++ )
	{
		if( $StartLimit == $NextStartPage )
			echo ($x+1) . " ";
		else
			echo "<a href="" . $_SERVER['SCRIPT_NAME'] . "?Total=" . $TotalToShow . "&Start=" . $NextStartPage . "">" . ($x+1) . "</a> ";
		
		$NextStartPage = $NextStartPage+$TotalToShow;
	}
	
	
	// Show the Next Link
	if( ($StartLimit + $TotalToShow) <= ($NumRecords - 1) )
	{
?>
<a href="<?= $_SERVER['SCRIPT_NAME'] ?>?Total=<?= $TotalToShow ?>&Start=<?= ($TotalToShow+$StartLimit) ?>">Next</a>
<?php
	} else {
		echo " Next";
	} /* if( ($StartLimit + $TotalToShow) <= ($NumRecords - 1) ) */
?>