Simple pagination with PHP and MySQL

How to achieve simple pagination using PHP and MySQL. Code with demo and free download.

In this tutorial I'll show you how to achieve simple pagination using PHP and MySQL.

In the demo example and download, there are twenty one dummy articles with one image each, which are captured in a database table.
The image names are also in the database, while the actual images are in our 'images' folder.
We're going to limit the articles to three per page, giving us a total of seven pages.

The CSS:

This will need to go preferably in your style sheet, or else in your document head between the style tags.

First we'll create a containing div with the id 'pagination' and add a 40px margin top and bottom to give some space above and below.

/* The pagination container div: */
#pagination {
position:relative;
margin:40px 0;
}

Then we need to create the styling for the navigation buttons and create a class to hide the navigation buttons if there's no more than one page.
We also need to create an 'active' class to underline the number on the active page button:

Now we need to add some basic responsive styling for our articles:

/* The article container: */
.article {
margin:20px 0;
padding:40px 0;
border-bottom:1px solid #ccc;
}

/* The artical image floated left: */
.article img {
float:left;
left:0;
width:100%;
max-width:300px;
margin:auto 30px 15px auto;
}

/* Then we need a clear fix to clear the floated image: */
.clearfix {
clear:both;
}

The MySQL database:

In the demo and download we have a database named 'pagination', with a table set up as follows:

  1. Name: id | type: INT | length: 3 | Auto_Increment
  2. Name: image | type: VARCHAR | length: 10
  3. Name: title | type: VARCHAR | length: 140
  4. Name: description | type: VARCHAR | length: 600
Simple pagination with php and mysql - table settings

The PHP:

First we create a file named 'dbcon.php' and save it in a folder called 'dbcon':

dbcon.php

<?php
$host = 'localhost'; // Host name
$username = 'root'; // Username
$password = ''; // Password - usually empty for localhost server
$database = 'pagination'; // Name of the database containing our 'articles' table

// Let's call our database connection variable $dbCon:

$dbCon = mysqli_connect($host, $username, $password, $database);

// Check connection
if (!$dbCon) {// If the database connection fails...
die('Connection failed: ' . mysqli_connect_error());// ... kill the connection and say 'Connection failed: ' followed by the error.
} ?>

The rest then goes in our 'index.php' file. I'll split this into the three main parts, starting with including the database connection file and the calculations:

<?php
include_once('dbcon/dbcon.php');// Include the database connection that we created

////// FIRST WE SET UP THE TOTAL ARTICLES PER PAGE & CALCULATIONS:
$per_page = 3;// Number of articles per page, change for a different number of articles per page

// Get the page and offset value:
if (isset($_GET['page'])) {
$page = $_GET['page'] - 1;
$offset = $page * $per_page;
}
// Otherwise we render the page and offset as non-existent:
else { $page = 0;
$offset = 0;
}

// Count the total number of articles in the table ordering by their id's ascending:
$articles = "SELECT count(id) FROM articles ORDER by id ASC";
$result = mysqli_query($dbCon, $articles);
$row = mysqli_fetch_array($result);
$total_articles = $row[0];

// Calculate the number of pages:
if ($total_articles > $per_page) {//If there is more than one page
$pages_total = ceil($total_articles / $per_page);
$page_up = $page + 2;
$page_down = $page;
$display ='';//leave the display variable empty so it doesn't hide anything
}
else {// Else if there is only one page
$pages = 1;
$pages_total = 1;
$display = ' class="display-none"';//class to hide page count and buttons if only one page
}

Next we set up and display the 'page number out of how many pages' heading and the navigation buttons:

////// THEN WE DISPLAY THE PAGE COUNT AND BUTTONS:

echo '<h2'.$display.'>Page '; echo $page + 1 .' of '.$pages_total.'</h2>';// Page out of total pages

$i = 1;// Set the $i counting variable to 1

echo '<div id="pageNav"'.$display.'>';// our $display variable will do nothing if more than one page

// Show the page buttons:
if ($page) {
echo '<a href="index.php"><button><<</button></a>';// Button for first page [<<]
echo '<a href="index.php?page='.$page_down.'"><button><</button></a>';// Button for previous page [<]
}

for ($i=1;$i<=$pages_total;$i++) {
if(($i==$page+1)) {
echo '<a href="index.php?page='.$i.'"><button class="active">'.$i.'</button></a>';// Button for active page, underlined using 'active' class
}

// In this next if statement, calculate how many buttons you'd like to show. You can remove to show only the active button and first, prev, next and last buttons:
if(($i!=$page+1)&&($i<=$page+3)&&($i>=$page-1)) {// This is set for two below and two above the current page
echo '<a href="index.php?page='.$i.'"><button>'.$i.'</button></a>'; }
}

if (($page + 1) != $pages_total) {
echo '<a href="index.php?page='.$page_up.'"><button>></button></a>';// Button for next page [>]
echo '<a href="index.php?page='.$pages_total.'"><button>>></button></a>';// Button for last page [>>]
}
echo "</div>";// #pageNav end

Finally, we select our data from the table and loop the array through the 'while' loop:

// DISPLAY THE ARTICLES:
// Select the articles from the table limited as per our $offset and $per_page total:

$result = mysqli_query($dbCon, "SELECT * FROM articles ORDER by id ASC LIMIT $offset, $per_page");
while($row = mysqli_fetch_array($result)) {// Open the while array loop

// Define the variables:
$title=$row['title'];
$image=$row['image'];
$description=$row['description'];

// Then we echo our HTML for each article:
echo '<div class="article">';
echo '<img src="images/'.$image.'">';
echo '<span>';
echo '<h3>'.$title.'</h3>';
echo $description;
echo '<a href="#">Read more</a>';// Link to the full article can go here
echo '</span>';
echo '<div class="clearfix"></div>';// The clear fix
echo '</div>';// .article end
}// Close the while array loop

?>

I hope you found this helpful.

For an awesome responsive image gallery with pagination, try out the below tutorial: