Paginate your web pages with PHP and MySQL
A usual problem facing new Web Developers is how to split a Database query over several pages (paginate). The example following is in a basic form so will not contain code or methods for content filtering or to allow dynamic options for the pagination but should give a good base to build from.
For this example we are going to use OOP and create a class. This will enable an easy way to plug this code into existing database queries and to make it unobtrusive so that it can be modified and extended at any time.
Lets start by creating our class and setting up our class attributes. The attributes $pagenumber and $lastpage will only be used from within the class so for correctness we shall make them private. The attribute $perpage will hold how many SQL results will be presented perpage, $paginationresults will hold the amount of page numbers to be presented in the pagination navigation, $pagenumber will hold which page the instance is dealing with and $lastpage will hold the number of the amount of pages that the SQL query will be split over.
class paginate{
public $perpage;
public $paginationresults;
private $pagenumber;
private $lastpage;
Now we need to set up the method that will return the SQL statement to limit the queries to return the correct number of results and the right results for the page being viewed.
public function sql_limit($numberrows){
From the outside of this class you will need to message in the number of rows returned from the SQL query and this information will enable us to calculate how many pages the query will be split over.
$lastpage = ceil($numberrows/$this->perpage);
Next we need to set our page number variable and give it a default value of 1
$pagenumber = 1;
We will be sending our page numbers by using GET query string, which as global we can pick up inside the class without messaging. If the $_GET['page'] is set and is a number we will override our $pagenumber variable.
if(ctype_digit(@$_GET['page'])) $pagenumber = $_GET['page'];
Now we have all we need to construct a limit statement for the SQL query.
$limit = 'limit '.($pagenumber - 1) * $this->perpage .',' .$this->perpage;
Now we need to assign to our attributes their values so this instance will be ready to accept a call to the Method display().
$this->lastpage=$lastpage;
$this->pagenumber=$pagenumber;
Finally for this method we want to return our built SQL limit query string and end the method with a closing curly brace.
return $limit; }
Next we need to create our display method that will provide the navigation between the pages.
public function display(){
Assign our attributes to variables
$paginationresults=ceil($this->paginationresults/2);
$pagenumber=$this->pagenumber;
$lastpage=$this->lastpage;
A simple condition that will only construct our navigation if there is more than one page in the result.
if($lastpage > 1){
Start our variable that will contain our HTML output
$paginationdisplay = '<div id="pagination" >';
Strip any page request from the current query string as we will need to override this with the value for the navigation whilst keeping all current query string intact
$querystring=eregi_replace("(page=[0-9]+)","",$_SERVER['QUERY_STRING']);
Add our links to the HTML that contain the URL of the first and last pages in the results
$first = '<a href="./?'.$querystring.'"
class="firstpage" >« First page</a> ';
$last = '<a href="./?'.$querystring.'&page='.$lastpage.'"
class="lastpage" >Last page »</a>';
Assign a variable that will contain the page numbered navigation
$pagination=NULL;
Now we create a for loop which will provide the links back from the current page to the first page
for($countpagelink = $pagenumber-$paginationresults;
$countpagelink < $pagenumber; $countpagelink ++){
if($countpagelink <1 ) continue;
$pagination .= '<a href="./?'.$querystring.'&page='.$countpagelink.'" >
'.$countpagelink.'</a> ';
}
Then we provide the current page
$pagination .= '<a href="./?'.$querystring.'&page='.$pagenumber.'"
class="currentpage" >'.$pagenumber.'</a> ';
Next run a second for lopp to provide the results after the first page until we reach the limit of allowed navigation items ($paginationresults)
for($countpagelink = $pagenumber+1;
$countpagelink < $pagenumber+($paginationresults+1); $countpagelink ++){
if($countpagelink > $lastpage ) break;
$pagination .= '<a href="./?'.$querystring.'&page='.$countpagelink.'" >
'.$countpagelink.'</a> ';
}
Concatenate the HTML generated so far
$paginationdisplay .= $first.$pagination.$last;
$paginationdisplay .= '</div>';
Return the HTML
return $paginationdisplay;
End the method and class
} } }
Now to make this class work, whenever we want to display database results over several pages we can simply add a few lines to our code to enable this class to split the results.
Start an instance of this object
$paginate = new paginate();
Add how many results as rows for each page
$paginate->perpage=10;
Add how many navigation links as page numbers you want displayed
$paginate->paginationresults=15;
For example we have a query to extract news items from a databse. All we do is add a query to count the number of rows
$getnews = $dbh->database->query("SELECT COUNT(id) FROM news ",PDO::FETCH_ASSOC);
$numberrows = implode($countrows->fetchAll(PDO::FETCH_COLUMN,0));
Now we pass the number of rows to our class which will return the SQL limit statement
$limit=$paginate->sql_limit($numberrows);
Which we can add to our query, this time the query is to retrive the data with our $limit variable
$getnews = $dbh->database->query("SELECT * FROM news $limit",PDO::FETCH_ASSOC);
We can process this query to HTML output
$news = $getnews->fetchAll();
$number_results = count($news);
for($i=1,$i<$number_results,$i++){
$key=$i-1;
echo '<tr><td>'.$news[$key]['title'].'</td></tr>';
}
As our instance of our paginate class holds state wherever we want the page number menu we can call
echo $paginate->display();
The complete class code is below
class paginate{
public $perpage;
public $paginationresults;
private $pagenumber;
private $lastpage;
public function sql_limit($numberrows){
$lastpage = ceil($numberrows/$this->perpage);
$pagenumber = 1;
if(ctype_digit(@$_GET['page'])) $pagenumber = $_GET['page'];
$limit = 'limit '.($pagenumber - 1) * $this->perpage .',' .$this->perpage;
$this->lastpage=$lastpage;
$this->pagenumber=$pagenumber;
return $limit;
}
public function display(){
$paginationresults=ceil($this->paginationresults/2);
$pagenumber=$this->pagenumber;
$lastpage=$this->lastpage;
if($lastpage > 1){
$paginationdisplay = '<div id="pagination" >';
$querystring=eregi_replace("(page=[0-9]+)","",$_SERVER['QUERY_STRING']);
$first = '<a href="./?'.$querystring.'"
class="firstpage" >« First page</a> ';
$last = '<a href="./?'.$querystring.'&page='.$lastpage.'"
class="lastpage" >Last page »</a>';
$pagination=NULL;
for($countpagelink = $pagenumber-$paginationresults;
$countpagelink < $pagenumber; $countpagelink ++){
if($countpagelink <1 ) continue;
$pagination .= '<a href="./?'.$querystring.'&page='.$countpagelink.'" >
'.$countpagelink.'</a> ';
}
$pagination .= '<a href="./?'.$querystring.'&page='.$pagenumber.'"
class="currentpage" >'.$pagenumber.'</a> ';
for($countpagelink = $pagenumber+1;
$countpagelink < $pagenumber+($paginationresults+1); $countpagelink ++){
if($countpagelink > $lastpage ) break;
$pagination .= '<a href="./?'.$querystring.'&page='.$countpagelink.'" >
'.$countpagelink.'</a> ';
}
$paginationdisplay .= $first.$pagination.$last;
$paginationdisplay .= '</div>';
return $paginationdisplay;
}
}
}