Pagination in PHP and MySQLi Source Code

pagination in php

In the previous article on how to create pagination links in php, I had seen why pagination is needed and also developed a basic structure for implementing a pagination system step by step using PHP full source code With Example and Demo. In step by step, let us start with our development step by step process as well as first of all we create a php script to connect to our mysql Database.

simple pagination in php

In this example We are going to explain how to create pagination or feature using PHP and MySQLi.

product_list.php

<?php  
$serverp_name='localhost'
$productp_name='root';
$password='';
$dbp_name = "tamilrokers";
$conn=mysqli_connect($serverp_name,$productp_name,$password,"$dbp_name");
if(!$conn){
die('sorry, Could not Connect My Sql:' .mysql_error());
}
$limit = 10;  
if (isset($_GET["page"])) {
	$page  = $_GET["page"]; 
	} 
	else{ 
	$page=1;
	};  
$start_from = ($page-1) * $limit;  
$result = mysqli_query($conn,"SELECT * FROM products ORDER BY productid ASC LIMIT $start_from, $limit");
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <title>Bootstrap Implementation of Pagination with PHP and MySQL</title>
  <meta charset="utf-8">
  <meta p_name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
</head>
<body>
<h2>how to use the paginations in php</h2>
<table class="table table-bordered table-striped">  
<thead>  
<tr>  
<th>productid</th>  
<th>Product p_name</th>
<th>Product desc</th>
 <th>Product code</th>
<th>Product price</th> 
</tr>  
<thead>  
<tbody>  
<?php  
while ($data = mysqli_fetch_array($result)) {  
?>  
            <tr>  
				<td><?php echo $data["productid"]; ?></td>  
				<td><?php echo $data["p_p_name"]; ?></td>
				<td><?php echo $data["p_desc"]; ?></td>
				<td><?php echo $data["p_code"]; ?></td>
				<td><?php echo $data["price"]; ?></td>			
            </tr>  
<?php  
};  
?>  
</tbody>  
</table>  
<?php  

$result_db = mysqli_query($conn,"SELECT COUNT(id) FROM products"); 
$data_db = mysqli_fetch_row($result_db);  
$all_products = $data_db[0];  
$all_product_pages = ceil($all_products / $limit); 
/* echo  $all_product_pages; */
$pagenavigationLinks = "<ul class='pagination'>";  
for ($i=1; $i<=$all_product_pages; $i++) {
              $pagenavigationLinks .= "<li class='page-item'><a class='page-link' href='pagination.php?page=".$i."'>".$i."</a></li>";	
}
echo $pagenavigationLinks . "</ul>";  
?>

</body>
</html>

How to make PHP Pagination With Example and Demo

Read Also:  Best PHP 7 Tutorial For Complete Beginners

Step 1: Connecting to Database

<?php 

// Define needed credentials. 
define("HOST", "localhost"); 
define("USER", 'root'); 
define("PASS", 'Jalsdfd7845'); 
define("DB", 'bolly4u'); 

// Establish database Connection. 
$conn = mysql_connect(HOST, USER, PASS) 
		or die ('Error connecting to Database.'); 
$connection = mysql_select_db(DB); 

?> 

Step 2: Fetching from Database

require_once "connection.php"; 

$limit = 10;
if (isset($_GET["page"])) { 
	$pn = $_GET["page"]; 
} 
else { 
	$pn=1; 
}; 

$start_from = ($pn-1) * $limit; 

$sql = "SELECT * FROM products LIMIT $start_from, $limit"; 
$get_products = mysql_query ($sql); 

Step 3: Showing the Records

//paginations in php example
<?php 
while ($data = mysql_fetch_array($get_products, MYSQL_ASSOC)) { 
// Display each field of the products records. 
?> 
<tr> 
	<td><?php echo $data["p_code"]; ?></td> 
	<td><?php echo $data["p_name"]; ?></td> 
	<td><?php echo $data["p_desc"]; ?></td> 
	<td><?php echo $data["price"]; ?></td>									 
</tr> 
<?php 
}; 
?> 

Step 4: Showing the Page Navigation

$sql = "SELECT COUNT(*) FROM products"; 
$get_products = mysql_query($sql); 
$data = mysql_fetch_row($get_products); 
$all_products = $data[0]; 

// Number of pages required. 
$all_product_pages = ceil($all_products / $limit); 
$pagenavigationLinks = "";					 
for ($i=1; $i<=$all_product_pages; $i++) { 
if($i==$pn) 
	$pagenavigationLinks .= "<li class='active'><a href='product_list.php?page= 
									".$i."'>".$i."</a></li>"; 
else
	$pagenavigationLinks .= "<li><a href='product_list.php?page=".$i."'> 
										".$i."</a></li>"; 
}; 
echo $pagenavigationLinks; 

Step 5: dynamic paginations in php with mysql example

<!DOCTYPE html> 
<html> 
<head> 
	<title>Implementation of Pagination with PHP and MySQL</title> 
	<meta charset="utf-8"> 
	<meta p_name="viewport" content="width=device-width, initial-scale=1"> 
	<link rel="stylesheet"
	href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> 
</head> 
<body> 
<h1>paginations in php using ajax</h1>
<?php 
	 
	require_once "connection.php"; 

	$limit = 10; // total Number of products to show in a page. 
	// Look for a GET variable page if not found default is 1.	 
	if (isset($_GET["page"])) { 
	$pn = $_GET["page"]; 
	} 
	else { 
	$pn=1; 
	}; 

	$start_from = ($pn-1) * $limit; 

	$sql = "SELECT * FROM products LIMIT $start_from, $limit"; 
	$get_products = mysql_query ($sql); 

?> 
<div class="container dsp"> 
	<br> 
	<div> 
	<h1>Implementation of Paginations with PHP and MySQL</h1> 
	<p>This page is just for demonstration of 
				Basic Paginations using PHP.</p> 
	<table class="table table-striped table-condensed table-bordered"> 
		<thead> 
		<tr> 
		<th width="10%">Code</th> 
		<th>Product Name</th> 
		<th>Product Info</th> 
		<th>Product Price</th> 
		</tr> 
		</thead> 
		<tbody> 
		<?php 
		while ($data = mysql_fetch_array($get_products, MYSQL_ASSOC)) { 
				// Display each field of the records. 
		?> 
		<tr> 
		<td><?php echo $data["p_code"]; ?></td> 
		<td><?php echo $data["p_name"]; ?></td> 
		<td><?php echo $data["p_desc"]; ?></td> 
		<td><?php echo $data["price"]; ?></td>										 
		</tr> 
		<?php 
		}; 
		?> 
		</tbody> 
	</table> 
	<ul class="pagination"> 
	<?php 
		$sql = "SELECT COUNT(*) FROM products"; 
		$get_products = mysql_query($sql); 
		$data = mysql_fetch_row($get_products); 
		$all_products = $data[0]; 
		
		// Number of total products pages required. 
		$all_product_pages = ceil($all_products / $limit); 
		$pagenavigationLinks = "";						 
		for ($i=1; $i<=$all_product_pages; $i++) { 
		if ($i==$pn) { 
			$pagenavigationLinks .= "<li class='active'><a href='product_list.php?page="
												.$i."'>".$i."</a></li>"; 
		}			 
		else { 
			$pagenavigationLinks .= "<li><a href='product_list.php?page=".$i."'> 
												".$i."</a></li>"; 
		} 
		}; 
		echo $pagenavigationLinks; 
	?> 
	</ul> 
	</div> 
</div> 
</body> 
</html> 

I hope you get an idea about paginations code in php with next and previous.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.

Read Also:  Create Live Editable Table with jQuery AJAX using PHP MySQLi

About Pakainfo

I am Jaydeep Gondaliya, a software engineer, the founder and the person running Pakainfo. I'm a full-stack developer, entrepreneur and owner of Pakainfo.com. I live in India and I love to write tutorials and tips that can help to other artisan, a Passionate Blogger, who love to share the informative content on PHP, Javascript, JQuery, Laravel, Codeigniter, VueJS, AngularJS and Bootstrap from the early stage.

View all posts by Pakainfo →

Leave a Reply

Your email address will not be published. Required fields are marked *