Browse All Articles > Simple PHP pagination
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.
Just the 'id' and 'name' (simple, simple, simple,...) fields. This is the data for the table:
Ready to go. Here's the index.php file
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:
Below: $ipp variable is for the Items Per Page, you would change it here to display the number of records you want.
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.
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.
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.
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
We have to know where to start and where to end the pages display, so we count that
Below is the section where we display the pages (the numbers).
And this is the section for the "next / last" links.
Let's hope it would work for you. If you want to praise / curse this tutorial, feel free to post a comment.
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 ;
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');
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>";
?>
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'];
Below: $ipp variable is for the Items Per Page, you would change it here to display the number of records you want.
$ipp = 3;
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 />";
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 />";
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>";
}
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> ";
}
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;
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> ";
}
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> ";
}
Let's hope it would work for you. If you want to praise / curse this tutorial, feel free to post a comment.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Commented:
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,searche
Commented: