Hey Technoz, In this tutorial we are going to learn how to filter contents and filter product using php and mysql. It helps user to view content on the webpage as per their preference also its save user time. Using filter feature user can find the relevant data from the numerous product list. In this tutorial we are going to deal with book as a product. In which we are going to create filters for category of book, price range and published year.
Creating database
To filter contents database need to be create to store the information about products so that we can filter products later.
So lets create the database by using following queries. The following SQL creates filter table which consist of title, category, price, year and cover(name of image) columns.
CREATE TABLE `filter` ( `title` varchar(500) NOT NULL, `category` varchar(500) NOT NULL, `price` int(255) NOT NULL, `year` year(4) NOT NULL, `cover` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Dumping data for table ‘counter’
Now lets insert some records by using following queries.
INSERT INTO `filter` (`title`, `category`, `price`, `year`, `cover`) VALUES
('Say you are sorry', 'mystery', 3, 2017, 'syas.jpg'),
('You will know me', 'mystery', 4, 2016, 'ywkm.jpg'),
('Say you are sorry', 'adventure', 7, 2017, 'origin.jpg'),
('New York', 'fiction', 8, 2016, 'Allthebird.jpg'),
('The Outsider', 'mystery', 10, 2018, 'toutsider.jpg'),
('You will know me', 'adventure', 11, 2016, 'nfg.jpg'),
('The Outsider', 'adventure', 12, 2018, 'fallen.jpg'),
('The gone world', 'fiction', 14, 2017, 'ny2140.jpg'),
('All the birds in sky', 'fiction', 16, 2018, 'tgw.jpg');
First, To make it convenient, We will create a new PHP file for database configuration named dbconfig.php that holds all configured parameters:
<?php
$con = mysqli_connect("localhost","root","","technopoints");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>
Filter Contents and Filter products using PHP
The following file index.php is used to build user interface of Visit to filter contents and filter products.
<!DOCTYPE html>
<html lang="en">
<head>
<title>Filter Tutorial on Technopoints</title>
<meta charset="utf-8">
<meta 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">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<style>
/* Responsive layout - makes the three columns stack on top of each other instead of next to each other */
@media screen and (max-width: 500px) {
.column {
width: 100%;
}
}
.thumbnail img{
max-width: 100%; /* do not stretch the bootstrap column */
}
.img-wrapper{
width: 100%;
padding-bottom: 100%; /* your aspect ratio here! */
position: relative;
}
.img-wrapper img{
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
min-height: 50%;
max-height: 100%;
min-width:100%/* optional: if you want the smallest images to fill the .thumbnail */
}
</style>
</head>
<body>
<div class="container">
<h2>Technopoints: Filter</h2>
<p>From below dropdown menus you can apply filter</p>
<form action="" method="post">
<div class="row">
<div class="col-sm-3">
<div class="form-group">
<select class="form-control" name="category">
<option value="">Select Category</option>
<option value="fiction">Science Fiction</option>
<option value="Mystery">Mystery</option>
<option value="Adventure">Adventure</option>
</select>
</div>
</div>
<div class="col-sm-3">
<div class="form-group">
<select class="form-control" name="year">
<option value ="">Year of Publishing</option>
<option value="2016">2016</option>
<option value="2017">2017</option>
<option value="2018">2018</option>
</select>
</div>
</div>
<div class="col-sm-3">
<div class="form-group">
<select class="form-control" name="price">
<option value="">Select Price Range</option>
<option value="range1">01$ - 05$</option>
<option value="range2">05$ - 10$</option>
<option value="range3">10$ - 15$</option>
</select>
</div>
</div>
<button type="submit" class="btn btn-primary">Apply</button>
</div>
</form>
</div>
<center><h2>Apply Filter To Check Result</h2><br><center>
<div class="container-fluid">
<div class="row"><?php
error_reporting( error_reporting() & ~E_NOTICE );
if(isset($_POST['price'])) { if($_POST['price']=='range1'){
$low = 1; $high = 5;
}
if($_POST['price']=='range2'){
$low = 5; $high = 10;
}
if($_POST['price']=='range3'){
$low = 10; $high = 15;
} }
@$category = $_POST['category'];
@$year = $_POST['year'];
//@$price = $_POST['price'];
include 'dbconfig.php';
if(isset($_POST['price']) && isset($_POST['category']) && isset($_POST['year'])) { $qry = "SELECT * FROM filter
WHERE price BETWEEN $low AND $high AND year = $year AND category ='$category'"; }
if(isset($_POST['price']) && isset($_POST['category']) && $_POST['year']==NULL){
$qry = "SELECT * FROM filter
WHERE price BETWEEN $low AND $high AND category ='$category'";
}
if(isset($_POST['category']) && isset($_POST['year']) && $_POST['price']==NULL){
$qry = "SELECT * FROM filter
WHERE year = $year AND category ='$category'";
}
if(isset($_POST['price']) && isset($_POST['year']) && $_POST['category']==NULL){
$qry = "SELECT * FROM filter
WHERE price BETWEEN $low AND $high AND year = $year";
}
if(isset($_POST['category']) && $_POST['year']==NULL && $_POST['price']==NULL){
$qry = "SELECT * FROM filter
WHERE category ='$category'";
}
if($_POST['category']==NULL && isset($_POST['year']) && $_POST['price']==NULL){
$qry = "SELECT * FROM filter
WHERE year =$year";
}
if($_POST['category']==NULL && $_POST['year']==NULL && isset($_POST['price'])){
$qry = "SELECT * FROM filter
WHERE price BETWEEN $low AND $high";
}
if($_POST['category']==NULL && $_POST['year']==NULL && $_POST['price']==NULL){
$qry = "SELECT * FROM filter";
}
$result = mysqli_query($con,$qry);
$num = mysqli_num_rows($result);
if($num > 0) while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){ ?>
<!-- item -->
<div class="col-xs-4 col-sm-2">
<div class="thumbnail">
<div class="img-wrapper">
<img src="cover/<?php echo $row['cover'];?>" alt="400">
</div>
<div class="caption text-center">Title:<?php echo $row['title'];?></div><!-- optional -->
<div class="caption text-center">Publishing Year:<?php echo $row['year'];?></div>
<div class="caption text-center">Category:<?php echo $row['category'];?></div>
<div class="caption text-center">Price:<?php echo $row['price'].'$';?></div>
</div>
</div>
<?php }else{
echo "<h4>No Result Found</h4>"; } ?>
</div>
</div>
</body>
</html>
In this tutorial we have learn to Filter Contents and Filter products using PHP and MySQL in the web application .
If you have any doubts regarding to this tutorial feel free to ask in the comment section below.
To catch even more knowledgeable and easy understandable tutorials please subscribe to get email notification.