PHP Paging

[jc_gmk]

I have created a PHP page that gets results from my database,
I have also managed to use paging so that only 10 results are shown per page.

I have also created a link at the top of the page that creates page numbers

e.g.

Showing page 3 of 10 pages

First Page | Last page | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | Next Page | Last Page


The problem comes when i have say 5000 entries in the database.
The numbers just keep going and therefore run off the page.

Is there anyway that i can limit this list?

e.g.

Showing page 3 of 500 pages

First Page | Last page | 1 | 2 | 3 | 4 | 5 | Next Page | Last Page

[soloWebDev]

can you post your code that produces the page numbers. I've done it before with a limit command, but just want to check your's to verify.

[alexjewell]

You could probably use JavaScript too.

[jc_gmk]

The bit it seems i need to limit is $nav
I ideally want it to have two numbers either side e.g.

| 1 | 2 | 3 | 4 | 5 |

| 6 | 7 | 8 | 9 | 10 |




$rowsPerPage = 9; // how many rows to show per page
$pageNum = 1; // first page by default
// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

$offset = ($pageNum - 1) * $rowsPerPage; // counting the offset
$sort_by = trim(htmlspecialchars($_GET['sort_by'])); // Get the ORDER BY variable
$product_id = trim(htmlspecialchars($_GET['product_id'])); // Gets product ID number from link
$query = " SELECT * FROM mytable WHERE brand='$product_id' OR catagory='$product_id'" . " ORDER BY $sort_by LIMIT $offset, $rowsPerPage"; // Selects product
$result = mysql_query($query) or die("Query Failed:<br />" . mysql_error() . "<br /><br />Actual query:<br />" . $query); // pulls the results in to the "$result" variable

//page number code
$data = "SELECT COUNT(*) AS numrows FROM mytable WHERE brand='$product_id' OR catagory='$product_id'";
$results = mysql_query($data) or die("Query Failed:<br />" . mysql_error() . "<br /><br />Actual query:<br />" . $query);
$row = mysql_fetch_array($results, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages
$maxPage = ceil($numrows/$rowsPerPage);

// print the links to access each page
$self = $_SERVER['PHP_SELF'];

$nav = '';

for($page = 1; $page <= $maxPage; $page++)
{
if ($page == $pageNum)
{
$nav .= " $page "; // Prints current page number without link
}
else
{
$nav .= " <a href=\"$self?product_id=$product_id&page=$page&sort_by=$sort_by\">$page</a> ";
}
}
// creating previous and next link
// plus the link to go straight to
// the first and last page
if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?product_id=$product_id&page=$page&sort_by=$sort_by\">[Prev]</a> ";
$first = " <a href=\"$self?product_id=$product_id&page=1&sort_by=$sort_by\">[First]</a> ";
}
else
{
$prev = '&nbsp;'; // When on page one, don't print previous link
$first = '&nbsp;'; // nor the first page link
}

if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?product_id=$product_id&page=$page&sort_by=$sort_by\">[Next]</a> ";
$last = " <a href=\"$self?product_id=$product_id&page=$maxPage&sort_by=$sort_by\">[Last]</a> ";
}
else
{
$next = '&nbsp;'; // When on the last page, don't print next link
$last = '&nbsp;'; // nor the last page link
}
?>





<?php print "Showing page $pageNum of $maxpage pages"; ?>

<?php print $first . $prev . $nav . $next . $last; ?>