[RESOLVED] Recordset Paging

[tnylsej]

Hi all.. I would like to do recordset paging for the following codes:

<?php
mysql_select_db($database_tsis, $tsis);
$query_Recordset2 = "SELECT * FROM log ORDER BY Updated DESC";
$Recordset2 = mysql_query($query_Recordset2, $tsis) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
?>

<div id="main">
     <fieldset><legend class="heading">Activity Log</legend>

<table width="950" border="1" bordercolor="#CCCCCC">
  <tr>
	<th>Time</th>
    <th>Task</th>
	<th>Action By (Emp No)</th>
  </tr>
  <?php do { ?>
  <tr>
      <td><?php echo $row_Recordset2['Updated']; ?></td>
      <td><?php echo $row_Recordset2['Task']; ?> <?php echo $row_Recordset2['TableName']; ?></td>
      <td><?php echo $row_Recordset2['EmpNo']; ?></td>
  </tr>
  <?php } while ($row_Recordset2 = mysql_fetch_assoc($Recordset2)); ?>
</table>
	 </fieldset>
</div>

It should be able to display how many records of the total number of records are being showed as well as link to first, previous, next, last. I would like to display 20 records per page.

May I know how to code it?

Thanks

[bradgrafelman]

tnylsej wrote:

May I know how to code it?

I would search this board for keywords such as "pagination" as this topic has been covered many times.

[tnylsej]

Hi.. I've done a search on pagination on this forum and came out with the following codes.. but my links do not work.. the following are my codes:

<?php
$rowsPerPage = 20;
$pageNum = 1;
if(isset($_GET['page'])) { 
	$pageNum = $_GET['page']; 
} 
$offset = ($pageNum - 1) * $rowsPerPage;
mysql_select_db($database_tsis, $tsis);
$query_Recordset2 = "SELECT * FROM log ORDER BY Updated DESC LIMIT $offset, $rowsPerPage ";
$Recordset2 = mysql_query($query_Recordset2, $tsis) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
?>

<div id="main">
     <fieldset><legend class="heading">Activity Log</legend>
<?php
 mysql_select_db($database_tsis, $tsis);
$query_Recordset4 = "SELECT COUNT(*) AS numrows FROM log";
$Recordset4 = mysql_query($query_Recordset4, $tsis) or die(mysql_error());
$row_Recordset4 = mysql_fetch_assoc($Recordset4);
$numrows = $row['numrows'];
$maxPage = ceil($numrows/$rowsPerPage);
$self = $_SERVER['PHP_SELF']; 
if ($pageNum > 1) { 
	$page = $pageNum - 1; 
    $prev = " <a href=\"$self?submit=Submit&page=$page\">[Prev]</a> "; 
    $first = " <a href=\"$self?index_radio=6&submit=Submit&page=1\">[First Page]</a> "; 
} else { 
    $prev  = ' [Prev] ';       

    $first = ' [First Page] ';  

} 

if ($pageNum < $maxPage) { 
    $page = $pageNum + 1; 
    $next = " <a href=\"$self?page=$page\">[Next]</a> "; 
    $last = " <a href=\"$self?index_radio=6&submit=Submit&page=$maxPage\">[Last Page]</a> "; 
} else { 
    $next = ' [Next] ';    

    $last = ' [Last Page] '; 
} 

echo "<p><center>".$first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last."</center></p>"; 
?>		

<table width="950" border="1" bordercolor="#CCCCCC">
  <tr>
	<th>Time</th>
    <th>Task</th>
	<th>Action By (Emp No)</th>
  </tr>
  <?php for ($i = 0; $i < $totalRows_Recordset2; $i++) { ?>
  <tr>
      <td><?php echo $row_Recordset2['Updated']; ?></td>
      <td><?php echo $row_Recordset2['Task']; ?> <?php echo $row_Recordset2['TableName']; ?></td>
      <td><?php echo $row_Recordset2['EmpNo']; ?></td>
  </tr>
  <?php  } ?>
</table>

 </fieldset>
</div>

What's wrong with my codes?

[bogu]

Tell us, what problem do u encounter.

tnylsej wrote:

...but my links do not work...

how does the links work and how should the links work?

[tnylsej]

$self = $_SERVER['PHP_SELF']; 
if ($pageNum > 1) { 
    $page = $pageNum - 1; 
    $prev = " <a href=\"$self?submit=Submit&page=$page\">[Prev]</a> "; 
    $first = " <a href=\"$self?index_radio=6&submit=Submit&page=1\">[First Page]</a> "; 
} else { 
    $prev  = ' [Prev] ';        

    $first = ' [First Page] ';   

} 

if ($pageNum < $maxPage) { 
    $page = $pageNum + 1; 
    $next = " <a href=\"$self?page=$page\">[Next]</a> "; 
    $last = " <a href=\"$self?index_radio=6&submit=Submit&page=$maxPage\">[Last Page]</a> "; 
} else { 
    $next = ' [Next] ';     

    $last = ' [Last Page] '; 
} 

echo "<p><center>".$first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last."</center></p>"; 

The 'First Page', 'Prev', 'Next', 'Last Page' links couldn't work.. they are shown on the page but as text not as links. When I clicked on 'First Page', it should show me the first 20 records. When I clicked on 'Previous', it should show me records on the previous page. When I clicked on 'Next', it should show me the records on the next page. When I clicked on 'Last Page', it should show me records on the last page.

[bogu]

OK, u explain what is suppose to happen, now explain what is happening ...

[tnylsej]

I've already explained what's happening.. those first, last, next and previous links cant work, as in, you can't click on them. They appear to be text rather than links.

[bogu]

Could this be, because?

$pageNum <= 1 and $pageNum >= $maxPage

Check this out and let us know ...

[ClarkF1]

You had a couple of problems, one was using $row when it hadn't been set. Once I found that I noticed it was printing the same thing 20 times per page because of the for loop so I changed it to a while.

Here's the fixed code:

<?php
$rowsPerPage = 20;
$pageNum = 1;
if(isset($_GET['page'])) {
    $pageNum = $_GET['page'];
}
$offset = ($pageNum - 1) * $rowsPerPage;
mysql_select_db($database_tsis, $tsis);
$query_Recordset2 = "SELECT * FROM log ORDER BY Updated DESC LIMIT $offset, $rowsPerPage ";
$Recordset2 = mysql_query($query_Recordset2, $tsis) or die(mysql_error());
?>

<div id="main">
     <fieldset><legend class="heading">Activity Log</legend>
<?php
mysql_select_db($database_tsis, $tsis);
$query_Recordset4 = "SELECT COUNT(*) AS numrows FROM log";
$Recordset4 = mysql_query($query_Recordset4, $tsis) or die(mysql_error());
$row = mysql_fetch_assoc($Recordset4);
$numrows = $row['numrows'];
$maxPage = ceil($numrows/$rowsPerPage);
$self = $_SERVER['PHP_SELF'];
if ($pageNum > 1) {
    $page = $pageNum - 1;
    $prev = " <a href=\"$self?submit=Submit&page=$page\">[Prev]</a> ";
    $first = " <a href=\"$self?index_radio=6&submit=Submit&page=1\">[First Page]</a> ";
} else {
    $prev  = ' [Prev] ';       

    $first = ' [First Page] ';  

}

if ($pageNum < $maxPage) {
    $page = $pageNum + 1;
    $next = " <a href=\"$self?page=$page\">[Next]</a> ";
    $last = " <a href=\"$self?index_radio=6&submit=Submit&page=$maxPage\">[Last Page]</a> ";
} else {
    $next = ' [Next] ';    

    $last = ' [Last Page] ';
}

echo "<p><center>".$first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last."</center></p>";
?>        

<table width="950" border="1" bordercolor="#CCCCCC">
  <tr>
    <th>Time</th>
    <th>Task</th>
    <th>Action By (Emp No)</th>
  </tr>
  <?php while ($row_Recordset2 = mysql_fetch_assoc($Recordset2))
 { ?>
  <tr>
      <td><?php echo $row_Recordset2['Updated']; ?></td>
      <td><?php echo $row_Recordset2['Task']; ?> <?php echo $row_Recordset2['TableName']; ?></td>
      <td><?php echo $row_Recordset2['EmpNo']; ?></td>
  </tr>
  <?php } ?>
</table>

 </fieldset>
</div>

[tnylsej]

Ahh.. thanks! that solves the problem =D

<3<3<3 =D

[bogu]

...