Simple PHP MYSQL Pagination
Pagination can get complicated depending on the size of records in the database, especially when you want to group number of links, display next/previous links etc. In this tutorial we’ll focus on creating simple PHP MYSQL pagination.
Let’s Create MYSQL Database
An SQL file is included in the complete script file attached. Please create a MYSQL database first, then import .sql file. (This is only for this tutorial purpose but you can use your own database as well if you have already created.)
Database Connection:
Let’s start with database connection file to store our MySql database username and password, and also to connect to MySQL using PHP mysqli_connect(). If you notice we are using PHP mysqli_connect() here, not regular mysql_connect(), i = stands for improved extension, developed to take advantage of new features found in newer MySQL systems. PHP encourages everyone to use mysqli_* instead of regular mysql_* extensions, which will be totally removed in the future PHP versions.
Create connection.php file and put that php code (provided below) in it.
$db_username = 'root'; // Your MYSQL Username.
$db_password = ''; // Your MYSQL Password.
$db_name = 'database_name_here'; // Your Database name.
$db_host = 'localhost';
$conDB = mysqli_connect($db_host, $db_username, $db_password,$db_name)or die('Error: Could not connect to database.');Pagination function:
Let’s create pagination function and store it in your functions.php file. (We’re using functions.php file to store this function. We prefer this method because that way, we can easily add more helpfull functions to our file later.)
function pagination($query,$per_page=10,$page=1,$url='?'){
global $conDB;
$query = "SELECT COUNT(*) as `num` FROM {$query}";
$row = mysqli_fetch_array(mysqli_query($conDB,$query));
$total = $row['num'];
$adjacents = "2";
$prevlabel = "‹ Prev";
$nextlabel = "Next ›";
$lastlabel = "Last ››";
$page = ($page == 0 ? 1 : $page);
$start = ($page - 1) * $per_page;
$prev = $page - 1;
$next = $page + 1;
$lastpage = ceil($total/$per_page);
$lpm1 = $lastpage - 1; // //last page minus 1
$pagination = "";
if($lastpage > 1){
$pagination .= "<ul class='pagination'>";
$pagination .= "<li class='page_info'>Page {$page} of {$lastpage}</li>";
if ($page > 1) $pagination.= "<li><a href='{$url}page={$prev}'>{$prevlabel}</a></li>";
if ($lastpage < 7 + ($adjacents * 2)){
for ($counter = 1; $counter <= $lastpage; $counter++){
if ($counter == $page)
$pagination.= "<li><a class='current'>{$counter}</a></li>";
else
$pagination.= "<li><a href='{$url}page={$counter}'>{$counter}</a></li>";
}
} elseif($lastpage > 5 + ($adjacents * 2)){
if($page < 1 + ($adjacents * 2)) {
for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++){
if ($counter == $page)
$pagination.= "<li><a class='current'>{$counter}</a></li>";
else
$pagination.= "<li><a href='{$url}page={$counter}'>{$counter}</a></li>";
}
$pagination.= "<li class='dot'>...</li>";
$pagination.= "<li><a href='{$url}page={$lpm1}'>{$lpm1}</a></li>";
$pagination.= "<li><a href='{$url}page={$lastpage}'>{$lastpage}</a></li>";
} elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2)) {
$pagination.= "<li><a href='{$url}page=1'>1</a></li>";
$pagination.= "<li><a href='{$url}page=2'>2</a></li>";
$pagination.= "<li class='dot'>...</li>";
for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++) {
if ($counter == $page)
$pagination.= "<li><a class='current'>{$counter}</a></li>";
else
$pagination.= "<li><a href='{$url}page={$counter}'>{$counter}</a></li>";
}
$pagination.= "<li class='dot'>..</li>";
$pagination.= "<li><a href='{$url}page={$lpm1}'>{$lpm1}</a></li>";
$pagination.= "<li><a href='{$url}page={$lastpage}'>{$lastpage}</a></li>";
} else {
$pagination.= "<li><a href='{$url}page=1'>1</a></li>";
$pagination.= "<li><a href='{$url}page=2'>2</a></li>";
$pagination.= "<li class='dot'>..</li>";
for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++) {
if ($counter == $page)
$pagination.= "<li><a class='current'>{$counter}</a></li>";
else
$pagination.= "<li><a href='{$url}page={$counter}'>{$counter}</a></li>";
}
}
}
if ($page < $counter - 1) {
$pagination.= "<li><a href='{$url}page={$next}'>{$nextlabel}</a></li>";
$pagination.= "<li><a href='{$url}page=$lastpage'>{$lastlabel}</a></li>";
}
$pagination.= "</ul>";
}
return $pagination;
}Displaying Database Records with pagination:
include_once('connection.php');
include_once('functions.php');
$page = (int)(!isset($_GET["page"]) ? 1 : $_GET["page"]);
if ($page <= 0) $page = 1;
$per_page = 10; // Set how many records do you want to display per page.
$startpoint = ($page * $per_page) - $per_page;
$statement = "`records` ORDER BY `id` ASC"; // Change `records` according to your table name.
$results = mysqli_query($conDB,"SELECT * FROM {$statement} LIMIT {$startpoint} , {$per_page}");
if (mysqli_num_rows($results) != 0) {
// displaying records.
while ($row = mysqli_fetch_array($results)) {
echo $row["name"]."<br>";
}
} else {
echo "No records are found.";
}
// displaying paginaiton.
echo pagination($statement,$per_page,$page,$url='?');
Simple CSS:
ul.pagination {
text-align:center;
color:#829994;
}
ul.pagination li {
display:inline;
padding:0 3px;
}
ul.pagination a {
color:#0d7963;
display:inline-block;
padding:5px 10px;
border:1px solid #cde0dc;
text-decoration:none;
}
ul.pagination a:hover,
ul.pagination a.current {
background:#0d7963;
color:#fff;
}Complete Pagination script
We just created a Simple PHP MYSQL Pagination function. You can download the complete script from right sidebar. Click on Download Attachment button.