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.