Pagination/LIMIT problem

MargateSteve · November 14, 2010

If a LIMIT on rows is set by the dropdown, the Pagination links do not reflect this. For example, if you had 100 rows showing 25 records per page, the pagination should show 4 pages, whereas if there were 10 records per page it should show 10 pages.

At the moment, the pagination shows 4 pages (which is correct for the page default of 25 records) but if someone chooses to show 10 records per page, it still only shows the 4 pages in the pagination links and if you click one it takes you the relevant page, ignoring the user selected LIMIT. If a user selects 5 records per page in the dropdown and then clicks page 2 in the pagination it shows records 26-50 when it should really show records 6-10.

On the flip side, without selecting a number of rows in the dropdown, if you click on page 2 it shows records 26-50 and if you then choose 5 in the dropdown it does show the first 5 from that page (ie 26-30).

So they do sort of work together but I would really like the pagination to alter depending on the number of rows selected.

I hope that has not confused everyone too much but below is the code that I am using, in full as everything is related it seems. I have commented it as best I can to try to clear things up but if it would be better for me to break it down into the relevant bits then just shout!

There are two sets of pagination on there, top and bottom, and the top one is after the

// TOP PAGINATION

comment

and the form for the dropdown is after

// LIMIT ROWS DROPDOWN

If anyone can explain how I can get this to work properly, or whether it is even possible, I would be extremely grateful!

Thanks in advance

Steve

<?php require_once('../../Connections/Test.php'); ?>

<?php
// Make a MySQL Connection
mysql_select_db($database_Test, $Test);

// How many rows to show per page
$rowsPerPage = 25;

// Show the first page by default
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

// Counting the offset to show the right records per page
$offset = ($pageNum - 1) * $rowsPerPage;

// Retrieve all the data from the seasons table
$query = "SELECT *,
DATE_FORMAT(`season_start`,' %D %M %Y') AS startdate,
DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate
FROM seasons ";
$query .= ( isset($_POST['records_per_page']) && intval($_POST['records_per_page']) > 0 ) ? 'LIMIT ' .$offset.',' . (int) $_POST['records_per_page'] : 'LIMIT ' .$offset.',' . $rowsPerPage;

$result = mysql_query( $query ) or die('Query: ' . $query . '<br>Produced error: ' . mysql_error() . '<br>');


//Query for the DECADES INDEX at the top
$links = mysql_query("SELECT DISTINCT SUBSTRING(season_name,1,3) as letter FROM seasons ORDER BY 1") or die(mysql_error());

// PAGINATION
// Find the number of records
$page_query   = "SELECT COUNT(season_name) AS numrows FROM seasons";
$page_result  = mysql_query($page_query) or die('Error, query failed');
$page_row     = mysql_fetch_array($page_result, MYSQL_ASSOC);
$page_numrows = $page_row['numrows'];

// Divide the number of records by records per page to get number of pages
$maxPage = ceil($page_numrows/$rowsPerPage);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Season List</title>
<link href="../../styles/databaseedit.css" rel="stylesheet" type="text/css" />


</head>

<body>

<div class="container">
  <?php include("/homepages/46/d98455693/htdocs/Templates/database/header.html"); ?>
<?php include("/homepages/46/d98455693/htdocs/Templates/database/left.html"); ?>
  <div class="content">
    <h1>
      SEASON LIST</h1>
    <p>Filter by Decade<br />
      <?php
// DECADES LISTING AT TOP OF PAGE	
// Fetch the records of the DECADE $row
while($linkrow = mysql_fetch_array($links))
{
// Show links to DECADE queries and add "0's" to the result
echo '<a href="seasonslistdecades.php?searchstring=';
echo $linkrow['letter']. '">'.$linkrow['letter'].'0\'s</a> ';
}
?>
    </p>
    <hr />
   <?php 
// TOP PAGINATION
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';

for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"$self?page=$page\">$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?page=$page\"><a href=\"$self?page=$page\"><img src=\"../../images/icons/Prev.png\" height=\"20\"  alt=\"Previous\" title=\"Previous Page\" /></a> ";

   $first = " <a href=\"$self?page=1\"><img src=\"../../images/icons/First.png\" height=\"20\"  alt=\"First\" title=\"First Page\" /></a> ";
} 
else
{
   $prev  = ' '; // we're on page one, don't print previous link
   $first = ' '; // nor the first page link
}

if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"$self?page=$page\"><img src=\"../../images/icons/Next.png\" height=\"20\"  alt=\"Next\" title=\"Next Page\" /></a> ";

   $last = " <a href=\"$self?page=$maxPage\"><img src=\"../../images/icons/Last.png\" height=\"20\"  alt=\"Last\" title=\"Last Page\" /></a> ";
} 
else
{
   $next = ' '; // we're on the last page, don't print next link
   $last = ' '; // nor the last page link
}

// print the navigation link
echo $first . $prev . $nav . $next . $last;?>

// LIMIT ROWS DROPDOWN
   <div class="drop_right" ><form action="" method="post" name="records_per_page" target="_self">
     Number of Records per page
     <select name="records_per_page" id="records_per_page">
       <option value="5">5</option>
       <option value="10">10</option>
       <option value="15">15</option>
       <option value="20">20</option>
       <option value="25">25</option>
       <option value="30">30</option>
       <option value="40">40</option>
       <option value="50">50</option>
   </select> <input type="submit" name="SUB" id="SUB" value="Submit" />
   </form></div>
<p>
      <?php
// SEASONS LISTING
// Show the Table Headers
  echo '
  <table  align="center" cellspacing="0" cellpadding="0">
  <tr>
  <th>ID</th>
  <th>Name</th>
  <th>From</th>
  <th>To</th>
  <th>Edit</th>
  </tr>
  ';

// Show the Season Data
  while($row = mysql_fetch_array($result))
{
echo '
<tr>
    <td>'.$row['season_id'] .'</td>
<td>'.$row['season_name'] .'</td>
    <td>'.$row['startdate'] .'</td>
    <td>'.$row['enddate'] .'</td>	 
<td><img src="../../images/icons/Search.png" height="20"  alt="View" /> 
	<img src="../../images/icons/Edit.png" height="20"  alt="Edit" />
 	<img src="../../images/icons/Close.png" height="20"  alt="Delete" /></td>
  	</tr>
' ;
}
echo '</table> ';


// BOTTOM PAGINATION
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';

for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"$self?page=$page\">$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?page=$page\"><a href=\"$self?page=$page\"><img src=\"../../images/icons/Prev.png\" height=\"20\"  alt=\"Previous\" title=\"Previous Page\" /></a> ";

   $first = " <a href=\"$self?page=1\"><img src=\"../../images/icons/First.png\" height=\"20\"  alt=\"First\" title=\"First Page\" /></a> ";
} 
else
{
   $prev  = ' '; // we're on page one, don't print previous link
   $first = ' '; // nor the first page link
}

if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"$self?page=$page\"><img src=\"../../images/icons/Next.png\" height=\"20\"  alt=\"Next\" title=\"Next Page\" /></a> ";

   $last = " <a href=\"$self?page=$maxPage\"><img src=\"../../images/icons/Last.png\" height=\"20\"  alt=\"Last\" title=\"Last Page\" /></a> ";
} 
else
{
   $next = ' '; // we're on the last page, don't print next link
   $last = ' '; // nor the last page link
}

// print the navigation link
echo $first . $prev . $nav . $next . $last;
?>
      <br />
    <p> </p>

    <!-- end .content --></div>
  <div class="footer">
    <p>This .footer contains the declaration position:relative; to give Internet Explorer 6 hasLayout for the .footer and cause it to clear correctly. If you're not required to support IE6, you may remove it.</p>
    <!-- end .footer --></div>
<!-- end .container --></div>
</body>
</html>

MargateSteve · November 14, 2010

Sorry, forgot to mention something.

Although not vital in the page I am working on at the moment, for some of the others, I would also be wanting to include a 'Start from Row Number' option so they could choose to show 5 records from record 7, for example. If that would affect the way things work again I would be appreciative if someone could explain to implement that with all of the above!

Thanks

Steve

PFMaBiSmAd · November 14, 2010

Wouldn't the correct logic be to pass the $rowsPerPage value as a get parameter in the URLs and if it is set to a value greater than zero to override the default $rowsPerPage = 25; line of code?

Since your calculations then use the $rowsPerPage value to determine the correct LIMIT clause in the query, getting the $rowsPerPage variable to have the correct value at the start of the calculations should make the code work.

MargateSteve · November 14, 2010

Firstly, I am not sure where the first few lines of my post went so it should have started something like........

I am trying to implement both pagination and a dropdown to limit the number of rows that work with each other.

I have a test page up and running at http://www.margate-fc.com/content/test/seasonslist.php and both the pagination and the dropdown (mainly thanks to Pikachu2000 in this thread http://www.phpfreaks.com/forums/php-coding-help/using-dropdown-to-limit-rows/) work fine on their own but they do now work together.

Secondly, thanks for the suggestion PFMaBiSmAd. I think I understand what you are trying to suggest but will have a bit of a read up on using values as a get parameter (searching on here first!) before I try anything.

Thanks

Steve

PFMaBiSmAd · November 14, 2010

If you already have an active thread for THIS PROBLEM, don't start another one.

This will get you closer (I also removed the duplicate code producing the pagination links since the top and bottom is the same. Just echo the values you already have.) -

<?php
require_once('../../Connections/Test.php');
// Make a MySQL Connection
mysql_select_db($database_Test, $Test);

// How many rows to show per page
$rowsPerPage = isset($_GET['rpp']) && $_GET['rpp'] > 0 ? (int)$_GET['rpp'] : 25; // use $_GET['rpp'] or a default of 25

// if the records per page form is submitted, process it and set the $rowsPerPage to the new value
if(isset($_POST['SUB'])){
// form was submitted
$rowsPerPage = (int)$_POST['records_per_page'];
}

$rpp = "&rpp=$rowsPerPage"; // add this get paraemter to the links

// Show the first page by default
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

// Counting the offset to show the right records per page
$offset = ($pageNum - 1) * $rowsPerPage;

// Retrieve all the data from the seasons table
$query = "SELECT *,
DATE_FORMAT(`season_start`,' %D %M %Y') AS startdate,
DATE_FORMAT(`season_end`,'%D %M %Y') AS enddate
FROM seasons ";
$query .= "LIMIT $offset,$rowsPerPage";

$result = mysql_query( $query ) or die('Query: ' . $query . '<br>Produced error: ' . mysql_error() . '<br>');
   
//Query for the DECADES INDEX at the top
$links = mysql_query("SELECT DISTINCT SUBSTRING(season_name,1,3) as letter FROM seasons ORDER BY 1") or die(mysql_error());

// PAGINATION
// Find the number of records
$page_query   = "SELECT COUNT(season_name) AS numrows FROM seasons";
$page_result  = mysql_query($page_query) or die('Error, query failed');
$page_row     = mysql_fetch_array($page_result, MYSQL_ASSOC);
$page_numrows = $page_row['numrows'];

// Divide the number of records by records per page to get number of pages
$maxPage = ceil($page_numrows/$rowsPerPage);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Season List</title>
<link href="../../styles/databaseedit.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div class="container">
  <?php include("/homepages/46/d98455693/htdocs/Templates/database/header.html"); ?>
<?php include("/homepages/46/d98455693/htdocs/Templates/database/left.html"); ?>
  <div class="content">
    <h1>
      SEASON LIST</h1>
    <p>Filter by Decade<br />
      <?php
// DECADES LISTING AT TOP OF PAGE   
// Fetch the records of the DECADE $row
   while($linkrow = mysql_fetch_array($links))
{
// Show links to DECADE queries and add "0's" to the result
   echo '<a href="seasonslistdecades.php?searchstring=';
   echo $linkrow['letter']. '">'.$linkrow['letter'].'0\'s</a> ';
}
   ?>
    </p>
    <hr />
   <?php 
// TOP PAGINATION
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';

for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"$self?page=$page$rpp\">$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?page=$page$rpp\"><a href=\"$self?page=$page$rpp\"><img src=\"../../images/icons/Prev.png\" height=\"20\"  alt=\"Previous\" title=\"Previous Page\" /></a> ";

   $first = " <a href=\"$self?page=1$rpp\"><img src=\"../../images/icons/First.png\" height=\"20\"  alt=\"First\" title=\"First Page\" /></a> ";
} 
else
{
   $prev  = ' '; // we're on page one, don't print previous link
   $first = ' '; // nor the first page link
}

if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"$self?page=$page$rpp\"><img src=\"../../images/icons/Next.png\" height=\"20\"  alt=\"Next\" title=\"Next Page\" /></a> ";

   $last = " <a href=\"$self?page=$maxPage$rpp\"><img src=\"../../images/icons/Last.png\" height=\"20\"  alt=\"Last\" title=\"Last Page\" /></a> ";
} 
else
{
   $next = ' '; // we're on the last page, don't print next link
   $last = ' '; // nor the last page link
}

// print the navigation link
echo $first . $prev . $nav . $next . $last;?>

// LIMIT ROWS DROPDOWN
   <div class="drop_right" ><form action="" method="post" name="records_per_page" target="_self">
     Number of Records per page
     <select name="records_per_page" id="records_per_page">
<?php
	$values = array(5,10,15,20,25,30,40,50);
	$options = '';
	foreach($values as $value){
		$select = ($rowsPerPage == $value) ? ' selected="selected"':'';
		$options .= "<option value='$value'$select>$value</option>\n";
	}
	echo $options;
?>
   </select> <input type="submit" name="SUB" id="SUB" value="Submit" />
   </form></div>
<p>
      <?php
// SEASONS LISTING
// Show the Table Headers
  echo '
  <table  align="center" cellspacing="0" cellpadding="0">
  <tr>
  <th>ID</th>
  <th>Name</th>
  <th>From</th>
  <th>To</th>
  <th>Edit</th>
  </tr>
  ';

// Show the Season Data
  while($row = mysql_fetch_array($result))
{
   echo '
   <tr>
    <td>'.$row['season_id'] .'</td>
   <td>'.$row['season_name'] .'</td>
    <td>'.$row['startdate'] .'</td>
    <td>'.$row['enddate'] .'</td>    
   <td><img src="../../images/icons/Search.png" height="20"  alt="View" /> 
      <img src="../../images/icons/Edit.png" height="20"  alt="Edit" />
       <img src="../../images/icons/Close.png" height="20"  alt="Delete" /></td>
     </tr>
   ' ;
}
   echo '</table> ';

// BOTTOM PAGINATION
// print the navigation link
echo $first . $prev . $nav . $next . $last;
?>
      <br />
    <p> </p>

    <!-- end .content --></div>
  <div class="footer">
    <p>This .footer contains the declaration position:relative; to give Internet Explorer 6 hasLayout for the .footer and cause it to clear correctly. If you're not required to support IE6, you may remove it.</p>
    <!-- end .footer --></div>
<!-- end .container --></div>
</body>
</html>

MargateSteve · November 14, 2010

That works almost 100% perfectly! Yet another excellent piece of help from the community!

The only thing that is not working with it is a slight problem if you are on a page number and you then choose a LIMIT which does not reach that page, but other than that it is just what I was looking for.

I will now take time to read through it so I understand it as part of my learning curve.

Thanks again

Steve

Sign In

Pagination/LIMIT problem

Recommended Posts

MargateSteve

Link to comment

Share on other sites

MargateSteve

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

MargateSteve

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

MargateSteve

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information