가격 슬라이더로 제품 검색, Creating Price Range Slider using jQuery in PHP with MySQL > php

출처 : https://www.codexworld.com/price-range-slider-jquery-ajax-php-mysql/

Range slider is very useful to add filter feature to the data list. Range slider mostly used to filter data by price range. Price range slider allows the user to filter the recordset by selecting price range instead of entering the price manually.

In this tutorial, we’ll create a price range slider using jQuery and add filter functionality to the data list with PHP & MySQL. The price filter is a required feature in products listing and range slider is a perfect choice for price filter. Here we’ll show how you can easily add a price range slider to the products list using jQuery in PHP and filter products by price range using jQuery, Ajax, PHP & MySQL.

Database Table Creation

To store the products data a table need to be created in the database. The following SQL creates a products table with some required filed in MySQL database.

CREATE TABLE `products` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `price` float(6,2) NOT NULL,
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL,
 `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Configuration (dbConfig.php)

This file is used to connect and select MySQL database. Specify your database credentials, host ($dbHost), username ($dbUsername), password ($dbPassword), and databse name ($dbName).

<?php
// Database configuration
$dbHost     = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName     = "codexworld";

// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}
?>

The jQuery is used to create range slider and implement ajax filter functionality, so, include the jQuery library first.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>

Create Price Range Slider

jRange is a light weight jQuery plugin that will help you to convert a input field to range slider. So, we’ll use jRange jQuery plugin to create range selector. Include the jRange JS and CSS library.

<link rel="stylesheet" href="jquery.range.css">
<script src="jquery.range.js"></script>

Now, place the following code after the price range input field, it will replace the price_range input field with a range slider.

<script>
$('.price_range').jRange({
    from: 0,
    to: 500,
    step: 50,
    format: '%s USD',
    width: 300,
    showLabels: true,
    isRange : true
});
</script>

jRange provides various configuration options to configure the price range slider based on your needs. All available options can be found from here.

Products List with Price Range Slider

Initially, all the products are fetched from the database and listed in the index.php file. Also, a price range slider will be displayed at the top of the list for filtering the products by price.

<div class="container">
    <div class="filter-panel">
        <p><input type="hidden" class="price_range" value="0,500" /></p>
        <input type="button" onclick="filterProducts()" value="FILTER" />
    </div>
    <div id="productContainer">
        <?php
        //Include database configuration file
        include('dbConfig.php');
        
        //get product rows
        $query = $db->query("SELECT * FROM products ORDER BY created DESC");
        
        if($query->num_rows > 0){
                while($row = $query->fetch_assoc()){
            ?>
                <div class="list-item">
                    <h2><?php echo $row["name"]; ?></h2>
                    <h4>Price: <?php echo $row["price"]; ?></h4>
                </div>
        <?php }
        }else{
            echo 'Product(s) not found';
        } ?>
    </div>
</div>

Filter by the Price Range

JavaScript Code:
The filterProducts() function sends the price range to the getProducts.php file using Ajax and displays the filtered products data under the productContainer div.

<script>
function filterProducts() {
    var price_range = $('.price_range').val();
    $.ajax({
        type: 'POST',
        url: 'getProducts.php',
        data:'price_range='+price_range,
        beforeSend: function () {
            $('.container').css("opacity", ".5");
        },
        success: function (html) {
            $('#productContainer').html(html);
            $('.container').css("opacity", "");
        }
    });
}
</script>

The filterProducts() function is triggered by clicking FILTER button.

PHP Code: (getProducts.php)
The getProducts.php file is requested by the filterProducts() function. In this file, products data is fetched from the database based on the given price range using PHP and MySQL. The filtered data will be rendered and returned to the Ajax success method.

<?php
if(isset($_POST['price_range'])){
    
    //Include database configuration file
    include('dbConfig.php');
    
    //set conditions for filter by price range
    $whereSQL = $orderSQL = '';
    $priceRange = $_POST['price_range'];
    if(!empty($priceRange)){
        $priceRangeArr = explode(',', $priceRange);
        $whereSQL = "WHERE price BETWEEN '".$priceRangeArr[0]."' AND '".$priceRangeArr[1]."'";
        $orderSQL = " ORDER BY price ASC ";
    }else{
        $orderSQL = " ORDER BY created DESC ";
    }
    
    //get product rows
    $query = $db->query("SELECT * FROM products $whereSQL $orderSQL");
    
    if($query->num_rows > 0){
        while($row = $query->fetch_assoc()){
    ?>
            <div class="list-item">
                <h2><?php echo $row["name"]; ?></h2>
                <h4>Price: <?php echo $row["price"]; ?></h4>
            </div>
    <?php }
    }else{
        echo 'Product(s) not found';
    }
}
?>

Conclusion

Using our source code, you can easily integrate price range slider filter to the products list. If you want to modify the range slider design, you can easily do it by modifying the jquery.range.css file. All source code files has been included in our downloadable package.