CODEWITHSUNDEEP
phpmysqlpagination
Chelle
Chelle

Reputation: 11

Pagination with PHP and MySQL

I've tried a load of different code snippets that are out there for using pagination in my result set. I've got the same problem in each case. Using LIMIT on the end of the query I can display the first page correctly along with the navigation links for the correct number of pages (so the code doing the calculations with number of rows returned and number to display on each page is right), but when you click to follow the link to any of the other pages then they're blank. So how do I get the updated query to run again and display my results on the subsequent pages?

This is one piece of code that I've tried:

$rowsperpage = 10;
$totalpages = ceil($numrows / $rowsperpage);

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

// if current page is greater than total pages...
if ($currentpage > $totalpages) { $currentpage = $totalpages; } 

// if current page is less than first page...
if ($currentpage < 1) { $currentpage = 1; } 

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

// run the query
$sql = "SELECT * FROM table WHERE keyword LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $link) or trigger_error("SQL", E_USER_ERROR);
// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result)) 
{   
     //echo data for each record here
} 

// building pagination links 
$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

Not permitted to show rest of navigation code due to too many hrefs, but the links appear correctly. The problem seems to be how the query is carried over to the next page.

Upvotes: 0

Views: 1324

Answers (1)

Zimbabao
Zimbabao

Reputation: 8240

You have to use limit offset, rowcount.

Typically for nth page

offset= (n-1)*pagesize and rowcount=pagesize

Upvotes: 3

Related Questions