PHP and SQLite Table Select and Pagination

.

PHP and SQLite Table Select and Pagination

The previous tutorial, http://php-steps.blogspot.my/2016/02/basic-php-and-sqlite-table-drop-create.html , demonstrated the basic Select statement. As the number of rows grows, the select operation may consume more time and resources. It is more efficient to select and display few records instead of the entire table. This tutorial demonstrates pagination concept

1) Starting Up

Continue from the previous tutorial, http://php-steps.blogspot.my/2016/02/basic-php-and-sqlite-table-drop-create.html 

2) Create PHP Script

Create a PHP Script to select records with limit parameters.

File: displaybuddies.php

<?php function getDB() {         $dbConnection = new PDO("sqlite:buddies.sdb");         $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);         return $dbConnection; } try {         $DB=getDB();         // page is the current page, if there's nothing set, default is page 1         $page = isset($_GET['page']) ? $_GET['page'] : 1;         // set records or rows of data per page         $recordsPerPage = 5;         // calculate for the query LIMIT clause         $fromRecordNum = ($recordsPerPage * $page) - $recordsPerPage;         // select all data         $query = "SELECT id, name, email, photo FROM tblbuddies ORDER BY id desc         LIMIT {$fromRecordNum}, {$recordsPerPage}";                           /*             page and its LIMIT clause looks like:             1 = 0, 5             2 = 5,10             3 = 10,15             4 = 15, 20             5 = 20, 25             */         $stmt = $DB->prepare( $query );         $stmt->execute();         $result = $stmt->fetchAll();         if(count($result) > 0) {             //start table             echo "<table id='tfhover' class='tftable' border='1'>";                   //creating our table heading         echo "<tr>";                 echo "<th>Id</th>";                 echo "<th>Name</th>";                 echo "<th>Email</th>";                 echo "<th>Photo</th>";         echo "</tr>";         foreach ($result as $row) {                 extract($row);                           //creating new table row per record             echo "<tr>";                 echo "<td>{$id}</td>";                 echo "<td>{$name}</td>";                 echo "<td>{$email}</td>";                 echo "<td>&nbsp;&nbsp;<img src='{$photo}' width='10px'/></td>";             echo "</tr>";                 }             echo "</table>";//end table                 } else {                  echo "Sorry, no results found";         } // close the database connection $DB = NULL;         }         catch(PDOException $e){     echo 'Exception : '.$e->getMessage(); } ?>

For simplicity, this tutorial writes all codes in one script file. In practice however, we would put database connection details in a separate script file and use a require call to the file. (require will produce a fatal error (E_COMPILE_ERROR) and stop the script if the database connection details is missing, read further here) The select statement will get all the records and sort the records in descending order based on id fields. The results is limited to the latest five (5) records only based on the formula $page = isset($_GET['page']) ? $_GET['page'] : 1; and $fromRecordNum = ($recordsPerPage * $page) - $recordsPerPage;

OUTCOME.

3) Add Navigation Links

Since not all records are displayed at a time, there should be a way for users to navigate back and forth through the table records. For this purpose, PAGING SECTION provides codes for the navigation.

<?php function getDB() {         $dbConnection = new PDO("sqlite:buddies.sdb");         $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);         return $dbConnection; } try {         $DB=getDB();         // page is the current page, if there's nothing set, default is page 1         $page = isset($_GET['page']) ? $_GET['page'] : 1;         // set records or rows of data per page         $recordsPerPage = 5;         // calculate for the query LIMIT clause         $fromRecordNum = ($recordsPerPage * $page) - $recordsPerPage;         // select all data         $query = "SELECT id, name, email, photo FROM tblbuddies ORDER BY id desc         LIMIT {$fromRecordNum}, {$recordsPerPage}";                           /*             page and its LIMIT clause looks like:             1 = 0, 5             2 = 5,10             3 = 10,15             4 = 15, 20             5 = 20, 25             */         $stmt = $DB->prepare( $query );         $stmt->execute();         $result = $stmt->fetchAll();         if(count($result) > 0) {     // *************** <PAGING_SECTION> ***************     echo "<div id='paging'>";         // ***** for 'first' and 'previous' pages         if($page>1){             // ********** show the first page             echo "<a href='" . $_SERVER['PHP_SELF'] . "' title='Go to the first page.' class='customBtn'>";                 echo "<span style='margin:0 .5em;'> << </span>";             echo "</a>";                           // ********** show the previous page             $prev_page = $page - 1;             echo "<a href='" . $_SERVER['PHP_SELF']                     . "?page={$prev_page}' title='Previous page is {$prev_page}.' class='customBtn'>";                 echo "<span style='margin:0 .5em;'> < </span>";             echo "</a>";                       }                             // ********** show the number paging         // find out total pages         $query = "SELECT COUNT(*) as total_rows FROM tblbuddies";         $stmt = $DB->prepare( $query );         $stmt->execute();         $row = $stmt->fetch(PDO::FETCH_ASSOC);         $total_rows = $row['total_rows'];         $total_pages = ceil($total_rows / $recordsPerPage);         // range of num links to show         $range = 2;         // display links to 'range of pages' around 'current page'         $initial_num = $page - $range;         $condition_limit_num = ($page + $range)  + 1;         for ($x=$initial_num; $x<$condition_limit_num; $x++) {                           // be sure '$x is greater than 0' AND 'less than or equal to the $total_pages'             if (($x > 0) && ($x <= $total_pages)) {                               // current page                 if ($x == $page) {                     echo "<span class='customBtn' style='background:red;'>$x</span>";                 }                                   // not current page                 else {                     echo " <a href='{$_SERVER['PHP_SELF']}?page=$x' class='customBtn'>$x</a> ";                 }             }         }                             // ***** for 'next' and 'last' pages         if($page<$total_pages){             // ********** show the next page             $next_page = $page + 1;             echo "<a href='" . $_SERVER['PHP_SELF'] . "?page={$next_page}' title='Next page is {$next_page}.' class='customBtn'>";                 echo "<span style='margin:0 .5em;'> > </span>";             echo "</a>";                           // ********** show the last page             echo "<a href='" . $_SERVER['PHP_SELF'] . "?page={$total_pages}' title='Last page is {$total_pages}.' class='customBtn'>";                 echo "<span style='margin:0 .5em;'> >> </span>";             echo "</a>";         }               echo "</div>";           // ***** allow user to enter page number     echo "<form action='" . $_SERVER['PHP_SELF'] . "' method='GET'>";         echo "Go to page: ";         echo "<input type='text' name='page' size='1' />";         echo "<input type='submit' value='Go' class='customBtn' />";     echo "</form>";           // *************** </PAGING_SECTION> ***************                              //start table             echo "<table id='tfhover' class='tftable' border='1'>";                   //creating our table heading         echo "<tr>";                 echo "<th>Id</th>";                 echo "<th>Name</th>";                 echo "<th>Email</th>";                 echo "<th>Photo</th>";         echo "</tr>";         foreach ($result as $row) {                 extract($row);                           //creating new table row per record             echo "<tr>";                 echo "<td>{$id}</td>";                 echo "<td>{$name}</td>";                 echo "<td>{$email}</td>";                 echo "<td>&nbsp;&nbsp;<img src='{$photo}' width='10px'/></td>";             echo "</tr>";                 }         } else {                  echo "Sorry, no results found";         }         echo "</table>";//end table // close the database connection $DB = NULL;         }         catch(PDOException $e){     echo 'Exception : '.$e->getMessage(); } ?>

OUTCOME.

REFERENCES

The codes for this tutorial were taken from the original website https://www.codeofaninja.com/2013/06/simple-php-pagination-script.html 

The row counting techniques were using a different approach. Refer http://stackoverflow.com/questions/25034652/php-pdo-rowcount-return-wrong-result 

.