Simple Pagination With PHP & MYSQL

Data Pagination is one of the comman task we have in web applications. When we get huge data form database insted of show all of it at once we will choose different ways to display that data. One such technique is php pagination.
In this tutorial we will use php and mysql to create simple pagination script.Pagination  lets you to paging your data in to number of pages. I simplifed code as much as possible. In the below example I have taken Country database with its country code. Please check on below link to check the demo. The countries database is included in countries.sql in the download file.

DEMO DOWNLOAD

We will create these pages. -config.php (Database Configuration) -index.php -paginate.php Let's First create database table which we are going to use in our example. 

DataBase

CREATE TABLE IF NOT EXISTS `countries` (
  `ccode` varchar(2) NOT NULL DEFAULT '',
  `country` varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY (`ccode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The above is simple countries table. Just execute the above command in your MySQL command prompt orphpmyadmin area.

config.php

$mysql_hostname = "host"; 
$mysql_user = "usr";            
$mysql_password = "pass";           
$mysql_database = "db"; 
 
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Opps some thing went wrong");
mysql_select_db($mysql_database, $bd) or die("Error on database connection");

This is the configuration file. Here you need to setup your database details. 

index.php

<?php
include('config.php');    //include of db config file
include ('paginate.php'); //include of paginat page
$per_page = 5;         // number of results to show per page
$result = mysql_query("SELECT * FROM countries");
$total_results = mysql_num_rows($result);
$total_pages = ceil($total_results / $per_page);//total pages we going to have
//-------------if page is setcheck------------------//
if (isset($_GET['page'])) {
    $show_page = $_GET['page']; //current page
    if ($show_page > 0 && $show_page <= $total_pages) {
        $start = ($show_page - 1) * $per_page;
        $end = $start + $per_page;
    } else {
        // error - show first set of results
        $start = 0;              
        $end = $per_page;
    }
} else {
    // if page isn't set, show first set of results
    $start = 0;
    $end = $per_page;
}
// display pagination
$page = intval($_GET['page']);
$tpages=$total_pages;
if ($page <= 0)
    $page = 1;
 
?>

Here we included two files config.php and paginate.php in first two lines. we can set $per_page to how many records you want to show on a page. We can calculate total pages depending on total results and per_page. Depending on the $_GET['page'] we can have start and end records. 

index.php(Displaying Data)

<?php
    $reload = $_SERVER['PHP_SELF'] . "?tpages=" . $tpages;
    echo '<div class="pagination"><ul>';
    if ($total_pages > 1) {
        echo paginate($reload, $show_page, $total_pages);
    }
    echo "</ul></div>";
    // display data in table
    echo "<table class='table table-bordered'>";
    echo "<thead><tr><th>country code</th> <th>Country Name</th></tr></thead>";
    // loop through results of database query, displaying them in the table 
    for ($i = $start; $i < $end; $i++) {
        // make sure that PHP doesn't try to show results that don't exist
        if ($i == $total_results) {
            break;
        }
        // echo out the contents of each row into a table
        echo "<tr " . $cls . ">";
        echo '<td>' . mysql_result($result, $i, 'ccode') . '</td>';
        echo '<td>' . mysql_result($result, $i, 'country') . '</td>';
        echo "</tr>";
    }       
    // close table>
echo "</table>";
// pagination
?>       

This part will loop from $start to $end variable. The records will be displayed on the page. 

paginate.php

function paginate($reload, $page, $tpages) {
    $adjacents = 2;
    $prevlabel = "‹ Prev";
    $nextlabel = "Next ›";
    $out = "";
    // previous
    if ($page == 1) {
        $out.= "<span>".$prevlabel."</span>\n";
    } elseif ($page == 2) {
        $out.="<li><a href=\"".$reload."\">".$prevlabel."</a>\n</li>";
    } else {
        $out.="<li><a href=\"".$reload."&amp;page=".($page - 1)."\">".$prevlabel."</a>\n</li>";
    }
    $pmin=($page>$adjacents)?($page - $adjacents):1;
    $pmax=($page<($tpages - $adjacents))?($page + $adjacents):$tpages;
    for ($i = $pmin; $i <= $pmax; $i++) {
        if ($i == $page) {
            $out.= "<li class=\"active\"><a href=''>".$i."</a></li>\n";
        } elseif ($i == 1) {
            $out.= "<li><a href=\"".$reload."\">".$i."</a>\n</li>";
        } else {
            $out.= "<li><a href=\"".$reload. "&amp;page=".$i."\">".$i. "</a>\n</li>";
        }
    }
    
    if ($page<($tpages - $adjacents)) {
        $out.= "<a style='font-size:11px' href=\"" . $reload."&amp;page=".$tpages."\">" .$tpages."</a>\n";
    }
    // next
    if ($page < $tpages) {
        $out.= "<li><a href=\"".$reload."&amp;page=".($page + 1)."\">".$nextlabel."</a>\n</li>";
    } else {
        $out.= "<span style='font-size:11px'>".$nextlabel."</span>\n";
    }
    $out.= "";
    return $out;
}

This is our main code which created the pagination html code and this code will be called from our index.php. In this first we need to set the $adjacents which is for how many li u want to display beside your current selected page. The remaining code will generate the li of the links.

Source: http://www.techumber.com/2012/08/simple-pagination-with-php-mysql.html