Filter products by price in PHP & MySQL

To filter products by price in PHP & MySQL, You will have to follow the given steps that are very simple to learn and understand.

Filtering products by price is a common feature in e-commerce websites that allows users to narrow down their search based on their budget. So, We’ll cover everything from setting up your database to writing code that lets users easily find products within their budget.

With step-by-step guidance on both the server and user interface sides, you’ll be able to upgrade your website with a handy price filter in no time.

You can also learn the following examples –

Table of Contents

Steps to Filter Products by Price using PHP & MySQL

Now, Let’s start coding to filter products by price withe the given some simple steps –

1. Create a Directory Structure

First of all, You should create the following directory structure to filter prices from low to high.

source-code/
   |__database.php
   |__index.php
   |__Products.php
   |

2. Create MySQL Table

Now, Create a MySQL database

CREATE DATABASE myproject;

Create a table with the name of the products

CREATE TABLE `products` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `productName` varchar(255) DEFAULT NOT NULL,
 `price` int(20) DEFAULT NOT NULL
);

3. Insert Data with Price in Table

Now, You have to insert some data with price into the product table.

INSERT INTO 
  products(id, productName, price)
VALUES
  (1', 'product one','25.00'),
  (2', 'product-2','30.00'),
  (3', 'product-3','80.00'),
  (4', 'product-4','150.00'),
  (5', 'product-5','100.00');

4. Connect to MySQL Database

File Name – database.php

<?php


$host = "localhost";
$user = "root";
$password = "";
$database = "myproject";

$conn = new mysqli($host, $user, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

5. Filter Products with Price Range Input

This code allows users to filter and display products based on a specified price range using a simple HTML form and PHP logic

File Name – index.php

<?php
require_once('database.php');
require_once('Products.php');
// Handle form submission
if (isset($_POST['submit'])) {
   
    $minPrice = $_POST['minPrice'];
    $maxPrice = $_POST['maxPrice'];

    $products = new Products($conn);
    $filteredProducts = $products->filterProductsByPrice($conn, $minPrice, $maxPrice);
} else {
    // Set default price range
    $minPrice = '';
    $maxPrice = '';
    // Display all products initially
    $filteredProducts = [];
}

?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Product Filter</title>
</head>
<body>

<h2>Product Filter by Price</h2>

<form method="post">
    <label>Min Price:</label>
    <input type="number" name="minPrice" value="<?=$minPrice; ?>" min="0">

    <label>Max Price:</label>
    <input type="number" name="maxPrice" value="<?=$maxPrice; ?>" min="0">

    <input type="submit" value="Filter" name="submit">
</form>

<br></br>
<table border="1" cellspacing="0" cellpadding="5" width="42%">
    <thead>
    <tr>
        <th>Product Name</th>
        <th>Price</th>
    </tr>
    </thead>
    <tbody>
    <?php
    // Display filtered products in a table
    foreach ($filteredProducts as $product) {
        echo "<tr><td>{$product['productName']}</td><td>{$product['price']}</td></tr>";
    }
    ?>
    </tbody>
</table>

</body>
</html>

Explanation –

The code initiates a connection to the database by requiring a file named database.php.
It includes a file called Products.php, presumably containing a class definition for handling products.
The code checks if a form has been submitted, retrieving minimum and maximum price values from the form’s POST data when submitted.
An instance of the Products class is created, and the filterProductsByPrice method is called to filter products based on the provided price range.
Default values for minimum and maximum prices are set, along with an empty array for filtered products if the form is not submitted.
The HTML form is displayed with input fields for users to enter minimum and maximum price values, allowing them to filter products.
An HTML table is rendered with headers for “Product Name” and “Price” to display the filtered products.
The table body is populated using a PHP loop, iterating through the filtered products and generating table rows with product names and prices.

6. Filter Products by Price from the Database

The code defines a PHP class “Products” for handling product-related operations, including a method to filter products by price range using a provided database connection.

File Name – Products.php

<?php

class Products {

    private $conn;
    private $adminTable = 'singleImage';
    

    public function __construct($conn) {
        $this->conn = $conn;
    }

    function filterProductsByPrice($conn, $minPrice, $maxPrice) {
        $filteredProducts = [];
    
        $sql = "SELECT * FROM products WHERE price BETWEEN $minPrice AND $maxPrice";
        $result = $conn->query($sql);
    
        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $filteredProducts[] = $row;
            }
        }
    
        return $filteredProducts;
    }


}



?>

Explanation –

The code defines a PHP class named “Products” with private properties for database connection and an admin table name.
The class constructor initializes the connection property with a provided database connection.
The class has a method named “filterProductsByPrice” that takes a connection, minimum price, and maximum price as parameters and returns an array of products filtered by the specified price range.
Inside the method, a SQL query is constructed to select products from a table named “products” where the price falls within the given range.
The query result is processed, and if there are matching rows, they are fetched into an array called $filteredProducts.
Finally, the array of filtered products is returned.

Suggestion

I hope you learned to filter products by price with PHP. Now, You can easily integrate it into your project So that users can filter products within the custom price range.