Limit and Paginate Record

SAMPLE: A FEEDBACK SYSTEM


As this records grows from just 3 feedbacks to let’s say 3000 (three thousand) or more feedbacks, then you will run into load time error or delay as a result of  trying to display this huge number of record at once, this is where pagination comes in.

Have you notice that whenever you search for a popular word like “Programmer” on Google it turns out thousands of search results, and it paginates these items into 10 result per views (I refuse to use the word per page, because all the result still loads on one page, but programmatically limited or paginated) this is a typical use of pagination, so let’s look at how to do this in the next example.

To test this I have to enter at least 10 records (submitted using the feedback form), below is what I have.

more

 

The next screenshot below shows the paginated version.

Note that I limit the display to 3 records per page, because we don’t have many records, so you can adjust this setting later in the code. But first let’s see what this paginated version looks like before its code. 

feedback_pg.php
pg1

You can now click the navigation links to see more records, below are more screenshots of this.

pgn

Below is the feedback_pg.php code, although it is quite big but it is also well commented, so follow it carefully.

feedback_pg.php


<HTML>
<HEAD>
<TITLE> Company Feedback </TITLE>
</HEAD>
<BODY>
<b>FEEDBACK LIST</b>
<br />
<br />

<?php
//include the connection string
include("conn.php");

//the table header
print "
<table width=\"50%\" border=1>
<tr>
<td>NAME</td>
<td>EMAIL</td>
<td>COMMENT</td>
<td>DATE POST</td>
</tr>
";

 

// how many rows to show per page
$rowsPerPage = 3;

// by default we show first page
$pageNum = 1;

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

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

//query the table
$query  = "SELECT * FROM feedback ORDER BY id DESC LIMIT $offset, $rowsPerPage";

$result= mysql_query($query, $conn) or die(mysql_error());

if (mysql_num_rows($result) == 0) {
 print "SORRY: no data.";
 exit;
}

//loop through the query result and print
while($info = mysql_fetch_array($result)){

     $data_id = $info['id'];
     $data_name = $info['name'];
     $data_email = $info['email'];
     $data_comment = $info['comment'];
     $data_date = $info['date_submit'];

           print "
           <tr>
           <td> $data_name </td>
           <td> $data_email </td>
           <td> $data_comment </td>
           <td> $data_date </td>
           </tr>
           ";
}
print "</table>";

 

// how many rows we have in database
$query   = "SELECT COUNT(id) AS numrows FROM feedback";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

 

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// 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\">[Prev]</a> ";
    
     $first = " <a href=\"$self?page=1\"> [First Page]</a> ";
}
else
{
     $prev  = '&nbsp;'; // we're on page one, don't print previous link
     $first = '&nbsp;'; // nor the first page link
}

if ($pageNum < $maxPage)
{
     $page = $pageNum + 1;
     $next = " <a href=\"$self?page=$page\">[Next]</a> ";
     $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
     $next = '&nbsp;'; // we're on the last page, don't print next link
     $last = '&nbsp;'; // nor the last page link
}

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

?>

</BODY>
</HTML>

This pagination program is similar to the Selecting the records program, but contain more code to do paginating task, I bolded the main code that does the pagination.

The first block of code is

// how many rows to show per page
$rowsPerPage = 3;

// by default we show first page
$pageNum = 1;

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

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

I purposely comment the code to make the explanation easy, this code include the two variables $rowsPerPage and $pageNum the first one specify the number of record per page, while the second line set the record where the paging should start from, this is usually the first page. The next line is a select or decision that it check if the user have click to move to another page, is this is done the variable $pageNum is set to the next number of the selected page, the next line does the counting  of the offset using both variable, first it subtract 1 from $pageNum and multiply it by $rowsPerPage then keep the result in $offset
 
The next line that follows is a SELECT query but with a function LIMIT to limit the result of the query using the variables set earlier $offset and $rowsPerPage to set the beginning and the number of record per page or records to retrieve, which is already set to 3 ($rowsPerPage = 3)

$query  = "SELECT * FROM feedback ORDER BY id DESC LIMIT $offset, $rowsPerPage";

The remaining bolded code starting from  // how many rows we have in database…. to print $first . $prev . $nav . $next . $last; have the logic, (which is well commented) that fetch the number of rows in the feedback table and uses the result to generate the navigations link for pagination.

pg1

With the pagination now done we can now move to another related interesting topic.

 

Next »


Return to PHP Sample Main Page