Welcome to TechYari!

Tutorials on webdesign, web development and more...

Pagination in PHP with MySql database

By  TechYari     10/14/2014    Labels:,, 
In this tutorial I am going to create a simple pagination script in PHP. This tutorial does not require any use of JavaScript or jQuery. It is based on pure PHP. I have used MySql as database but you can use any database that you like. I am writing this tutorial for PHP  as it is  used by more then 75 % of the websites. I have used twitter bootstrap for look and feel and therefor have added jQuery, you may use anything that suits you.

The pagination script will have following features :
  • Numbered links based on total messages in database table.
  • Next and Previous links based on the page.

Pgination in PHP with MySql database

Database Table Setup (table-setup.php)

Below is code to setup a table with two columns id & message. Create a database in MySql and run query given below to setup messages table. Now you need to populate this table with some data at least 20 rows so that we can get a nice feel of pagination.

CREATE TABLE messages(
   message VARCHAR(100) NOT NULL,

Database Connection Setup (database-config.php)

To connect to database via php use below code. Change variable values according to your database environment.

   // define database related variables
   $database = 'database_name_here';
   $host = 'localhost';
   $user = 'root';
   $pass = '';

   // try to conncet to database
   $dbh = new PDO("mysql:dbname={$database};host={$host};port={3306}", $user, $pass);


      echo "unable to connect to database";

HTML and PHP Code Page to display paginated data (index.php)

Below is the part of HTML/PHP code which renders data from database with pagination using php. This page requires php code to get data from database ( data.php ), and data.php requires database-config.php to connect to database. 

<!-- Display messages from database-->
      <div class="col-md-10 col-md-offset-2 content">
                <table class="table table-striped">
                      for ($i=0; $i<count($result); $i++) { 
        <!-- Render pagination based on messages-->
      <div class="pagination-wrap">
                <ul class="pagination">

                    if($page_counter == 0){
                        echo "<li><a href=?start='0' class='active'>0</a></li>";
                       for ($j=1; $j < $paginations; $j++) { 
                          echo "<li><a href=?start=$j>".$j."</a></li>";
                        echo "<li><a href=?start=$previous>Previous</a></li>"; 
                        for ($j=0; $j < $paginations; $j++) {
                         if ($j == $page_counter) {
                            echo "<li><a href=?start=$j class='active'>".$j."</a></li>";
                            echo "<li><a href=?start=$j>".$j."</a></li>";
                      }if($j != $page_counter+1)
                        echo "<li><a href=?start=$next>Next</a></li>"; 

PHP code to retrieve data using pagination links (data.php)

 require 'database-config.php';

    // Variables for the first page hit
 $start = 0;
 $page_counter = 0;
    $per_page = 5;
    $next = $page_counter + 1;
    $previous = $page_counter - 1;

    // Check the page location with start value sent by get request and change variable values accordingly
  $start = $_GET['start'];
  $page_counter =  $_GET['start'];
  $start = $start *  $per_page;
  $next = $page_counter + 1;
  $previous = $page_counter - 1;

    // query to get messages from messages table
 $q = "SELECT id,message FROM messages LIMIT $start, $per_page";

 $query = $dbh->prepare($q);

    //placeholder variable to store result
 $result = null;

 if($query->rowCount() == 0){

  while($row = $query->fetch(PDO::FETCH_ASSOC)){
       // store each row in result variable 
    $result[] = $row['id']." ".$row['message'];

    // query to get total number of rows in messages table
 $count_query = "SELECT * FROM messages";

 $query = $dbh->prepare($count_query);


 $count = $num_rows = $query->rowCount();
    // calculate number of paginations required based on row count 
 $paginations = ceil($count / $per_page);


A bit of styling for pagination links using css (style.css)

.info,.pagination-wrap{text-align: center;}
h2{padding: 10px;}
.pagination li{background: #428BCA;
color: white;}

If you run into any issues you can download the source file from provide link and go through the code. Do not forget to comment if you like this tutorial or have any issues. Happy to help cheers :)

About TechYari

A web developer, blogger, amateur designer and an open source enthusiast.

No comments:

Post a comment

Contact form


Email *

Message *


Follow TechYari on


About TechYari

Tech Yari is a Technical Blog featuring articles on Web Development, Web Design, Search Engine Optimization, Social Media Marketting and Programming.