Paging Results with PHP and MYSQL – Part 2

Submitted by Dharshin on Wed, 04/04/2007 - 17:40.

Welcome to part 2, if you didn’t read part 1, please do, because this is a continuation of that. Now we want to go a step further and add “First” and “Last” links to our navigation.
“First” should point to the first page of our result set and last should point to the last page.

If user is at the first page, “First” and “Previous” links should disabled and like wise “Last” and “Next” should be disabled at the last page of the result set. It makes sense right?

See the code below and I will explain the logic.

            $previous=$page-1;
            $next=$page+1;
$last=(int)($totResults/$maxResults);
            if($page != 1) {
                        echo "<a href='paging_first_last.php'>First</a> ";
                        echo "<a href='paging_first_last.php?page=". $previous ."'>Previous</a> ";
            }
            else {
                        echo "First ";
                        echo "Previous ";
            }

            if($totResults > ($from+$maxResults)) {
                        echo "<a href='paging_first_last.php?page=". $next ."'>Next</a> ";
                        echo "<a href='paging_first_last.php?page=". $last ."'>Last</a> ";
            }
            else {
                        echo "Next ";
                        echo "Last ";
            }

This code should come after we close the table tag with an echo. Firstly, I have identified the next and previous page numbers. Then we have to identify the last page. Remember, we are passing the page number as a parameter with the URL and we have a predefined number of results per page value.

$last=(int)($totResults/$maxResults);. Here, we have the floating-point division and then rounded that off to an integer value. The rest of the coding is simple, we disable the links of navigation text appropriately, as I mentioned earlier.

Now our next goal is to add page numbers. We are placing page numbers in between “First, Previous” and “Last, Next”.

Simply add this for-loop

      for($pageCount=1; $pageCount <= $lastPage; $pageCount++) {
            if ($pageCount!=$page)
            {
                  echo "<a href='paging_page_numbers.php?page=". $pageCount ."'> ". $pageCount ."</a> ";
            }
            else {
                  echo "[" .$pageCount. "] ";
            }
      }

What id does is simple. We are printing page numbers and providing a link to that page and for the current page, we do not need a link and we are highlighting that page number with square brackets. Lets say we have 4 pages and we are reading the second page, so page numbers will appear line 1 [2] 3 4. 1, 3 and 4 will have links and 2 is within brackets because we are now reading it.

Here is the full code.

<!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=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">

</style>
</head>

<body>
<table width="593" border="0" cellspacing="0" cellpadding="0">
<tr>
    <td width="105"><div align="center" class="style1">Headline ID </div></td>
    <td width="183"><div align="center"><span class="style1">Site Name</span> </div></td>
    <td width="305"><div align="center" class="style1">Feed URL </div></td>
</tr>
<?php

      $conn=mysql_connect('localhost', 'root') or die(mysql_error());
      mysql_select_db('temp', $conn);

      $page;
      $totResults;
      $maxResults=5;


      if(isset($_GET['page'])) {
            $page=$_GET['page'];
      }
      else {
            $page=1;
      }
      /* Getting the total number of results in the database */
      $calcRowsQuery="Select COUNT(*) as rows FROM headlines";
      $calcRowsRes=mysql_query($calcRowsQuery, $conn);
      $totResults=mysql_result($calcRowsRes, 0);

      $from=$maxResults*($page-1);
      $resQuery="SELECT * FROM headlines LIMIT $from, $maxResults";
      $result=mysql_query($resQuery);

    while($row = mysql_fetch_array($result)) {
            echo "<tr class='style3'>";
            echo " <td>".$row['hid']."</td>";
            echo " <td>".$row['sitename']."</td>";
            echo " <td>".$row['headlinesurl']."</td>";
            echo "</tr>";
      }

      echo "</table> \n <p class='style3'> <center>";

      $previousPage=$page-1;
      $nextPage=$page+1;
      $last=(int)($totResults/$maxResults);
      if($page != 1) {
            echo "<a href='paging_page_numbers.php'>First</a> ";
            echo "<a href='paging_page_numbers.php?page=". $previousPage ."'>Previous</a> ";
      }
      else {
            echo "First ";
            echo "Previous ";
      }

      for($pageCount=1; $pageCount <= $lastPage; $pageCount++) {
            if ($pageCount!=$page)
            {
                  echo "<a href='paging_page_numbers.php?page=". $pageCount ."'> ". $pageCount ."</a> ";
            }
            else {
                  echo "[" .$pageCount. "] ";
            }
      }



      if($totResults > ($from+$maxResults)) {
            echo "<a href='paging_page_numbers.php?page=". $nextPage ."'>Next</a> ";
            echo "<a href='paging_page_numbers.php?page=". $lastPage ."'>Last</a> ";
      }
      else {
            echo "Next ";
            echo "Last ";
      }

      echo "</center></p> \n";

      ?>
</body>
</html>

Please note that I haven’t paid much attention to security issues here, but before you implement this on a public server, consider about the security aspect as well, specially when you are reading the page number. It’s good idea that you always convert that to an integer $page=(int)($_GET['page']);.

Thanks
Dharshin

Bookmark/Search this post with: