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> ";
}
if ($i == $current_page) { // bold current page
$page_navigation .= "<b><a href='".$PHP_SELF."?page=".$i."'>".$i."</a></b> ";
} else {
$page_navigation .= "<a href='".$PHP_SELF."?page=".$i."'>".$i."</a> ";
}
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]?> </td>
<td><?=$itemArray[firstname]?> </td>
<td><?=$itemArray[lastname]?> </td>
</tr>
<?
}
}
mysql_free_result($Result);
?>
</table>
<?=$page_navigation?>
<? mysql_close($Link); ?>
</body>
</html>
