Technology. Education. Art (TEA) TEAlearn.org

Home > Courses > Web development using PHP and MySQL > Sorting and Pagination

Sorting and Pagination

Subject: Web development using PHP and MySQL

The feedback list must have displayed but your client may still complain, if he or she has to scroll down all the time to see the most recent feedback post.

Imagine having to scroll down to record 1000 to see you most recent message.

Sort record
We can sort our record in descending order so that the last record appears first or on top. To do this, we need to alter the SELECT statement to ORDER its result in DESC (descending order).


$query  = "SELECT * FROM feedback ORDER BY id DESC";

This will order the record to display in descending order. I ordered the record using the id field because it contain auto-generated ascending numbers.

Ascending order with Id field
This code below will sort our record using id in Ascending order:


$query  = "SELECT * FROM feedback ORDER BY id";

Ascending order with name field
You can even sort using "name" field


$query  = "SELECT * FROM feedback ORDER BY name";

Pagination
After sorting, it won't still be presentable have large record displayed at once, you can notice that pagination is done your gmail or search engine result. So the code below does that

feedback_table_paginate.php


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


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

// 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 = mysqli_query($conn, $query) or die(mysqli_error($conn));	

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

//loop through the query and print the result
while($info = mysqli_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  = mysqli_query($conn, $query) or die(mysqli_error($conn));	
$row     = mysqli_fetch_array($result);
$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>

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 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 links for pagination.

Page 1