Page Navigation Using PDO Query in PHP/MySQL

Submitted by argie on Thursday, November 21, 2013 - 12:38.
This tutorial will teach you on how to create a content pager using PHP/MySQL. I uses PDO query to retrieve the data from database table. This tutorial is useful when you have multiple data from the database. This code divide the content and display by page. To start this tutorial fallow the steps bellow:

Creating Our Database

First we are going to create our database which stores our data. To create a database: 1. Open phpmyadmin 2. Then create database and name it as "pdo_ret". 3. After creating a database name, click the SQL and paste the following code.
  1. CREATE TABLE IF NOT EXISTS `members` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `fname` varchar(100) NOT NULL,
  4.   `lname` varchar(100) NOT NULL,
  5.   `age` int(5) NOT NULL,
  6.   PRIMARY KEY (`id`)
  7. ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

Creating our Database Connection

Next step is to create a database connection and save it as "connect.php". In this Step, we will write our connection script in PDO format.
  1. <?php
  2. /* Database config */
  3. $db_host                = 'localhost';
  4. $db_user                = 'root';
  5. $db_pass                = '';
  6. $db_database    = 'pdo_ret';
  7.  
  8. /* End config */
  9.  
  10. $db = new PDO('mysql:host='.$db_host.';dbname='.$db_database, $db_user, $db_pass);
  11. $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  12.  
  13. ?>

Creting our Display Page

In this steps we will write our script that retrieve our data from database table and display the data by page, we will do that by using the PDO query. Save this page as "index.php".

Create a table that display the data

Copy the code bellow and paste on your index.php file
  1. <table cellspacing="0" cellpadding="2" >
  2. <thead>
  3.         <tr>
  4.                 <th> First Name </th>
  5.                 <th> Last Name </th>
  6.                 <th> Age </th>
  7.         </tr>
  8. </thead>
  9. <tbody>
  10.         <?php
  11.                 include('connect.php');
  12.                 if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; };
  13.                 $start_from = ($page-1) * 3;           
  14.                 $result = $db->prepare("SELECT * FROM members ORDER BY id ASC LIMIT $start_from, 3");
  15.                 $result->execute();
  16.                 for($i=0; $row = $result->fetch(); $i++){
  17.         ?>
  18.         <tr class="record">
  19.                 <td><?php echo $row['fname']; ?></td>
  20.                 <td><?php echo $row['lname']; ?></td>
  21.                 <td><?php echo $row['age']; ?></td>
  22.         </tr>
  23.         <?php
  24.                 }
  25.         ?>
  26. </tbody>
  27. </table>

Write the script that divide the content and display by page

Copy the code bellow and paste under the closing tag of your table.
  1. <div id="pagination">
  2.         <?php
  3.  
  4.         $result = $db->prepare("SELECT COUNT(id) FROM members");
  5.         $result->execute();
  6.         $row = $result->fetch();
  7.         $total_records = $row[0];
  8.         $total_pages = ceil($total_records / 3);
  9.          
  10.         for ($i=1; $i<=$total_pages; $i++) {
  11.                                 echo "<a href='index.php?page=".$i."'";
  12.                                 if($page==$i)
  13.                                 {
  14.                                 echo "id=active";
  15.                                 }
  16.                                 echo ">";
  17.                                 echo "".$i."</a> ";
  18.         };
  19.         ?>
  20. </div>

Create Our Style

this part of code will make our display more organize and beautifull to look at. paste this before the opening tag of the table.
  1. <style>
  2. table {
  3.     border-collapse: separate;
  4.         background-color: #FFFFFF;
  5.    border-spacing: 0;
  6.     width: 50%;
  7.         color: #666666;
  8.    text-shadow: 0 1px 0 #FFFFFF;
  9.         border: 1px solid #CCCCCC;
  10.         box-shadow: 0 5px 5px -5px rgba(0, 0, 0, 0.3);
  11.         margin: 0 auto;
  12.         font-family: arial;
  13.         margin-top: 20px;
  14. }
  15. table thead tr th {
  16.     background: none repeat scroll 0 0 #EEEEEE;
  17.    color: #222222;
  18.    padding: 10px 14px;
  19.     text-align: left;
  20.         border-top: 0 none;
  21.         font-size: 12px;
  22. }
  23. table tbody tr td{
  24.     background-color: #FFFFFF;
  25.         font-size: 11px;
  26.     text-align: left;
  27.         padding: 10px 14px;
  28.         border-top: 1px solid #DDDDDD;
  29. }
  30. #pagination {
  31.         text-align: center;
  32.         margin-top: 20px;
  33. }
  34. #pagination a {
  35.         border: 1px solid #CCCCCC;
  36.         padding: 5px 10px;
  37.         font-family: arial;
  38.         text-decoration: none;
  39.         background: none repeat scroll 0 0 #EEEEEE;
  40.         color: #222222;
  41. }
  42. #pagination a:hover {
  43.         background-color: #FFFFFF;
  44. }
  45. a#active{
  46.         background-color: #FFFFFF;
  47. }
  48. </style>

Summary of display page

the display page will look like the code bellow.
  1. <style>
  2. table {
  3.     border-collapse: separate;
  4.         background-color: #FFFFFF;
  5.     border-spacing: 0;
  6.     width: 50%;
  7.         color: #666666;
  8.     text-shadow: 0 1px 0 #FFFFFF;
  9.         border: 1px solid #CCCCCC;
  10.         box-shadow: 0 5px 5px -5px rgba(0, 0, 0, 0.3);
  11.         margin: 0 auto;
  12.         font-family: arial;
  13.         margin-top: 20px;
  14. }
  15. table thead tr th {
  16.     background: none repeat scroll 0 0 #EEEEEE;
  17.     color: #222222;
  18.     padding: 10px 14px;
  19.     text-align: left;
  20.         border-top: 0 none;
  21.         font-size: 12px;
  22. }
  23. table tbody tr td{
  24.     background-color: #FFFFFF;
  25.         font-size: 11px;
  26.     text-align: left;
  27.         padding: 10px 14px;
  28.         border-top: 1px solid #DDDDDD;
  29. }
  30. #pagination {
  31.         text-align: center;
  32.         margin-top: 20px;
  33. }
  34. #pagination a {
  35.         border: 1px solid #CCCCCC;
  36.         padding: 5px 10px;
  37.         font-family: arial;
  38.         text-decoration: none;
  39.         background: none repeat scroll 0 0 #EEEEEE;
  40.         color: #222222;
  41. }
  42. #pagination a:hover {
  43.         background-color: #FFFFFF;
  44. }
  45. a#active{
  46.         background-color: #FFFFFF;
  47. }
  48. </style>
  49.  
  50. <table cellspacing="0" cellpadding="2" >
  51. <thead>
  52.         <tr>
  53.                 <th> First Name </th>
  54.                 <th> Last Name </th>
  55.                 <th> Age </th>
  56.         </tr>
  57. </thead>
  58. <tbody>
  59.         <?php
  60.                 include('connect.php');
  61.                 if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; };
  62.                 $start_from = ($page-1) * 3;           
  63.                 $result = $db->prepare("SELECT * FROM members ORDER BY id ASC LIMIT $start_from, 3");
  64.                 $result->execute();
  65.                 for($i=0; $row = $result->fetch(); $i++){
  66.         ?>
  67.         <tr class="record">
  68.                 <td><?php echo $row['fname']; ?></td>
  69.                 <td><?php echo $row['lname']; ?></td>
  70.                 <td><?php echo $row['age']; ?></td>
  71.         </tr>
  72.         <?php
  73.                 }
  74.         ?>
  75. </tbody>
  76. </table>
  77. <div id="pagination">
  78.         <?php
  79.  
  80.         $result = $db->prepare("SELECT COUNT(id) FROM members");
  81.         $result->execute();
  82.         $row = $result->fetch();
  83.         $total_records = $row[0];
  84.         $total_pages = ceil($total_records / 3);
  85.          
  86.         for ($i=1; $i<=$total_pages; $i++) {
  87.                                 echo "<a href='index.php?page=".$i."'";
  88.                                 if($page==$i)
  89.                                 {
  90.                                 echo "id=active";
  91.                                 }
  92.                                 echo ">";
  93.                                 echo "".$i."</a> ";
  94.         };
  95.         ?>
  96. </div>
That's it, you've been successfully created a PHP content pager using PDO Query, thank you.
Tags
Content Pager
PDO
PDO query
PHP/MySQL
Content Pager Using PHP/MySQL

Comments

Submitted by michael greiner (not verified) on Tue, 04/14/2015 - 02:04

First off, great tutorial. I was just curious as whether there is a way to add a text search?

Submitted by Horst Hamumpel (not verified) on Sun, 03/20/2016 - 17:57

You safe a part of my own Project. Your Tutorial was very helpful and easy to understand.

Submitted by Hansi (not verified) on Sun, 11/13/2016 - 20:20

Code doesn't work - keep searching for a useable code.

Add new comment

About text formats