Version: 2

Type: Sample Code (HOWTO)

Category: Databases

License: GNU General Public License

Description: The only variable you will need to change is to set the total number of rows you want to display on a single Web page. This script will retreive the number of rows in the database table, divide the number of rows in the table with the total number of rows to show. If there is any remaining data to show it will add one page to the total number of pages. Set the minimum number receive data for each page and set the maximum number to stop data for each page. Set up the display for the page links that will show at the bottom of the page. I have designed a sample template page to show you how to include the script into your PHP script.



<?php
// connection settings -- better to store in an include file
$Host = "myhost";
$User = "myusername";
$Password = "mypassword";
$DBName = "tablename";
$Link = mysql_connect ($Host, $User, $Password);
mysql_select_db($DBName);
?>
<html>
<body bgcolor="#FFFFFF" text="#000000">
<table border="0" cellspacing="1" cellpadding="2">
	<tr bgcolor="#DCDCDC">    
	  <td><b>ID</b></td> 
	  <td><b>First Name</b></td> 
	  <td><b>Last Name</b></td>
	</tr>
	<?
	// initialize paging options
	if (strlen($_GET['page'])>0) { $current_page = $_GET['page']; } else { $current_page = 1; }
	$number_of_pages = 1;
	$rows_to_show = 20; // number of rows you want displayed on each page
	$my_query = "SELECT * from users"; // the query you are performing

	// do paging math
	$Result = mysql_query($my_query);
	if ($Result) {
		$number_of_rows = mysql_num_rows($Result);
		// figure how many total pages
		$number_of_pages = floor($number_of_rows / $rows_to_show);
		$remaining_number_of_data = $number_of_rows % $rows_to_show;
		if ($remaining_number_of_data >= 1) {
			$number_of_pages = $number_of_pages + 1;
		}
		// create page navigation links at bottom of table, as needed
		if ($number_of_pages <= 1) {
			$page_navigation .= "";
		} else {
			for ($i=1; $i<=$number_of_pages; $i++) {
				if ($i == 1) { // start with PREV button
					if ($current_page <= 1) { $prev_number = 1; }
					else { $prev_number = $current_page - 1; }
					$page_navigation .= "<a href='".$PHP_SELF."?page=".$prev_number."'>[ PREV ]</a>&nbsp;";
				}
				if ($i == $current_page) { // bold current page
					$page_navigation .= "<b><a href='".$PHP_SELF."?page=".$i."'>".$i."</a></b>&nbsp;";
				} else  {
					$page_navigation .= "<a href='".$PHP_SELF."?page=".$i."'>".$i."</a>&nbsp;";
				}
				if ($i == $number_of_pages) { // end with NEXT button
					if ($current_page >= $number_of_pages) { $next_page = $number_of_pages; }
					else { $next_page = $current_page + 1; }
					$page_navigation .= "<a href='".$PHP_SELF."?page=".$next_page."'>[ NEXT ]</a>";
				}
			}
		}
	}

	// set starting point for database query
	if ($current_page == 1) { $start_row = 0; }
	else { $start_row = $rows_to_show * ($current_page-1); }
	// now, get only rows to be displayed
	$Result = mysql_query($my_query." LIMIT ".$start_row.", ".$rows_to_show);
	if ($Result) {
		// loop through results & build table rows
		while ($itemArray = mysql_fetch_array($Result)) {
		?>
		<tr bgcolor="#EEEEEE">
		  <td><?=$itemArray[userid]?>&nbsp;</td>
		  <td><?=$itemArray[firstname]?>&nbsp;</td>
		  <td><?=$itemArray[lastname]?>&nbsp;</td>
		</tr>
		<?
		}
	}
	mysql_free_result($Result);
	?>
</table>
<?=$page_navigation?>
<?	mysql_close($Link); ?>
</body>
</html>