PHP SQLite Pagination Script for Efficient Data Management

DEMO

What is paging of records ?

Advantage of Paging.

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.

PHP SQLite3 Pagination of records.

$offset=$_GET['offset'];// Collect data from Query string

if(strlen($offset) ==0){$offset=0;}

$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

$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 "<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>";

Navigational links to different pages

$nume=$my_conn->querySingle('SELECT count(*) FROM student');

Part I : Link to Previous page

$back = $offset - $limit; // Starting number of record for Previous page

if($back >=0) {
print "<a href='$page_name?offset=$back'>Previous</a>";}

Part II : Center navigation to all sub-pages

$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

if($next < $nume) {
print "<a href='$page_name?offset=$next'>Next</a>";}

<?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

Using different source of database

Order of the record display

$sql="SELECT * FROM student ORDER BY mark DESC LIMIT :offset, :limit ";

$sql="SELECT * FROM student ORDER BY mark ASC LIMIT :offset, :limit ";

$sql="SELECT * FROM student ORDER BY mark LIMIT :offset, :limit ";

Using WHERE

$sql="SELECT * FROM student WHERE class='Three' ORDER BY mark ASC LIMIT :offset, :limit ";

$nume=$my_conn->querySingle("SELECT count(*) FROM student WHERE class='Three'");

Sample script using PHP , SQLite3 connection and SQLite database : plus2net_sqlite3_v1

Questions