Online Tutorials & Training Materials | STechies.com
Register Login

How to Create Pagination with PHP and MySql

|| 0

How to Create Pagination with PHP and MySql
Stechies

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'>&lt;</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'> &gt;</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'>&lt;</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'> &gt;</a>";
				}
              ?>
        </div>
	
</body>
</html>