Product Grid View with Pagination Using PHP, Bootstrap 4 and MySQLi

This PHP tutorial helps to create a product listing grid with pagination. I have already shared PHP pagination tutorial with ajax and non-ajax manner.

This tutorial will use MySQL as a database. We will create a product listing page with pagination.

I have been working on a project that has product listing functionality, A single product Block containing a title, an image and a description. I have created div based layout for this but you can also achieve the same result using an un-ordered list with the CSS property.

You can also check other recommended tutorials of PHP pagination,

• Simple Pagination with PHP and MySQl Using jQuery
• Simple Ajax Pagination with PHP
• Simple tutorial of pagination in php
• Advanced Ajax Pagination PHP,MySQL Using jQuery

I will use the following files for this Product Listing tutorial,

1. index.php: This file is the main entry file of the PHP product grid view application.
2. response.php: This file will contain html of rows.
3. connection.php: This file will use to create a MySQL database connection with PHP.

We will create product_grid_view_example folder into /www directory and create the above files and folder structure into product_grid_view_example folder.

Include css and js Files

I will included all dependecnies libs files into header section of index.php file.

<script type="text/javascript" charset="utf8" src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-2.0.3.js"></script>

Create MySQL Database and table For PHP Login

We will create 'test' database into the MySQL database server and create table ‘tbl_products’ using the below MySQL script. You can also use the MySQL command line to execute SQL file.

//create table
CREATE TABLE IF NOT EXISTS `products` (
  `productCode` varchar(15) NOT NULL,
  `productName` varchar(70) NOT NULL,
  `productLine` varchar(50) NOT NULL,
  `productScale` varchar(10) NOT NULL,
  `productVendor` varchar(50) NOT NULL,
  `productDescription` text NOT NULL,
  `quantityInStock` smallint(6) NOT NULL,
  `buyPrice` double NOT NULL,
  `MSRP` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now I will add dummy data into the above products MySQL table, You need to run the below query into 'test' database.

//create sample data
INSERT INTO `products` (`productCode`, `productName`, `productLine`, `productScale`, `productVendor`, `productDescription`, `quantityInStock`, `buyPrice`, `MSRP`) VALUES
('S10_1678', '1969 Harley Davidson Ultimate Chopper', 'Motorcycles', '1:10', 'Min Lin Diecast', 'This replica features working kickstand, front suspension, gear-shift lever, footbrake lever, drive chain, wheels and steering. All parts are particularly delicate due to their precise scale and require special care and attention.', 7933, 48.81, 95.7),
('S10_1949', '1952 Alpine Renault 1300', 'Classic Cars', '1:10', 'Classic Metal Creations', 'Turnable front wheels; steering function; detailed interior; detailed engine; opening hood; opening trunk; opening doors; and detailed chassis.', 7305, 98.58, 214.3);

Mysql Database Connection using PHP MySQLI

I will create connection.php file and added the below code into this file.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";
$limit = 6;

$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());
/* check connection */if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
} 
?> 

As you can see, I have passed following mysql database parameters.

$servername : MySQL server hostname.
$username : MySQL Database user name.
$password : MySQL Database password.
$dbname : MySQL Database name.

Added below code into response.php file. We will fetch data from MySQL database table using mysqli.

<?php
include('connection.php');

if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; };  
$start_from = ($page-1) * $limit;  
  
$sql = "SELECT * FROM products ORDER BY productCode ASC LIMIT $start_from, $limit";  
$rs_result = mysqli_query($conn, $sql); 
?>

<?php  
while ($row = mysqli_fetch_assoc($rs_result)) {
?>  

<div class="clearfix card" style="width: 20rem;">
 <img class="card-img-top" src="http://placehold.it/150x100" alt="">

<div class="card-body">

<h4 class="card-title"><?php echo $row['productName']?></h4>

   
<?php echo $row['productLine']?>
</div>


<div class="card-footer">
   <a href="#" class="btn btn-primary">Find Out More!</a>
</div>

</div>

<?php  
};  
?>

We have included 'connection.php' file into the top of response.php, that used to fetch data from the MySQL database. I am creating SQL Query using $start_from and $limit variable, that helps to fetch chunks of data from database.

We will create html table listing with pagination links into index.php, we are using bootstrap to create HTML table grid and pagination nav.

<div id="target-content" class="clearfix">
</div>

We will add jquery code to render first time data,

<script type="text/javascript">
$(document).ready(function(){
 jQuery("#target-content").load("response.php?page=1");
})
</script>

How To Create Pagination navigation

To create a pagination nav link into the listing page, we will fetch all data from products table and store. We’ll count all rows into a php $total_records variable. We will add below code into top of the index.php file,

include('connection.php');

//for total count data
$countSql = "SELECT COUNT(productCode) FROM products";  
$tot_result = mysqli_query($conn, $countSql);  

 
$row = mysqli_fetch_row($tot_result);  
$total_records = $row[0];  
$total_pages = ceil($total_records / $limit);

We have total number of records of products table, now we will add number pagination link at the bottom of table list grid into index.php file.

<ul class="pagination" id="pagination">
  <?php if(!empty($total_pages)):for($i=1; $i<=$total_pages; $i++):  
   if($i == 1):?>

  <li class="page-item active" id="<?php echo $i;?>"><a href="response.php?page=<?php echo $i;?>" class="page-link"><?php echo $i;?></a></li>

   <?php else:?>

  <li id="<?php echo $i;?>" class="page-item"><a href="response.php?page=<?php echo $i;?>" class="page-link"><?php echo $i;?></a></li>

   <?php endif;?> 
  <?php endfor;endif;?> 
</ul>

Now, we will add jQuery call to render particular page records and bind with in target div container into index.php file.

jQuery("#pagination li").on('click',function(e){
 e.preventDefault();
 jQuery("#target-content").html('loading...');
 jQuery("#pagination li").removeClass('active');
 jQuery(this).addClass('active');
        var pageNum = this.id;
        jQuery("#target-content").load("response.php?page=" + pageNum);
});

Added some styling on index.php page using css,

<style type="text/css">
 .card {
  height: 260px;
  width: 20%;
  margin-top:10px;
  margin-right:10px;
  float: left
 }
 .page-container {
  margin-top: 20px;
 }
</style>

We have created a simple product grid view using bootstrap HTML list and bootstrap card, also added pagination with php grid view using jQuery and PHP. I am doing AJAX based pagination using jQuery.