How to Filter Prices from Low to High in PHP and MySQL?
How often do you want to filter the prices from low to high in PHP and MySQL? Your answer would probably be – very often. So, if you know how to do it, you’re good to go, but if you’re still wondering how to do it, then this tutorial will really be very helpful for you.
In this comprehensive guide, we will not only demonstrate the steps to filter prices from low to high in PHP and MySQL but also share the PHP filter price source codes with examples. So, read this entire tutorial without skipping.
How Do Price Filters Work in PHP?
Before we move forward to demonstrate the step-by-step process, it is imperative to understand how Price filters actually work in PHP.
The filter price has two choices in the select options input fields:
- The first choice is ‘Price low to high
- The second available option is ‘Price high to low
To sort the prices in ascending order (from low to high), you need to choose the first option ‘price low to high‘ and then click the submit button. Then you will see the sorted price in the HTML table.
To sort the prices in descending order (from high to low), you need to choose the second option ‘price high to low’ and then click the submit button. Then you will see the sorted price in the HTML table
Step-by-Step Process to Filter Prices from Low to High in PHP and MySQL
Let’s start the most-awaited tutorial to filter prices from Low to High in PHP and MySQL:
Step 1: Create a Directory Structure
We will start with creating a directory structure. It will filter the prices from Low to High.
source-code/ |__database.php |__index.php |__filter-price.php |__display-price.php |
Step 2: Create MySQL Table
The next step is to create a database on MySQL.
CREATE DATABASE codingstatus;
The name of the table should be similar to the product.
CREATE TABLE `products` ( `id` int(10) NOT NULL AUTO_INCREMENT, `productName` varchar(255) DEFAULT NOT NULL, `price` int(20) DEFAULT NOT NULL );
Step 3: Insert Data with Price in Table
Now, insert the data and prices into the product table.
INSERT INTO
products(id, productName, price)
VALUES
(1', 'product-1','45'),
(2', 'product-2','60'),
(3', 'product-3','70'),
(4', 'product-4','10'),
(5', 'product-5','110'),
(6', 'product-6','80'),
(7', 'product-7','90'),
(8', 'product-8','65'),
(9', 'product-9','4'),
('10', 'product-10','120');
Step 4: Setup MySQL Database Connection
The following PHP script will help you to connect your PHP to the MySQL database –
We have defined the table name in the constant variable so that we can use it on any page globally.
define('productTable', 'products');
File Name – database.php
<?php
define('productTable', 'products');
$hostname = "localhost";
$username = "root";
$password = "";
$databasename = "codingstatus";
$conn = mysqli_connect($hostname, $username, $password, $databasename);
if (!$conn) {
die("Unable to Connect database: " . mysqli_connect_error());
}
?>
Step 5: Create a Form to Filter Price
Now, create the price filtering UI by following these steps –
- First, you need to write a basic HTML code that will define the structure of your web page.
- Second, you need to add database.php and filter-price.php files that will handle the database connection and the price filtering logic.
- Third, you need to make a form that will send the user’s choice of price order to the server.
- Fourth, you need to give a name to the select input field, which is filterPrice. You also need to create two options for the user to choose from, which are priceAsc and priceDesc.
- Fifth, you need to create a submit button that will send the form data to the server.
- Sixth, you need to include another file that will display the data from the database according to the user’s choice of price order. This file is called display-data.php.
File Name – index.php
<?php
include("database.php");
include("filter-price.php");
?>
<form method="post">
<select name="filterPrice">
<option value="priceAsc" <?php echo $_POST['filterPrice']=='priceAsc'?'selected':''; ?>>Price Low to High</option>
<option value="priceDesc" <?php echo $_POST['filterPrice']=='priceDesc'?'selected':''; ?>>Price High to Low</option>
</select>
<input type="submit" name="filter">
</form>
<?php
include("display-data.php");
?>
Step 6: Filter Data by Category in PHP
The next step is to create a price-filtering script using the following points –
- First, you need to verify if the form is submitted or not to filter the price using $_POST[‘filter’] inside the if condition. If the form is submitted then invoke the function filterPriceData() and store it in the $filterPriceData variable.
- Second, you need to create a custom function filterPriceData() and implement the following points inside it
- Get the filtered price input value using $_POST[‘filterPrice’] and store it in the $filterPrice variable.
- Declare $conn with the keyword global to use it throughout the function.
- If $filterPrice is not empty then execute the following points inside the if condition.
- Store a value ‘DESC’ in the variable $orderby
- If $filterPrice is equal to ‘priceAsc’ then store a value ‘ASC’ in the $orderby.
- Write a MySQLi query to select data with a price based on the value of $orderby
- If records is more than zero then fetch the records with the $result->fetch_all(MYSQLI_ASSOC) and store it in the $data variable.
- After that, return the value of $data.
File Name – filter-price
<?php
if(isset($_POST['filter'])) {
$filterPriceData = filterPriceData();
}
function filterPriceData() {
$filterPrice = $_POST['filterPrice'];
global $conn;
$data =[];
if(!empty($filterPrice)){
$orderby = 'DESC';
if($filterPrice == 'priceAsc'){
$orderby = 'ASC';
}
$query = "SELECT productName, price FROM ".productTable;
$query .= " ORDER BY price ". $orderby;
$result = $conn->query($query);
if($result->num_rows > 0) {
$data = $result->fetch_all(MYSQLI_ASSOC);;
}
}
return $data;
}
Step 7: Display Data with Price in Table
To show data with the price in the HTML table when the filter price and submit the form, you need to do these steps:
- If the value of $filterPriceData is not empty then do the next all steps inside it
- Make the first row of the table with three columns S.N, Product, price
- Then make other rows dynamically based on the filter price data.
- Use the forearch loop to the $filterPriceData and display the value of columns ‘productName’ and ‘price’ accordingly.
File Name – display-data.php
<?php
if(!empty($filterPriceData)) {
?>
<table border="1" cellspacing="0" cellpadding="5">
<tr>
<th>S.N</th>
<th>Product</th>
<th>Price</th>
</tr>
<?php
foreach($filterPriceData as $filterData){
$sn = 1;
?>
<tr>
<td><?php echo $sn; ?></td>
<td><?php echo $filterData['productName']?></td>
<td><?php echo $filterData['price']?></td>
</tr>
<?php
$sn++; }
?>
</table>
<?php
}
?>
Conclusion
After implementing the steps listed above, you can filter prices from Low to high in Php and MySQL. You can also test it in your web browser. Here’s how you can test the functionality:
You will see a select form with two options – ‘Price Low to High’ and ‘Price High to Low.’
- When you choose the price low to high option and submit the form then the data with price will show in ascending order in the shape of a table.
- When you choose the price high to low option and submit the form then the data with price will show in descending order in the shape of a table.
Hope you find this guide best to your interest. We will continue coming back with more interactive blogs and tutorials related to PHP.
