How to Create Pagination with PHP, PDO using OOP | Coding Cage

How to Create Pagination with PHP, PDO using OOP

By
In this tutorial we will see that how to create Pagination with PHP, PDO using OOP concept, when we have number of records in database then we must Paginate the data, this is very Simple tutorial using PDO and OOP without using Ajax or jQuery it is based on pure PHP, i have created one class file using that class file you can make Pagination of database records and this class file featured with next and previous links based on the page.
How to create Pagination with PHP, PDO using OOP

Create Database & table

create a database named "dbpaging" and import the following sql code in phpmyadmin.

CREATE TABLE `dbpaging`.`tbl_tutorials` (
`tuts_id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`tuts_title` VARCHAR( 100 ) NOT NULL ,
`tuts_link` VARCHAR( 200 ) NOT NULL 
) ENGINE = MYISAM ;

now insert some records in the table paste the following code

INSERT INTO `dbpaging`.`tbl_tutorials` (`tuts_id`, `tuts_title`, `tuts_link`) VALUES (NULL, 'Dynamic Drop Down Menu using PHP & MySQLi', 'http://www.codingcage.com/2015/03/dynamic-drop-down-menu-using-php-and.html'), (NULL, 'How to use PHP Data Object - PDO tutorial', 'http://www.codingcage.com/2015/03/how-to-use-php-data-object-pdo-tutorial.html');
INSERT INTO `dbpaging`.`tbl_tutorials` (`tuts_id`, `tuts_title`, `tuts_link`) VALUES (NULL, 'PHP CRUD tutorial with MySQLi extension', 'http://www.codingcage.com/2015/03/php-crud-tutorial-with-mysqli-extension.html'), (NULL, 'Drop Down Menu using CSS3 & jQuery', 'http://www.codingcage.com/2015/02/how-to-create-drop-down-menu-using-css3.html');
INSERT INTO `dbpaging`.`tbl_tutorials` (`tuts_id`, `tuts_title`, `tuts_link`) VALUES (NULL, 'Server Side Form Valiations', 'http://www.codingcage.com/2015/02/server-side-form-validations-using-php.html'), (NULL, 'Registration & Login Script with PHP MySQl', 'http://www.codingcage.com/2015/01/user-registration-and-login-script-using-php-mysql.html');
INSERT INTO `dbpaging`.`tbl_tutorials` (`tuts_id`, `tuts_title`, `tuts_link`) VALUES (NULL, 'PHP CRUD with MySQL Part - 2', 'http://www.codingcage.com/2015/01/php-oop-crud-tutorial-with-mysql-part-2.html'), (NULL, 'Delete uploaded files from Folder', 'http://www.codingcage.com/2014/12/delete-uploaded-files-from-folder-in-php.html');
INSERT INTO `dbpaging`.`tbl_tutorials` (`tuts_id`, `tuts_title`, `tuts_link`) VALUES (NULL, 'Fetch data From Multiple tables', 'http://www.codingcage.com/2014/12/fetch-data-from-multiple-tables-in-php.html'), (NULL, 'File Upload and View with PHP & MySQl', 'http://www.codingcage.com/2014/12/file-upload-and-view-with-php-and-mysql.html');
INSERT INTO `dbpaging`.`tbl_tutorials` (`tuts_id`, `tuts_title`, `tuts_link`) VALUES (NULL, 'Simple PHP CRUD operations', 'http://www.codingcage.com/2014/12/simple-php-crud-operations-with-mysql.html'), (NULL, 'Validations', 'http://www.codingcage.com/search/label/validations');
INSERT INTO `dbpaging`.`tbl_tutorials` (`tuts_id`, `tuts_title`, `tuts_link`) VALUES (NULL, 'HTML5 Form validations', 'http://www.codingcage.com/2015/03/html5-form-validations-with-pattern.html'), (NULL, 'PHP', 'http://www.codingcage.com/search/label/php');

Create a new file and name it ‘dbconfig.php’ and paste the following code inside it.

dbconfig.php

Set the credentials for the database and make a new PDO connection if the connection fails display the error .
Next include the ‘class.paging.php’ file and make an instance of it, pass in the database object to the class to make use of the database.

<?php

$db_host = "localhost";
$db_user = "root";
$db_pass = "";
$db_name = "dbpaging";

try
{
     $DB_con = new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_pass);
     $DB_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $exception)
{
     echo $exception->getMessage();
}

include_once 'class.paging.php';
$paginate = new paginate($DB_con);

class.paging.php

this is the main class file which paginates the database records.
>> dataview() : function selects the whole records from database table.
>> paging() : function set’s the QueryString like “page_no=number”.
>> paginglink() : function creates the paging number links with “Next and Last” feature.

<?php

class paginate
{
     private $db;
 
     function __construct($DB_con)
     {
         $this->db = $DB_con;
     }
 
     public function dataview($query)
     {
         $stmt = $this->db->prepare($query);
         $stmt->execute();
 
         if($stmt->rowCount()>0)
         {
                while($row=$stmt->fetch(PDO::FETCH_ASSOC))
                {
                   ?>
                   <tr>
                   <td><?php echo $row['tuts_id']; ?></td>
                   <td><?php echo $row['tuts_title']; ?></td>
                   <td><a href="<?php echo $row['tuts_link']; ?>">visit</a></td>
                   </tr>
                   <?php
                }
         }
         else
         {
                ?>
                <tr>
                <td>Nothing here...</td>
                </tr>
                <?php
         }
  
 }
 
 public function paging($query,$records_per_page)
 {
        $starting_position=0;
        if(isset($_GET["page_no"]))
        {
             $starting_position=($_GET["page_no"]-1)*$records_per_page;
        }
        $query2=$query." limit $starting_position,$records_per_page";
        return $query2;
 }
 
 public function paginglink($query,$records_per_page)
 {
  
        $self = $_SERVER['PHP_SELF'];
  
        $stmt = $this->db->prepare($query);
        $stmt->execute();
  
        $total_no_of_records = $stmt->rowCount();
  
        if($total_no_of_records > 0)
        {
            ?><tr><td colspan="3"><?php
            $total_no_of_pages=ceil($total_no_of_records/$records_per_page);
            $current_page=1;
            if(isset($_GET["page_no"]))
            {
               $current_page=$_GET["page_no"];
            }
            if($current_page!=1)
            {
               $previous =$current_page-1;
               echo "<a href='".$self."?page_no=1'>First</a>&nbsp;&nbsp;";
               echo "<a href='".$self."?page_no=".$previous."'>Previous</a>&nbsp;&nbsp;";
            }
            for($i=1;$i<=$total_no_of_pages;$i++)
            {
            if($i==$current_page)
            {
                echo "<strong><a href='".$self."?page_no=".$i."' style='color:red;text-decoration:none'>".$i."</a></strong>&nbsp;&nbsp;";
            }
            else
            {
                echo "<a href='".$self."?page_no=".$i."'>".$i."</a>&nbsp;&nbsp;";
            }
   }
   if($current_page!=$total_no_of_pages)
   {
        $next=$current_page+1;
        echo "<a href='".$self."?page_no=".$next."'>Next</a>&nbsp;&nbsp;";
        echo "<a href='".$self."?page_no=".$total_no_of_pages."'>Last</a>&nbsp;&nbsp;";
   }
   ?></td></tr><?php
  }
 }
}

index.php

this file contains HTML and few lines of PHP code to display Paginated.
include above created ‘dbconfig.php’ in this file to use class files functions data.
you can change records per page number in this file i have set 3 .

<?php
include_once("dbconfig.php");
?>
<link rel="stylesheet" href="style.css" type="text/css" />
<table align="center" width="50%"  border="1">
<tr>
<td><a href="http://cleartuts.blogspot.com/">PHP Pagination with PDO using OOP concept.</a></td>
</tr>
<tr>
<td>

        <table align="center" border="1" width="100%" height="100%" id="data">
        <?php 
        $query = "SELECT * FROM tbl_tutorials";       
        $records_per_page=3;
        $newquery = $paginate->paging($query,$records_per_page);
        $paginate->dataview($newquery);
        $paginate->paginglink($query,$records_per_page);  
        ?>
        </table>
</td>
</tr>
</table>
<div id="footer">
<a href="http://cleartuts.blogspot.com/">cleartuts.blogspot.com</a>
</div>

style.css


@charset "utf-8";
/* CSS Document */

table,td
{
 padding:15px;
 border-collapse:collapse;
}
#data td
{
 padding:10px;
 border-collapse:collapse;
 position:relative;
 font-family:Verdana, Geneva, sans-serif;
}

a
{
 text-decoration:none;
 color:#00a2d1;
 font-family:"Courier New", Courier, monospace;
 font-weight:bold;
 font-family:Tahoma, Geneva, sans-serif;
}
#footer
{
 text-align:center;
 margin-top:50px;
}

that’s it we have created here Simple PHP Pagination with PDO using OOP concept.
Feel free to comment your suggestions regarding this tutorial.



37 comments:

  1. thanks man...
    i was looking for the same...

    ReplyDelete
  2. Great tutorial? Is there any chance that there is a method to implementing a text search feature? thank you!

    ReplyDelete
  3. I'm a bit of a newbie..any instruction on how to implement the select query?

    ReplyDelete
  4. Can you help on how to include variables in the query SELECT
    I will send these variables from another script
    I have tried but it didn't work. Please help.
    Thank you

    ReplyDelete
    Replies
    1. you have to use $_POST[] or $_GET[] variable

      SELECT * FROM tbl_user WHERE user_id='$_GET[var_name]'

      Delete
    2. No. It only displays the first page. After that displays the Nothing here... message.
      Thanks for the tip any way... Can you suggest something else?

      Delete
  5. Thanks for the Script..

    ReplyDelete
  6. Thanks. I have one question, how can I get the table data to display in descending order (as opposed to ascending). I'm trying to get the newest entries from my database to show up first rather than the old entries.

    ReplyDelete
  7. Hi Pradeep, I figured out the answer to my question. In the index file I made this change at line 14:

    $query = "SELECT * FROM tbl_tutorials";

    to

    $query = "SELECT * FROM tbl_tutorials ORDER BY [please insert a column name here!] DESC";

    ReplyDelete
  8. Nice blog...Very useful information is providing by ur blog. Great beginning php tutorials Very clear and helpful for beginners.

    ReplyDelete
  9. Pradeep, I need some help please. I'm trying to use your scrip for upload & view tutorial, and this tutorial problem is I'm not getting it right. I encounter various errors, or data doesn't get uploaded to the database. Can you please do a tutorial that uses both of these tutorials.

    ReplyDelete
    Replies
    1. Ok , I'll make a tutorial about it, soon...

      Delete
  10. Thank you, great tutorial, worked perfectly.

    ReplyDelete
  11. Hi..unable to download script

    Please help.

    ReplyDelete
  12. Great script, very useful, many thanks. If i could ask though, I'm looking try and limit the displayed pagination to 5, and have them scroll on clicking next/back. As opposed to showing all page numbers at once. Don't suppose you have any pointers? Thanks again.

    ReplyDelete
    Replies
    1. yes you can give limit to 5 for display pages with 5 records just replace 3 with 5 in "$records_per_page=3"; variable.

      Delete
    2. Appreciate your quick reply, if I'm not mistaken that would vary the number of results shown on each page? What I'm attempting (and currently failing!!) to do is limit the page numbers being displayed at any given time. So if i have enough results to create 15 pages, only 5 page links would be displayed. Thanks.

      Delete
    3. this is what exactly i'd like to ask too. upvote

      Delete
    4. upvote. hundreds of page numbers... :/
      can we limit before 3 and after tree page number and other ...
      First - Previous - ... - 7 - 8 - 9 - 10 - 11 - 12 - 13 - ... - Next - Last

      Delete
    5. http://www.ubilisim.com/dosyalar/ice_screenshot_20161128-101100.png
      :/
      Not so good.

      Delete
  13. This comment has been removed by the author.

    ReplyDelete
  14. Hi, great script, I have one question.
    when I linked with my customer database, it shows so many numbers with the pagination.
    can you modify that to get like this :

    1 2 3 .....7 8 9 Next > Last >>

    Like put some dots to pagination?

    Thanks!

    ReplyDelete
  15. tanx for this tutorial, bt i can i get script to paginate dynamicallly generated page

    ReplyDelete
  16. Awesome and Elegant script i was looking for thanks a lot... What if i want to align pagination to center of table how to achieve it..?

    ReplyDelete
  17. error on page in dbcongig.
    it shows
    warning: include_once(class.paging.php): failed to open stream: No such file or directory in F:\cse\training\php\wamp64\www\suffi\pagination\dbconfig.php on line 18

    ReplyDelete
  18. very easy i dont understand nothing

    ReplyDelete
  19. i have error
    Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '3,3' at line 1 in /opt/lampp/htdocs/Apgredz/class/class_paging.php:9 Stack trace: #0 /opt/lampp/htdocs/Apgredz/class/class_paging.php(9): PDOStatement->execute() #1 /opt/lampp/htdocs/Apgredz/pagination.php(17): paginate->dataView('SELECT * FROM p...') #2 {main} thrown in /opt/lampp/htdocs/Apgredz/class/class_paging.php on line 9

    ReplyDelete
  20. hello sir. plsss add a script search with pagination...

    ReplyDelete
  21. Thanks for your great tutorial that helped me a lot.It is really helpful.This tutorial provide very useful information.
    Web Design Victoria

    ReplyDelete
  22. thank you dear friend
    My problem was resolved

    ReplyDelete
  23. Box.com link not working. can you upload again.

    ReplyDelete
  24. THANKS AM USING THIS SCRIPT FOR A PROJECT. (I EDITED IT ).

    ReplyDelete
  25. Why do you use HTML and echo's in your functions?

    ReplyDelete
  26. Thanks! Amazing code. I using with search field in my project.

    ReplyDelete