Paging of records, also known as pagination, is a technique used to divide a large set of data into smaller, more manageable pages. This allows users to view the data in smaller chunks, rather than having to load all of the data at once.
Advantage of Paging.
Improved Performance: Paging can improve the performance of a website or application by loading only the data that is currently being viewed. This can reduce the page load time and make the website or application more responsive.
Easier to Navigate: Paging can make it easier for users to navigate through a large set of data. By dividing the data into smaller pages, users can quickly and easily find the information they are looking for.
Reduced Memory Usage: Paging can reduce the memory usage of a website or application. By loading only the data that is currently being viewed, the website or application can avoid loading all of the data at once, which can reduce the amount of memory that is required.
Improved Scalability: Paging can help to improve the scalability of a website or application. By dividing the data into smaller pages, the website or application can handle a larger number of users without slowing down.
Better User Experience: Paging can provide a better user experience by making it easier for users to find the information they are looking for and by reducing the amount of time it takes to load pages.
Our sample student table has 35 rows of data. We kept the number of rows per page is 5 by setting the value of $limit=5 . Using this $limit value the first page will display records from 1 to 5 and next page will start from 6th record , so each page will have one $offset value as received from the URL or the query string.
$offset=$_GET['offset'];// Collect data from Query string
The `$offset` value is retrieved from the URL (query string) and used in the SQL query. However, it is crucial to thoroughly validate and sanitize the value before using it in the query to prevent injection attacks. If the `$offset` value is not present in the URL (indicating the starting page), it should be explicitly set to 0.
if(strlen($offset) ==0){$offset=0;}
Using this $offset value we will set the fresh value for the $offset for previous page and next page by adding and subtracting $limit to this $offset value.
Here are the settings.
$limit = 5; // No of records to be shown per page.
$next = $offset + $limit; // offset value for Next page
$back = $offset - $limit; // offset value for Previous page
We also assigned page name to a variable so we can mangage the same from a single point while generating links for navigation.
$page_name="display-paging.php"; // Pange name for linking
Here we are using Prepared statements to execute the query and getting the rows from the table. This paramterized query is used to prevent the injection attack.
$sql="SELECT * FROM student LIMIT :offset, :limit"; // query with place holders
$stmt = $my_conn->prepare($sql);
$stmt->bindParam(':offset', $offset, SQLITE3_INTEGER);
$stmt->bindParam(':limit', $limit, SQLITE3_INTEGER);
$result=$stmt->execute();
Using the above code we will get Result object, then we will use fetchArray() to get an array of rows of data.
The total number of records in a dataset is essential for calculating the total number of pages that will be needed to display the entire dataset.
$nume=$my_conn->querySingle('SELECT count(*) FROM student');
There are three parts in the navigation.
Part I : Link to Previous page
Part 1 shows the link to Previous page. This link should be shown only if records are avilable ( based on the $offset value ). For this we will check the value of variable $back. This value is calculated above like this.
$back = $offset - $limit; // Starting number of record for Previous page
We will check this value of $back and show the link to navigate to previous page.
We need to be able to calculate the total number of pages in your dataset. This can be done by dividing the total number of rows in your dataset ( $nume ) by the number of rows per page ( $limit ).
Based on the total number of rows ( $nume ) we will break the rows and create pages based on $limit value. Here we used on For loop to create the links. The current page should not have any link and it is displayed by using higher size font.
$i=0;
$l=1;
for($i=0;$i < $nume;$i=$i+$limit){
if($i <> $offset){
echo " <a href='$page_name?offset=$i'>$l</a> ";
}
else { echo "<font size='4' color=red>$l</font>";} /// Current page is not displayed as link and given font color is red
$l=$l+1;
}
Part III : Link to Next page
Like Part 1 , we will show the link to Next page if the records are available based on $next value.
<?php
require 'menu.php';
$my_conn = new SQLite3('my_db.db');// Connect to Database
$offset=$_GET['offset'];// Collect data from Query string
if(strlen($offset) ==0){$offset=0;} // If not available in the Query string
/*******
$offset is the starting row number of the page. For first time it is taken as 0 or from first record .
It must be an integer.
You can add this additional check to ensure that nothing other than an integer is used.
$offset=$_GET['offset'];// Collect data from Query string
if(strlen($offset) > 0 and !is_numeric($offset)){
echo "Data Error";
exit;
}
********/
$limit = 5; // No of records to be shown per page.
$next = $offset + $limit; // Starting number of record for Next page
$back = $offset - $limit; // Starting number of record for Previous page
$page_name="display-paging.php"; // Pange name for linking
$sql="SELECT * FROM student LIMIT :offset, :limit"; // query with place holders
$stmt = $my_conn->prepare($sql);
$stmt->bindParam(':offset', $offset, SQLITE3_INTEGER);
$stmt->bindParam(':limit', $limit, SQLITE3_INTEGER);
$result=$stmt->execute();
echo "<br><center><table>";
while ($row=$result->fetchArray()) {
echo "<tr ><td>$row[id]</td><td>$row[name]</td>
<td>$row[class]</td><td>$row[mark]</td><td>$row[gender]</td></tr>";
}echo "</table></center><br><br>";
/////// Bottom navigation Links /////
$nume=$my_conn->querySingle('SELECT count(*) FROM student'); // Total number of records
echo "<table align = 'center' width='50%'><tr><td
align='left' width='30%'>";
if($back >=0) {
print "<a href='$page_name?offset=$back'>Previous</a>";}
echo "</td><td align=center width='30%'>";
$i=0;
$l=1;
for($i=0;$i < $nume;$i=$i+$limit){
if($i <> $offset){
echo " <a href='$page_name?offset=$i'>$l</a> ";
}
else { echo "<font size='4' color=red>$l</font>";} /// Current page is not displayed as link and given font color red
$l=$l+1;
}
echo "</td><td align='right' width='30%'>";
if($next < $nume) {
print "<a href='$page_name?offset=$next'>Next</a>";}
echo "</td></tr></table>";
?>
How to handle empty pages
Here the total pages are dynamically calculated based on total number of records in the Dataset and the number of records to be shown per page. So the sub pages and links are prepared automatically based on the values.
Using different source of database
The concept and the basic script remain same for other databases, only the connection string and some database specific functions will change. Check here for the PHP MySQL paging script.
Order of the record display
By using ORDER BY in our query we can change the listing order. Here we are showing the rows based on the marks in descending order.
$sql="SELECT * FROM student ORDER BY mark DESC LIMIT :offset, :limit ";
In ascending order using ASC ( default )
$sql="SELECT * FROM student ORDER BY mark ASC LIMIT :offset, :limit ";
OR
$sql="SELECT * FROM student ORDER BY mark LIMIT :offset, :limit ";
Using WHERE
We can filter the rows by using WHERE condition.
$sql="SELECT * FROM student WHERE class='Three' ORDER BY mark ASC LIMIT :offset, :limit ";
If we are using WHERE condition then we must use the same WHERE condition while finding out total number of rows in Dataset. Otherwise our counting of sub-pages will go wrong.
$nume=$my_conn->querySingle("SELECT count(*) FROM student WHERE class='Three'");
Sample script using PHP , SQLite3 connection and SQLite database : plus2net_sqlite3_v1