Basic PHP and MySQL Pagination Tutorial

I thought I would try my hand at writing my own little tutorial on how to do a pagination script with PHP and MySQL. Pagination refers to the numbering of pages in a document. So in regards to a Web Site it is the ability to span content over multiple pages while showing the end-user something like:

Pages [1] 2 3 4 5

This is useful for website such as Blogs, where you show a set amount of postings per page. I show 5 posts per page so that the end-user does not have to scroll forever to read the content.

Tools required: PHP, MySQL and your favorite editor. I use NotePad++

Step 1: Set The Amount Of Entries Per Page

At the top of the script is where I normaly set the page limit

<?php
// 5 Entries Per Page
$LIMIT = 5;
?>

Step 2: Get Current Page

Next I check to see if the URL shows the current page we are viewing. If not then we set the page to 1.

<?php
If (isset($_GET[‘page’])) {
  // Get Current page from URL
  $page = $_GET[‘page’];
  If ($page <= 0) {
    // Page is less than 0 then set it to 1
    $page = 1;
  }
} else {
  // URL does not show the page set it to 1
  $page = 1;
}
?>

That seems pretty simple right? I’ll show how you set the page in the URL down a bit further.

Step 3: Query Database for Number of Entries

Assume that you know how to create a connection to your MySQL Database in PHP, you can also follow this example if you are unsure: PUT URL HERE

<?php
// Create MySQL Query String
$strqry = “SELECT id from MyTable”;
$query = mysql_query($strqry) or die("MySQL Error: <br /> {$strqry} <br />", mysql_error());
// Get number of rows returned
$TOTALROWS = mysql_num_rows();
// Figure out how many pages there should be based on your $LIMIT
$NumOfPages = $TOTALROWS / $LIMIT;
// This is for your MySQL Query to limit the entries per page
$LimitValue = $page * $LIMIT – ($LIMIT);
?>

With me so far? What the above code does is creates a string for the MySQL query, I find this easier to debug if something in the query goes wrong. I then figure out how many pages in total there will be and what limit my next MySQL query will take on.

Now on to the fun part that actually displays the page numbers and their hyperlinks like they do in my Blog script.

Step 4: Create Hyperlinks Based on Pages and Current Page Being Viewed

<div id="paginating" align="right">Pages:
<?php // Check to make sure we’re not on page 1 or Total number of pages is not 1
If ($page == ceil($NumOfPages) &amp;&amp; $page != 1) {
  for($i = 1; $i <= ceil($NumOfPages)-1; $i++) {
    // Loop through the number of total pages
    if($i > 0) {
      // if $i greater than 0 display it as a hyperlink
      echo "<a href=\"/{$i}\">{$i}</a>";
      }
    }
}
If ($page == ceil($NumOfPages) ) {
  $startPage = $page;
} else {
  $startPage = 1;
}
for ($i = $startPage; $i <= $page+6; $i++) {
  // Display first 7 pages
  if ($i <= ceil($NumOfPages)) {
    // $page is not the last page
    if($i == $page) {
      // $page is current page
      echo " [{$i}] ";
    } else {
      // Not the current page Hyperlink them
      echo "<a href="\"/{$i}\">{$i}</a> ";
    }
  }
}
?>
</div>

Now all of that might be a bit confusing but pretty much what it does is that it loops through the number of pages and displays the first 7 pages. I then find out what the start page should be.

Next is the loop the next 6 pages from the current page you’re on. Perhaps this is not the best way about doing it but it seems to work for now.

Now if you are doing a MySQL Query based on the current page you are viewing you would do something similar to this:

<?php
$strqry = "SELECT * FROM MyTable LIMIT $LimitValue, $LIMIT";
?>

That will give you the proper limit range which will tell MySQL to pull the current location and then limit it only to that of 5 or whatever you set $LIMIT to.

This was my first tutorial and even though I am sure there are other ways to go about it, I took what I learnt from previous tutorials and tried my own thing and for now it seems to work.

So I hope this little tutorial has giving you a little knowledge on how Pagination works with PHP and MySQL and that you can use some of it with whatever project you are working on. I’d also love to hear your comments about this tutorial as well so that I can become better at writing them!