In this tutorial you will learn how to create pagination for listing pages using PHP and MySql.
What is Pagination?
It is possible that one can have thousands of fetched records or data, but it is not a good practice to display all the data on one page because when you show all your fetched results in one page, this will affect the page load time.
Pagination means showing or displaying all your data in multiple pages instead of displaying all the fetched data in a single page.
As we know that with the help of the pagination, we can divide fetched results into multiple pages, by doing this page will take less time to load and provide excellent user experience while browsing your site or application.
Connecting to MySql Database
We need to connect to the database in order to fetch the data from our existing table.
Code:
// Database Connection Start
$link_id = mysql_connect("localhost", "root", "shiaghahQu1shesohquu");
if (!link_id) die(mysql_error());
$link_id = mysql_select_db("test_db");
if (!link_id) die(mysqli_error());
// Database Connection Ends
Create table
This is optional if you already have your table then you simply need to fetch the data from your ‘Table’
To explain this tutorial, we have created here table which is “article_table” with fields.
article_id: integer 5, Primary Key, AUTO_INCREMENT
article_title: VarChar 255
article_desc: Text
article_date: Date
code:
--
-- Table structure for table `article_table`
--
CREATE TABLE `article_table` (
`article_id` int(5) UNSIGNED NOT NULL,
`article_title` varchar(255) NOT NULL,
`article_desc` text NOT NULL,
`article_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `article_table`
--
ALTER TABLE `article_table`
ADD PRIMARY KEY (`article_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `article_table`
--
ALTER TABLE `article_table`
MODIFY `article_id` int(5) UNSIGNED NOT NULL AUTO_INCREMENT;COMMIT;
Get Current Page no
First we will get the current page no if not defined then it will take 0 as current page.
// Define Show, showRecords & maxshow
// Variable to get page number
// if page no is not define then it will take 0
$show=$_GET['page'];
$maxshow = 10; // No of entry show in a single page
if (empty($show)) {
$show = 0;
} else {
$show = $show - 1;
}
Define Current Page Data Variables
Before fetching data from database we need to define start row and end rows to fetch records.
// Set limit to show recored from databse
$showRecord = $show * $maxshow;
$nextShow = $showRecord + $maxshow;
// Define Show, showRecords & maxshow END
Fetch Data from Database
To fatch data from databse we ill run two queries one to get all records and second to get records to show in current page.
// Get all records from database to get total no of recored
$getAllRecord=mysql_query("SELECT count( * ) as total_record FROM article_table");
$rowTotal = mysql_fetch_row($getAllRecord);
$totrecord = $rowTotal[0];
// Get no of recored to show in a single page
$getRecord=mysql_query("SELECT * FROM article_table ORDER BY article_id ASC LIMIT $showRecord, $maxshow");
Code to Generate Pagination
// Create Page Navigation
$pageUrl = "/Pagination.php?page=";
$totpage = $totrecord / $maxshow;
$show=$show+1;
$int = (int)$totpage;
$decimal = $totpage - $int;
if($decimal){
$totpage = $int + 1;
}
$pageLimit = 10;
$pageMid = $pageLimit/ 2;
if(!$show){
$show=1;
}
if($show >= $pageLimit){
$previous=$show-$pageLimit;
if($previous < 1){
$previous=1;
}
echo"<a href='$pageUrl$previous'><</a>";
}
if($show < $pageLimit){
$i=1;
}else{
$i=$show - $pageMid;
}
if($show >= $pageLimit){
$pageShowEnd = $show + $pageMid;
}else{
$pageShowEnd = $pageLimit;
}
if($pageShowEnd >= $totpage){
$i=$i - $pageMid;
}
if($i < 1){
$i=1;
}
for($i;$i <= $totpage and $i <= $pageShowEnd;$i++){
if($show==$i){
echo"<span>$i</span>";
}else{
echo"<a href='$pageUrl$i'>$i</a>";
}
}
if($totpage > $i){
$next=$i;
echo"<a href='$pageUrl$next'> ></a>";
}
Create Button CSS
Following are the code example to create button:
<style>.pagination a {
display: inline-block;
margin: 0 3px;
padding-left: 2px;
padding-right: 2px;
border-radius: 50%;
min-width: 30px;
height: 30px;
line-height: 26px;
font-weight: 900;
color: #000;
background-color: transparent;
border: 2px solid #e74c3c;
opacity: .7;
text-align: center;
text-decoration: none;
}
.pagination span {
display: inline-block;
margin: 0 3px;
padding: auto;
border-radius: 50%;
min-width: 30px;
height: 30px;
line-height: 26px;
font-weight: 900;
color: #000;
background-color: #000;
color: #fff;
border: 2px solid #000;
opacity: .7;
text-align: center;
}</style>
Complete Code:
<?php
// Database Connection Start
$link_id = mysql_connect("localhost", "root", "shiaghahQu1shesohquu");
if (!link_id) die(mysql_error());
$link_id = mysql_select_db("test_db");
if (!link_id) die(mysqli_error());
// Database Connection Ends
// Define Show, showRecords & maxshow
// Variable to get page number
// if page no is not define then it will take 0
$show=$_GET['page'];
$maxshow = 10; // No of entry show in a single page
if (empty($show)) {
$show = 0;
} else {
$show = $show - 1;
}
// Set limit to show recored from databse
$showRecord = $show * $maxshow;
$nextShow = $showRecord + $maxshow;
// Define Show, showRecords & maxshow END
// Get all records from database to get total no of recored
$getAllRecord=mysql_query("SELECT count( * ) as total_record FROM article_table");
$rowTotal = mysql_fetch_row($getAllRecord);
$totrecord = $rowTotal[0];
// Get no of recored to show in a single page
$getRecord=mysql_query("SELECT * FROM article_table ORDER BY article_id ASC LIMIT $showRecord, $maxshow");
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Pagination</title>
<style>.pagination a {
display: inline-block;
margin: 0 3px;
padding-left: 2px;
padding-right: 2px;
border-radius: 50%;
min-width: 30px;
height: 30px;
line-height: 26px;
font-weight: 900;
color: #000;
background-color: transparent;
border: 2px solid #e74c3c;
opacity: .7;
text-align: center;
text-decoration: none;
}
.pagination span {
display: inline-block;
margin: 0 3px;
padding: auto;
border-radius: 50%;
min-width: 30px;
height: 30px;
line-height: 26px;
font-weight: 900;
color: #000;
background-color: #000;
color: #fff;
border: 2px solid #000;
opacity: .7;
text-align: center;
}</style>
</head>
<body>
<h1>Pagination</h1>
<p>Total Records: <?=$totrecord?></p>
<?php while($rowCat = mysql_fetch_array($getRecord)){?>
<p><strong><?php echo $rowCat['article_title'];?></strong><br>
<?php echo $rowCat['article_desc'];?>
</p><hr>
<? }?>
<div class="pagination" style="text-align: center;">
<?php
// Create Page Navigation
$pageUrl = "/Pagination.php?page=";
$totpage = $totrecord / $maxshow;
$show=$show+1;
$int = (int)$totpage;
$decimal = $totpage - $int;
if($decimal){
$totpage = $int + 1;
}
$pageLimit = 10;
$pageMid = $pageLimit/ 2;
if(!$show){
$show=1;
}
if($show >= $pageLimit){
$previous=$show-$pageLimit;
if($previous < 1){
$previous=1;
}
echo"<a href='$pageUrl$previous'><</a>";
}
if($show < $pageLimit){
$i=1;
}else{
$i=$show - $pageMid;
}
if($show >= $pageLimit){
$pageShowEnd = $show + $pageMid;
}else{
$pageShowEnd = $pageLimit;
}
if($pageShowEnd >= $totpage){
$i=$i - $pageMid;
}
if($i < 1){
$i=1;
}
for($i;$i <= $totpage and $i <= $pageShowEnd;$i++){
if($show==$i){
echo"<span>$i</span>";
}else{
echo"<a href='$pageUrl$i'>$i</a>";
}
}
if($totpage > $i){
$next=$i;
echo"<a href='$pageUrl$next'> ></a>";
}
?>
</div>
</body>
</html>