Let's Get Started
The goal of this tutorial is to show the basic concepts involved in MySQL/PHP paging and how you go about coding a simple solution. This is not meant as a final class ready to use on a live site with lots of traffic. The class can be further optimized and streamlined, but that is beyond the purpose of this article.
To accomplish simple MySQL pagination you need two things: a function to run the MySQL statement through to go grab the right rows from the database and a function to create the navigation. I want mine to output a fully styled navigation, with some default css that is really easy to change. To begin with lets call the class pagination and make the two functions.
<?php
class pagination {
function paginate() {
}
function navigation() {
}
}
?>
If you want you can download the files here.
Handling the SQL
First let's make the function which handles the MySQL statement. Let's call it paginate. Paginate will take 3 arguments: the page we are on, the sql statement, and how many results per page we want (with a default of 10). It will return the limited MySQL statement. First take a look at the complete function then I'll break down each part.
function paginate($page,$sql,$perpage=10)
{
//count the number of resutls
$count = mysql_num_rows(mysql_query("$sql"));
//if the page isnt a number set it to page 1
$page = is_nan($page)?1:$page;
//limit the sql statement
$start = (($page - 1) * $perpage);
$end = $start + $perpage;
$sql .= " LIMIT $start, $perpage";
//return the result
return $sql;
}
First off let's take a look at the first two lines.
$count = mysql_num_rows(mysql_query("$sql"));
$page = is_nan($page)?1:$page;
The first thing we do is count the number of rows for the statement and store it in $count. The second line just makes it so that if the page number is not a number it will default to page one. Now the rest.
$start = (($page - 1) * $perpage);
$end = $start + $perpage;
$sql = " LIMIT $start, $perpage";
All these lines do is LIMIT the MySQL statement by starting on the first record for that page, and ending on the last record for that page. Pretty simple. For example let's say you are displaying a list of blog posts and want 10 to a page. You might do something like this to show all the posts on page 5:
//start the class
$paging = new paging;
//this grabs all the blog posts
$sql = "SELECT * FROM `blog_posts`";
//set this to the page you want
$currentpage = "5";
//limit the statement to just this page
$new_sql = $paging->paginate($currentpage,$sql);
//now use the new_sql like you normally would...
Now that you see how it works, you can change the page in the best way you see fit. An easy way is setting a GET variable like /blog_post.php?page=5 and running it like this:
//start the class
$paging = new paging;
//this grabs all the blog posts
$sql = "SELECT * FROM `blog_posts`";
//limit the statement to just this page
$new_sql = $paging->paginate($_GET['page'],$sql);
//now use the new_sql like you normally would...
Implementing a Navigation
The navigation function is a bit more complex. It takes 5 arguments: the page we are on, the original MySQL statement, optional number of results per page, optional number of links to limit the navigation to, and optional url of the page we are on. Take a look at the full thing, with comments explaining what is going on.
function navigation($page,$sql,$perpage=10,$limitnav=10,$link="") {
//count the number of rows
$count = mysql_num_rows(mysql_query("$sql"));
//if the page isnt a number set it to page 1
$page = is_nan($page)?1:$page;
//if the number of rows is less than the number per page we only need
//one page and no nav
if($count <= $perpage) {
return;
}
//set some variables and counters to use
$nav = ""
$a=1;
$previous = $page - 1;
$next = $page + 1;
//get the total number of pages rounded up
$pages = ceil($count/$perpage);
//as long as we are not on page 1 we will build the navigation
if($pages !== 1) {
if(($page - ($limitnav/2)) > 1) {
$nav .= "<span class='pagenav'><a href=\"$link?page=1\"><b>first</b></a></span>";
}
if($page !== 1 && $page !== "1") {
$nav .= " <span class='pagenav'><a href=\"$link?page=$previous\"><b>prev</b></a></span> ";
}
//if the number of pages is more than our limit for links in the
//navigation then create the inner links
if($pages > $limitnav) {
//if we are past halfway then start at half the limit under the page
if(( $page - ($limitnav/2) ) > 0) {
$a = $page - ($limitnav/2);
} else {
//otherwise start at page 1
$a = 1;
}
//similarly find the end page number of the navigation
if(($page+($limitnav/2))<$pages) {
$toplim = $page + ($limitnav/2);
} else {
$toplim = $pages;
}
//from the first page of the nav to the last add a link
while($a <= $toplim) {
//if this is the page we are on add some classes so we can style it
if($a == $page) {
//page we are on
$nav .= "<span class='pagenav pageat'><b>$page</b></span> ";
} else {
//any other page
$nav .= "<span class='pagenav'><a href=\"$link?page=$a\">$a</a></span> ";
}
//increment the counter
$a++;
}
} else {
//if this is the page we are on add some classes so we can style it
while($a <= $pages) {
//page we are on
if($a == $page) {
$nav .= "<span class='pagenav pageat'><b>$page</b></span> ";
} else {
//every other page
$nav .= "<span class='pagenav'><a href=\"$link?page=$a\">$a</a></span> ";
}
//increment the counter
$a++;
}
}
//if we need to add the next and last links
if($page != $pages && $pages != '0') {
$nav .= " <span class='pagenav'><a href=\"$link?page=$next\"><b>next</b></a></span>";
}
if($page < ($pages - ($limitnav/2))) {
$nav .= " <span class='pagenav'><a href=\"$link?page=$pages\"><b>last</b></a></span>";
}
}
//add a div to wrap it in and return it
return "<div class='paging'>$nav</div>";
}
You can use the function like so to show a navigation and paginate the results.
//start the class
$paging = new paging;
//this grabs all the blog posts
$sql = "SELECT * FROM `blog_posts`";
//limit the statement to just this page
$new_sql = $paging->paginate($_GET['page'],$sql);
//output the navigation at the top
//we don't have any other get variables so we dont need to set the link
//make sure to use the ORIGINAL sql here, not the new one
echo $paging->navigation($_GET['page'],$sql);
//now use the new_sql like you normally would...
Styling your navigation
Here is the default styling that I use for pagination. I usually just take this and change the colors and it is fine.
.pagenav {
padding-left:5px;
padding-right:5px;
height:12px;
font-weight:bold;
}
a .pagenav {
color:#E3BD14;
}
.pageat {
background-color:#cccccc;
}
.paging {
text-align:center;
padding-top:3px;
padding-bottom:2px;
}
#paging {
border:1px solid gray;
background-color:#2f281e;
}
You can download the files here.
If you have questions, remarks, bugs, or you want to show off how you styled it please leave me a comment below!
|
|
 |
|
|
|
|
|
|
$pages = round($pages+0.49);
Wow! :) Use this:
$pages = ceil($count/$perpage);
$page = is_nan($page)?1:$page;
start over.
I realize I could optimize this class and write the code far better. My goal for this article was not to make a perfect pagination class, or even one that was ready for a live site with traffic, but to demonstrate the concepts of how to code pagination to a beginning programmer.
You say "Also embedding PHP and HTML is not really a good idea," but the whole reason I made this code is so it generates the divs which can then be easily styled with just a few changes to the css I have already made. I don't want to have to write the layout each time I implement the function, I just want to call it once and be done with it.