<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Simple PHP pagination

Published on
12,029 Points
4,829 Views
2 Endorsements
Last Modified:
Approved
As this topic comes over and over again in different forms, I've finally decided to write a short (yea, right...) article / tutorial about pagination with PHP with MySQL database. There are dozens of these kind of tutorials, I know - I wanted to make it as simple as possible and as short as possible AND so we all can reach for it at EE.

First of all - the preparations. I assume you have your Apache + PHP set up (by XAMPP / WAMP / any other).

1. What you need is one file - lets name it.... hmmm.... index.php ! Place it under a directory of your choice.
2. Let's setup a database. If you use phpmyadmin, you know what to do - import the script creating the "pagination" table. I have created a database named "test" for it.

CREATE TABLE IF NOT EXISTS `pagination` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;

Open in new window


Just the 'id' and 'name' (simple, simple, simple,...) fields. This is the data for the table:

INSERT INTO `pagination` (`id`, `name`) VALUES
(1, 'one'),
(2, 'two'),
(3, 'three'),
(4, 'four'),
(5, 'five'),
(6, 'six'),
(7, 'seven'),
(8, 'eight'),
(9, 'nine'),
(10, 'ten'),
(11, 'eleven'),
(12, 'twelve'),
(13, 'thirteen'),
(14, 'fourteen'),
(15, 'fifteen'),
(16, 'sixteen'),
(17, 'seventeen'),
(18, 'eighteen'),
(19, 'nineteen'),
(20, 'twenty'),
(21, 'twenty one');

Open in new window


Ready to go. Here's the index.php file

<?php
    // LET'S SEE WHAT'S WRONG
    // BUT DO NOT DISPLAY NOTICES
    error_reporting(E_ALL ^ E_NOTICE);
    
    // CONNECT TO THE DB
    $link = mysql_connect("localhost","you","yourpass") or die(mysql_error());
    mysql_select_db("test") or die(mysql_error());
    
    // WE NEED TO KNOW HOW MUCH RECORDS THERE ARE
    $query = "select count(*) as recordcount from pagination";
    $result = mysql_query($query) or die(mysql_error());
    $row = mysql_fetch_array($result) or die(mysql_error());
    
    // NUMBER OF ALL ROWS
    $numrows = $row['recordcount'];
    
    // HERE WE SET THE NUMER OF ITEMS PER ONE PAGE
    $ipp = 3;
    
    // NUMBER OF ALL PAGES
    // WE USE CEIL SO IF THE RECORD COUNT
    // IS - FOR EXAMPLE - 11 WE GET 3 PAGES
    $pagecount = ceil($numrows / $ipp);
    echo"We have ".$pagecount." pages<br />";
    
    // DISPLAY THE PAGES LINKS
    for($i=1;$i<=$pagecount;$i++){
        echo"<a href=\"index.php?page=".$i."\">$i</a> | ";
    }
    
    // THE CURRENT PAGE NUMBER FROM GET VARIABLE
    // IF NOT SET - IT'S FIRST 
    $page = 0;
	if(empty($_GET[page]) || !isset($_GET[page])) $page = 1;
	else $page = $_GET[page];	
	
	// WHERE ARE WE ?
    echo"<br /><br />current page: $page<br /><br />";	
	
	// STARTRECORD IS USED FOR THE MYSQL QUERY
	// SO WE KNOW WHERE WE CAN START FROM
	// LIMIT WITH IPP (ITEMS PER PAGE)
	if(!empty($page)) $startrecord = ($page-1)*$ipp;
	else $startrecord = 0; 		
	
	$query = "select * from pagination limit $startrecord,$ipp";
	$result = mysql_query($query) or die(mysql_error());
	while($row = mysql_fetch_array($result)){
        echo"<div>$row[id], $row[name]</div>";
	}
	
	// THIS IS THE VALUE FOR THE NUMBER OF
	// NEXT AND PREVIOUS PAGES DISPLAYED IN
	// THE DYNAMIC PANEL
 	$margin = 2;
	
	
	// THE DYNAMIC PANEL
    echo"<br /><div>";
    echo"Page ";
            
			if($page == 1);
			else
			{
				$prev = $page - 1;
				echo" <a href=\"index.php?page=1\">first</a> ";
				echo" <a href=\"index.php?page=$prev\">prev</a> ";
			}

			if($page-$margin <= 0) $start = 1; else $start = $page - $margin;
			if($page+$margin > $pagecount) $last = $pagecount; else $last = $page + $margin;

			for($i=$start;$i<=$last;$i++){
                // SHOW THE CURRENT PAGE WITH DIFFERENT FORMATTING
                if($i == $page)
                    echo" <a href=\"index.php?page=$i\" style=\"font-weight: bold;\">$i</a> ";
                else
                    echo" <a href=\"index.php?page=$i\">$i</a> ";
			}


			if($page == $pagecount);
			else
			{
				$next = $page + 1;
				echo" <a href=\"index.php?page=$next\">next</a> ";
				echo" <a href=\"index.php?page=$pagecount\">last</a> ";
			}
    
    echo"</div>";  		
    
?>

Open in new window

   
This is all you need. The comments are there, but to make myself clear - I will add explanation below. I hope the top part of the script is obvious, we have to connect, display errors and know how much records in the table we have:

    
error_reporting(E_ALL ^ E_NOTICE);
    
$link = mysql_connect("localhost","you","yourpass") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
    
$query = "select count(*) as recordcount from pagination";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
    
$numrows = $row['recordcount'];

Open in new window


Below: $ipp variable is for the Items Per Page, you would change it here to display the number of records you want.

$ipp = 3;

Open in new window


Now we can count the number of the pages: let's say you have 501 records in your query and you set the $ipp to 100. So there are 6 pages with one item on the last - this is why we use ceil.

$pagecount = ceil($numrows / $ipp);
echo"We have ".$pagecount." pages<br />";

Open in new window


Display navigation having all the page links, pointing each link to index.php,
having $page variable in $_GET[]. If the page variable is not set, the page number is 1. Then we output the page number - where we are.

for($i=1;$i<=$pagecount;$i++){
   echo"<a href=\"index.php?page=".$i."\">$i</a> | ";
}

if(empty($_GET[page]) || !isset($_GET[page])) $page = 1;
else $page = $_GET[page];	

echo"<br /><br />current page: $page<br /><br />";

Open in new window

     

And the heart of the pagination. We use the "main" db query to output the records FROM certain index with a limit of $ipp (items per page). You put your query instead of "select * from pagination". Display the records.
if(!empty($page)) $startrecord = ($page-1)*$ipp;
else $startrecord = 0; 		
	
$query = "select * from pagination limit $startrecord,$ipp";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
        echo"<div>$row[id], $row[name]</div>";
}

Open in new window


That's it. Or isn't it ? Oh, yes - the dynamic panel.
That is not necessary, but when you have 50 pages - might be handy. It shows the "middle" pages with a $margin variable - for adjacent pages - you can adjust and links to first / previous, next / last page. I've set it to 2.
The dynamic panel is divided into sections, this is the "first / previous" section

if($page == 1);
else
{
   $prev = $page - 1;
   echo" <a href=\"index.php?page=1\">first</a> ";
   echo" <a href=\"index.php?page=$prev\">prev</a> ";
}

Open in new window


We have to know where to start and where to end the pages display, so we count that

if($page-$margin <= 0) $start = 1; else $start = $page - $margin;
if($page+$margin > $pagecount) $last = $pagecount; else $last = $page + $margin;

Open in new window


Below is the section where we display the pages (the numbers).

for($i=$start;$i<=$last;$i++){
   // SHOW THE CURRENT PAGE WITH DIFFERENT FORMATTING
   if($i == $page)
      echo" <a href=\"index.php?page=$i\" style=\"font-weight: bold;\">$i</a> ";
   else
      echo" <a href=\"index.php?page=$i\">$i</a> ";
}

Open in new window


And this is the section for the "next / last" links.

if($page == $pagecount);
else
{
   $next = $page + 1;
   echo" <a href=\"index.php?page=$next\">next</a> ";
   echo" <a href=\"index.php?page=$pagecount\">last</a> ";
}

Open in new window

   
Let's hope it would work for you. If you want to praise / curse this tutorial, feel free to post a comment.
   
2
Comment
2 Comments
LVL 7

Expert Comment

by:Vimal DM
Hey,

Just go for a grid based implementation where you can make thing possible for all kind of your stuff.

"flexigrid"

which is the one will have pagination,filters,searches.
0

Expert Comment

by:Souradeep From Learningocean
Great Tutorial! This PHP pagination tutorial have all the options. But I think it would be better if you add an option to change the total number of results. What do you think about it?
0

Featured Post

HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Join & Write a Comment

Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month