Implementing Pagination in SQLite Using PDO and SQLite3 in PHP

Part I : Using Basic Queries in SQLite with PDO 🔝

Explanation of Query String Handling 🔝

@$start=$_GET['start'];// Collect data from Query string
if(strlen($start) ==0){$start=0;} // if blank then set to 0 
if(strlen($start) > 0 and !is_numeric($start)){
echo "Data Error";
exit;
}

• Collecting Query Parameter: `@$start=$_GET['start'];` collects the `start` parameter from the query string in the URL. The `@` symbol is used to suppress any errors in case the parameter is not set.
• Setting Default Value: `if(strlen($start) == 0){$start=0;}` checks if the `start` value is empty. If so, it sets `$start` to `0` to ensure a default starting point for pagination.
• Data Validation: `if(strlen($start) > 0 and !is_numeric($start))` checks if the `start` value is not numeric, displaying a "Data Error" message and stopping further execution using `exit;` to prevent invalid data from being processed.

Settings of Pagination script 🔝

$steps = 10; // No of records to be shown per page.
$next = $start + $steps; // Starting number of record for Next page
$back = $start - $steps; // Starting number of record for Previous page
$page_name="display-paging.php"; // Pange name for linking 

• Records per Page: `$steps = 10;` defines the number of records to display per page (10 in this case).
• Next Page Calculation: `$next = $start + $steps;` calculates the starting record number for the next page by adding the current start value to the number of records per page.
• Previous Page Calculation: `$back = $start - $steps;` calculates the starting record number for the previous page by subtracting the steps from the current start value.
• Page Link Setup: `$page_name="display-paging.php";` sets the page URL used in pagination links.

Data Fetching and Display 🔝

$query=" SELECT * FROM student  LIMIT $start, $steps ";
echo "<table align=center width='50%'>";
foreach ($my_conn->query($query) as $row) {
 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>";

• SQL Query with Pagination: `$query=" SELECT * FROM student LIMIT $start, $steps ";` retrieves student records from the database, limited by the starting point (`$start`) and the number of records per page (`$steps`). This implements pagination.
• Generating Table: The `foreach` loop iterates through the result set and dynamically generates an HTML table with student details like `id`, `name`, `class`, `mark`, and `gender`.
• Displaying Data: Each row of the student table is displayed within the table tags, using HTML `<tr>` and `<td>` elements.

Navigation Links and Total Record Count 🔝

$nume = $my_conn->query("SELECT COUNT(id) FROM student")->fetchColumn();// Total number of records

// Left side navigational link 
echo "<table align=center width='50%'><tr><td align='left' width='30%'>";
if($back >=0) {
print "<a href='$page_name?start=$back'>PREVIOUS</a>";
}

// Center navigational link

echo "</td><td align=center width='30%'>";
$i=0;
$l=1;
for($i=0;$i < $nume;$i=$i+$steps){
if($i <>$start){
echo " <a href='$page_name?start=$i'><font size='6'>$l</font></a> ";
}
else { /// Current page is not displayed as link and given font color red
  echo "<font size='8' color=red>$l</font>";
  } 
$l=$l+1;
}

// Right side navigational link 
echo "</td><td align='right' width='30%'>";
if($next < $nume) {
print "<a href='$page_name?start=$next'>NEXT</a>";}
echo "</td></tr></table>";

• Total Record Count: `$nume = $my_conn->query("SELECT COUNT(id) FROM student")->fetchColumn();` retrieves the total number of student records to determine the number of pagination links needed.
• Previous Link: If `$back` is greater than or equal to 0, the "Previous" link is displayed to navigate to the previous set of records.
• Center Navigation: A loop creates numbered page links. The current page is highlighted in red without being clickable.
• Next Link: If there are more records to show, the "Next" link is displayed for forward navigation.

$my_conn = new PDO('sqlite:my_db.db');

@$start=$_GET['start'];// Collect data from Query string
if(strlen($start) ==0){$start=0;} // if blank then set to 0 
if(strlen($start) > 0 and !is_numeric($start)){
echo "Data Error";
exit;
}

$steps = 10; // No of records to be shown per page.
$next = $start + $steps; // Starting number of record for Next page
$back = $start - $steps; // Starting number of record for Previous page
$page_name="display-paging.php"; // Pange name for linking 

$query=" SELECT * FROM student  LIMIT $start, $steps ";
echo "<table align=center width='50%'>";
foreach ($my_conn->query($query) as $row) {
 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>";

// Rows display is over 
// Bottom navigation links to different pages part starts 

//$nume=$my_conn->querySingle("SELECT count(*) FROM student "); // For SQlite3 
$nume = $my_conn->query("SELECT COUNT(id) FROM student")->fetchColumn();// Total number of records

// Left side navigational link 
echo "<table align=center width='50%'><tr><td align='left' width='30%'>";
if($back >=0) {
print "<a href='$page_name?start=$back'>PREVIOUS</a>";
}

// Center navigational link

echo "</td><td align=center width='30%'>";
$i=0;
$l=1;
for($i=0;$i < $nume;$i=$i+$steps){
if($i <>$start){
echo " <a href='$page_name?start=$i'><font size='6'>$l</font></a> ";
}
else { /// Current page is not displayed as link and given font color red
  echo "<font size='8' color=red>$l</font>";
  } 
$l=$l+1;
}

// Right side navigational link 
echo "</td><td align='right' width='30%'>";
if($next < $nume) {
print "<a href='$page_name?start=$next'>NEXT</a>";}
echo "</td></tr></table>";

$my_conn = null;

Part II : Using Parameterized Query for Data Fetching 🔝

$query="SELECT * FROM student LIMIT :start, :steps ";//query with place holders 
$stmt = $my_conn->prepare($query); 
$stmt->bindParam(':start', $start,PDO::PARAM_INT);
$stmt->bindParam(':steps', $steps,PDO::PARAM_INT);
$result=$stmt->execute();		

echo "<br><center><table align=center width='50%'>";
//while ($row=$result->fetchArray()) { // for sqlite3 
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {	
 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><br><br>";

• Parameterized Query: The query `"SELECT * FROM student LIMIT :start, :steps "` uses placeholders `:start` and `:steps` to safely insert values for pagination. This prevents SQL injection.
• Binding Parameters: `bindParam(':start', $start, PDO::PARAM_INT, 3)` and `bindParam(':steps', $steps, PDO::PARAM_INT, 3)` bind the actual values of `$start` and `$steps` to the placeholders, ensuring they are treated as integers.
• Executing Query: The prepared statement is executed with `$stmt->execute()` and the data is fetched using `PDO::FETCH_ASSOC`, displaying each student's details inside an HTML table.

$my_conn = new PDO('sqlite:my_db.db');
@$start=$_GET['start'];// Collect data from Query string
if(strlen($start) ==0){$start=0;}

$steps = 10; // No of records to be shown per page.
$next = $start + $steps; // Starting number of record for Next page
$back = $start - $steps; // Starting number of record for Previous page
$page_name="display-paging.php"; // Pange name for linking 

$query="SELECT * FROM student LIMIT :start, :steps ";//query with place holders 
$stmt = $my_conn->prepare($query); 
$stmt->bindParam(':start', $start,PDO::PARAM_INT);
$stmt->bindParam(':steps', $steps,PDO::PARAM_INT);
$result=$stmt->execute();		

echo "<br><center><table align=center width='50%'>";
//while ($row=$result->fetchArray()) { // for sqlite3 
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {	
 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><br><br>";

// Total number of records
//$nume=$my_conn->querySingle("SELECT count(*) FROM student "); // For SQlite3 
$nume = $my_conn->query("SELECT COUNT(id) FROM student")->fetchColumn();

// Rows display is over 
// Bottom navigation links to different pages part starts 

// Left side navigational link 
echo "<table align=center width='50%'><tr><td align='left' width='30%'>";
if($back >=0) {
print "<a href='$page_name?start=$back'>PREVIOUS</a>";
}

// Center navigational link

echo "</td><td align=center width='30%'>";
$i=0;
$l=1;
for($i=0;$i < $nume;$i=$i+$steps){
if($i <>$start){
echo " <a href='$page_name?start=$i'><font size='6'>$l</font></a> ";
}
else { /// Current page is not displayed as link and given font color red
  echo "<font size='8' color=red>$l</font>";
  } 
$l=$l+1;
}

// Right side navigational link 
echo "</td><td align='right' width='30%'>";
if($next < $nume) {
print "<a href='$page_name?start=$next'>NEXT</a>";}
echo "</td></tr></table>";

Using sqlite3 driver 🔝

$my_conn = new SQLite3('my_db.db');// sqlite3 Connect to Database
//$my_conn = new PDO('sqlite:my_db.db'); // using PDO

$query="SELECT * FROM student LIMIT :start, :steps "; // query with placeholders
$stmt = $my_conn->prepare($query);

// Using sqlite3_bind_int() for SQLite3 binding
$stmt->bindValue(':start', $start, SQLITE3_INTEGER);
$stmt->bindValue(':steps', $steps, SQLITE3_INTEGER);

$result = $stmt->execute();		

echo "<br><center><table align=center width='50%'>";

while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    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><br><br>";

$nume=$my_conn->querySingle("SELECT count(*) FROM student "); // For SQlite3 
//$nume = $my_conn->query("SELECT COUNT(id) FROM student")->fetchColumn(); // PDO 

Basic Differences Between PDO and SQLite3 Drivers 🔝

• API:
  • PDO: A unified API that supports multiple databases (MySQL, SQLite, PostgreSQL, etc.), allowing easy database switching.
  • SQLite3: Specific to SQLite, designed only for SQLite databases.
• Parameter Binding:
  • PDO: Uses bindParam() and bindValue() for parameter binding, compatible across different databases.
  • SQLite3: Uses bindValue() with SQLite-specific constants like SQLITE3_INTEGER.
• Error Handling:
  • PDO: Offers advanced error handling modes such as ERRMODE_EXCEPTION.
  • SQLite3: Limited error handling compared to PDO.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: