CODEWITHSUNDEEP
phpmysql
Václav Zeman
Václav Zeman

Reputation: 616

How to filter by price ranges in PHP / MySQL?

I'm trying to create a function that will filter out products based on price ranges.

A user can select more price ranges at once (via checkboxes). The checkboxes looks like this:

    <label>0-500 <input type="checkbox" name="price[]" value="1"/></label>
    <label>500-1000 <input type="checkbox" name="price[]" value="2"/></label>
    <label>1000-2000 <input type="checkbox" name="price[]" value="3"/></label>

When the user submits the filter form with 2 checkboxes checked, it sends me an array which looks like this [0 => "2", 1 => "3"].

Then I'd filter the products via SQL query (SELECT * FROM ... WHERE price > 500 AND price < 2000 // very simplified).

The thing is I don't know how to properly get those price ranges from the array, which doesnt include it specifically.

I would highly appreciate your help. Thanks in advance!

Upvotes: 1

Views: 7791

Answers (2)

smftr
smftr

Reputation: 955

try this,

//parameter like [0 => "2", 1 => "3"]
function getQuery($form){
    $query = "SELECT * FROM yourtable WHERE false";
    if (in_array("1", $form)){
        $query .= " OR price >= 0 AND price <= 500";
    }
    if (in_array("2", $form)){
        $query .= " OR price >= 500 AND price <= 1000";
    }
    if (in_array("3", $form)){
        $query .= " OR price => 1000 AND price <= 2000";
    }
    return $query;
}

the function will return the query based on your checkboxes array

Edit

(with Aris idea)

//parameter like [0 => "500-1000", 1 => "1000-2000"]

function getQuery($form){
    $query = "SELECT * FROM yourtable WHERE false";
    foreach ($form as $range){
        $ranges = explode("-", $range);
        $from = ranges[0];
        $to = ranges[1];
        $query .= " OR BETWEEN $from AND $to";
    }
    return $query;
}

Upvotes: 1

Aris
Aris

Reputation: 5057

You need to map it somehow. For example value="1" means "0-500".

Another solution is to set the value to the actual range, then parse it and get the minimum and maximum prices:

    <label>0-500 <input type="checkbox" name="price[]" value="0-500"/></label>
    <label>500-1000 <input type="checkbox" name="price[]" value="500-1000"/></label>
    <label>1000-2000 <input type="checkbox" name="price[]" value="1000-2000"/></label>

In this case your array will look like below:

[0 => "500-1000", 1 => "1000-2000"]

Parse the strings and insert in the sql query

Upvotes: 1

Related Questions