Need Pagination help

[jay7981]

Hey all,

I am trying to understand pagination and how the best way to implement but i am getting very very confused and my head is about to burst@... and cant seem to get any various ways to work with the code i currently have..

im trying to get 15 row to show per page

Any help with this would be GREAT! Thanks in advance.

 

<?php
include_once("./pages/membership/config.php");
$result=mysql_query("SELECT * FROM members ORDER BY LName ASC");
$total_rows = mysql_num_rows($result);
?>
<table align="center" width="100%" border="0" cellspacing="2" cellpadding="2">
<td><p>There are <?php echo $total_rows ; ?> Members</p></td>
</tr>
<tr>
<td><?php
if ($loggedInUser->checkPermission(array(5)))
{
?>
<button onclick="window.location.href='member-db.php?add'">Add Member</button>
<?php
}
?></td>
</tr>
</table>
<br/>
<table width="75%" align="center" border=1 style="white-space:nowrap">
<tr bgcolor='#CCCCCC'>
<td><div align="center">Action</div></td>
<td><div align="center">MemberID</div></td>
<td><div align="center">Name</div></td>
<td><div align="center">Department</div></td>
<td><div align="center">Status</div></td>
</tr>
<?php
while($res=mysql_fetch_array($result)){
$status = "Active";
if ($res['Revoked_Removed']=="x"){
$status = "Revoked";
}
if ($res['Deceased']=="x"){
$status = "Deceased";
}
if ($res['Inactive']=="x"){
$status = "Inactive";
}
?>
<tr>
<td><a href="member-db.php?viewid&ID=<?php echo $res['ID']; ?>">View</a>
<?php
if ($loggedInUser->checkPermission(array(5)))
{
?>
| <a href="member-db.php?edit&ID=<?php echo $res['ID']; ?>">Edit</a> | <a href="member-db.php?delete&ID=<?php echo $res['ID']; ?>">Delete</a>
<?php
}
?>
</td>
<td><?php echo $res['mID']; ?></td>
<td><?php echo $res['FName']; ?> <?php echo $res['LName']; ?> <?php echo $res['Suffix']; ?></td>
<td><?php echo $res['Department']; ?></td>
<td><?php echo $status; ?></td>
<?php
}
?>
</table>

Edited February 21, 2013 by jay7981

[Jessica]

There is nothing in your code that attempts to either limit the number of rows displayed, or change that limit to paginate.

[jay7981]

i know, thats why i am here looking for help. i just for some reason cannot grasp the pagination concept... of course i am down with the flu and have a major bacterial infection of the lung so this could be contributing to my demise....

but this is the code i have been fighting with...

 

$page = isset($_GET['page']) ? (int) $_GET['page'] : 1;
$pages = implode(mysql_fetch_assoc(mysql_query("SELECT COUNT(key) FROM table")));
$pages = ceil($pages / 6);
foreach ($_GET as $key => $value) {
if ($key != "page") $querystring .= "$key=$value&";
}
for ($i = 1; $i <= $pages; $i++) {
echo "<a " . ($i == $page ? "class=\"selected\" " : "");
echo "href=\"?{$querystring}page=$i";
echo "\">$i</a> ";
}
$result = mysql_query("SELECT * FROM table LIMIT " . (($page - 1) * 6) . ", 6");

Edited February 21, 2013 by jay7981

[Jessica]

This forum is for help with code you've written. You need to make an attempt at the code to paginate your results, or explain what exactly you are stuck on. There are plenty of good tutorials on pagination.

[jay7981]

see my edit sorry was posting as you did

[Jessica]

1. Don't nest mysql_* functions like that. It prevents you checking for errors.

2. The limit clause should be LIMIT offset, rowcount. You can only select 6 rows with your current logic. So if you want 15, change it to 15.

 

Otherwise it looks fine at a glance. You need to explain WHAT DOES NOT WORK if you want help. 

[jay7981]

ok lets start over... i have found a slightly better tutorial (here on Freaks) so i am attempting to use that instead of above code so please disreguard previous posts....

 

I have the pagination implemeted and now my query is no longer displaying results... i get a blank table

i take COUNT(*) away and maek it just * and i get my results back but no pagination ...

 

here is the code

<?php
include_once("./pages/membership/config.php");
$result=mysql_query("SELECT COUNT(*) FROM botb_members ORDER BY LName ASC");
$total_rows = mysql_num_rows($result);
$r = mysql_fetch_row($result);
$numrows = $r[0];
// number of rows to show per page
$rowsperpage = 15;
// 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;
?>
<table align="center" width="100%" border="0" cellspacing="2" cellpadding="2">
 <td><p>There are <?php echo $total_rows ; ?> Members</p></td>
 </tr>
 <tr>
 <td><?php
if ($loggedInUser->checkPermission(array(5)))
{
 ?>
  <button onclick="window.location.href='member-db.php?add'">Add Member</button>
<?php 
}
?></td>
 </tr>
</table>
<br/>
<table width="75%" align="center" border=1 style="white-space:nowrap">
<tr bgcolor='#CCCCCC'>
<td><div align="center">Action</div></td>
<td><div align="center">MemberID</div></td>
<td><div align="center">Name</div></td>
<td><div align="center">Department</div></td>
<td><div align="center">Status</div></td>
</tr>
<?php
while($res=mysql_fetch_array($result)){
$status = "Active";
if ($res['Revoked_Removed']=="x"){
$status = "Revoked";
}
if ($res['Deceased']=="x"){
$status = "Deceased";
}
if ($res['Inactive']=="x"){
$status = "Inactive";
}
?>
<tr>
<td><a href="member-db.php?viewid&ID=<?php echo $res['ID']; ?>">View</a>
<?php
if ($loggedInUser->checkPermission(array(5)))
{
 ?>
  | <a href="member-db.php?edit&ID=<?php echo $res['ID']; ?>">Edit</a> | <a href="member-db.php?delete&ID=<?php echo $res['ID']; ?>">Delete</a>
<?php 
}
?>
</td>
<td><?php echo $res['mID']; ?></td>
<td><?php echo $res['FName']; ?> <?php echo $res['LName']; ?> <?php echo $res['Suffix']; ?></td>
<td><?php echo $res['Department']; ?></td>
<td><?php echo $status; ?></td>
<?php
}
$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']}?view&currentpage=1'><<</a> ";
  // get previous page num
  $prevpage = $currentpage - 1;
  // show < link to go back to 1 page
  echo " <a href='{$_SERVER['PHP_SELF']}?view&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']}?view&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']}?view&currentpage=$nextpage'>></a> ";
  // echo forward link for lastpage
  echo " <a href='{$_SERVER['PHP_SELF']}?view&currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
?>
</table>

[Jessica]

Computers do exactly what you tell them to. Read your code and think about what you're telling it to do.

Hint: you never tell it to get your rows.

[jay7981]

isn't that what

$total_rows = mysql_num_rows($result);
$r = mysql_fetch_row($result);
$numrows = $r[0];

 

 

does?

[Jessica]

That's getting you the number of rows, if your query is what you posted. Don't rush, read your code line by line and explain what it does in English.

[jay7981]

please dont take this the wrong way as i am sick and dopped up on all sorts of meds at the moment,

 

i cant see any error from my point of view as i have already stated i dont know anything about pagination, i cant learn from a mistake if i cannot identify the mistake and all i really want to do is learn, so if you would kindly point out the mistake and help me through this without being cyrptic it would be much appreciated.

[jay7981]

I Guess you were right i just needed to look a bit harder ... so after a shot of whiskey and a smoke ...

This works

<?php
include_once("./pages/membership/config.php");
$count=mysql_query("SELECT COUNT(*) FROM botb_members");
$r = mysql_fetch_row($count);
$numrows = $r[0];
// number of rows to show per page
$rowsperpage = 15;
// 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;
?>
<?php
$result=mysql_query("SELECT * FROM botb_members ORDER BY LName ASC LIMIT $offset, $rowsperpage");
?>
<table align="center" width="100%" border="0" cellspacing="2" cellpadding="2">
 <td><p>There are <?php echo $numrows ; ?> Members</p></td>
 </tr>
 <tr>
 <td><?php
if ($loggedInUser->checkPermission(array(5)))
{
 ?>
  <button onclick="window.location.href='member-db.php?add'">Add Member</button>
<?php 
}
?></td>
 </tr>
</table>
<br/>
<table width="75%" align="center" border=1 style="white-space:nowrap">
<tr bgcolor='#CCCCCC'>
<td><div align="center">Action</div></td>
<td><div align="center">MemberID</div></td>
<td><div align="center">Name</div></td>
<td><div align="center">Department</div></td>
<td><div align="center">Status</div></td>
</tr>
<?php
while($res=mysql_fetch_array($result)){
$status = "Active";
if ($res['Revoked_Removed']=="x"){
$status = "Revoked";
}
if ($res['Deceased']=="x"){
$status = "Deceased";
}
if ($res['Inactive']=="x"){
$status = "Inactive";
}
?>
<tr>
<td><a href="member-db.php?viewid&ID=<?php echo $res['ID']; ?>">View</a>
<?php
if ($loggedInUser->checkPermission(array(5)))
{
 ?>
  | <a href="member-db.php?edit&ID=<?php echo $res['ID']; ?>">Edit</a> | <a href="member-db.php?delete&ID=<?php echo $res['ID']; ?>">Delete</a>
<?php 
}
?>
</td>
<td><?php echo $res['mID']; ?></td>
<td><?php echo $res['FName']; ?> <?php echo $res['LName']; ?> <?php echo $res['Suffix']; ?></td>
<td><?php echo $res['Department']; ?></td>
<td><?php echo $status; ?></td>
<?php
}
$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']}?view&currentpage=1'><<</a> ";
  // get previous page num
  $prevpage = $currentpage - 1;
  // show < link to go back to 1 page
  echo " <a href='{$_SERVER['PHP_SELF']}?view&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']}?view&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']}?view&currentpage=$nextpage'>></a> ";
  // echo forward link for lastpage
  echo " <a href='{$_SERVER['PHP_SELF']}?view&currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
?>
</table>