Home » Pagination in PHP and MySQLi Source Code

Pagination in PHP and MySQLi Source Code

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:  how to upload images in wordpress by custom code?

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:  Group by and Having Clause in SQL

Related FAQ

Here are some more FAQ related to this Article:

  1. Read Also:  Remove undefined value from jquery array
  2. Read Also:  Add prefix in each key of PHP Array
  3. Read Also:  Convert object to Array in PHP Example
  4. Read Also:  How To Add Flash Messages in Laravel 6 Session?
  5. Read Also:  Laravel 6 Eloquent Model Increment Decrement
  6. Read Also:  Linux Crontab Unix Linux Command examples
  7. Read Also:  How to get CSS class Name Property using Jquery
  8. Read Also:  angular 6 tutorial for beginners step by step
  9. Read Also:  PHP AJAX Live Search Box Autocomplete Using MySQL Database
  10. Read Also:  get hours/months time difference between two dates in Laravel

Leave a Comment

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