Jump to content

Pagination in PHP/MySQL


shanzay

Recommended Posts

ARGHHHHH pagination, again? I know!

 

OK here is a code that I worked on to get a grocery store items list displayed in a table in a paginated design. Everything is working fine except I don't know how to dynamically display auto incremented indexID range on that particular page, like 1-10 11-20 21-30 31-40 and so on (something like amazon items in cart)  instead of page numbers << < 1 2 3 4 > >> as pagination hyperlink to move between different pages. Any help will be greatly appreciated.

 

 

<?php
// database connection info
$conn = mysql_connect('host','user','password') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('dbName',$conn) or trigger_error("SQL", E_USER_ERROR);

// find out how many rows are in the table 
$sql = "SELECT COUNT(*) FROM store";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 10;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

// get the info from the db 
$sql = "SELECT indexID, item, price FROM store LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

// while there are rows to be fetched...
echo "<p><table><tr><td><strong>Item</strong></td><td><strong>Price</strong></td></tr>";
while ($list = mysql_fetch_assoc($result)) {
   echo "<tr>";
   echo "<td>" . $list['item'] . "</td>";
   echo "<td>$" . $list['price'] . "</td>";
   echo "</tr>";
} // end while
echo "</table></p><br /><br />";


/******  build the pagination links ******/
// range of num links to show

$range = 3;

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " <b>$x</b> ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for
                 
// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
?>

Link to comment
Share on other sites

Just wrote this up for you to make ranges of pages.

 

<?php
function pageRange($page_number,$page_amount){
if($page_amount == ""){
$page_amount = 10;
}
$page_high = $page_number *$page_amount;
$page_low = $page_high -$page_amount +1;
$page_range = "$page_low-$page_high";
return $page_range;
}

//usage
$page_number = 1;
$page_amount = 10;
$page_range = pageRange($page_number,$page_amount);
echo $page_range;
?>

Link to comment
Share on other sites

I made a little demo with ranges of pages and groups of items.

 

http://get.blogdns.com/dynaindex/page-range.php

 

<?php
$page = mysql_real_escape_string($_GET['page']);
if (!isset($_GET['page']) or !is_numeric($_GET['page'])) {
$page = 1;
}
$range = range(1,10);

function pageRange($page_number,$page_amount=NULL){
if($page_amount == ""){
$page_amount = 10;
}
$page_high = $page_number *$page_amount;
$page_low = $page_high -$page_amount +1;
$page_range = "$page_low-$page_high";
return $page_range;
}


foreach($range as $page_number){
$page_range = pageRange($page_number,$page_amount);
echo "<a href='page-range.php?page=$page_number'>$page_range</a> ";
}
echo "<br />";
echo "Now on page $page, viewing items ".pageRange($page)."<br />";
?>

Link to comment
Share on other sites

QuickOldCar, thanks but somehow the code is not working the way I want it to work

 

Only this part is changing:

1-10 11-20 21-30 31-40 41-50 51-60 61-70 71-80 81-90 91-100

Now on page 9, viewing items 81-90,

 

while the page stays the same, i.e. only first 10 items on the list are displayed on each click.

 

It's also giving an error that $page_amount is an undefined variable.

 

If you look at amazon.com cart with more than 10 items in it, you will know what exactly I am talking about.

Link to comment
Share on other sites

The code was to give you ideas of how to go about achieving this with your code, not a drop in solution.

 

Your values are different. You need to use your page number values, the total posts, amounts per page and href style links.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.