使用SQL命令创建价格范围的下拉框

Question

The Problem

I am having trouble filtering my table, I have filtered it for product category but I am unsure how to do it when looking at numbers.

Expected Outcome

What I expect to see when the user presses the submit button is for the table to change to what ever it was that submitted. For example, in the drop down box there is an option for '0 - 1', this meaning at price range less than a pound. If I was to press this I would expect to see the table change, showing only products with a price less than 1 pound.

What the actual outcome is

At the moment I am getting different errors every time I try something new, I first attempted to copy the category option but that didn't work.

Could I do something like this?

For the drop down box

<form action="database.php" method="post">
<select name="price" id="price">
    <option value="?">All Products</option>
    <option value="?">Less than £1</option>
    <option value="?">More than £1, Less than £5</option>
    <option value="?">More than £5, Less than £10</option>
</select>
<input  type="submit" name="submit" value="Search"/>
</form>

Would not know what to put for the value.

The SQL code

$'?' = pg_query("SELECT Foodtype, Manufacturer, 
    Description, Price FROM food WHERE Price = BETWEEN 0.00 AND 1.00");

$'?' = pg_query("SELECT Foodtype, Manufacturer, 
    Description, Price FROM food WHERE Price = BETWEEN 1.00 AND 5.00");

$'?' = pg_query("SELECT Foodtype, Manufacturer, 
    Description, Price FROM food WHERE Price = BETWEEN 5.00 AND 10.00");

Here is my current code for implement the table

<?php
    $conn = pg_connect("host=hostname.com port=1234
                                        dbname=******* user=guest password=********");
    // Empty var that will be populated if the form is submitted
    $where = '';
        if (isset($_POST['submit'])) {
            if (!empty($_POST['category'])) {
        // Where conditional that will be used in the SQL query
        $where = " WHERE Category = '".pg_escape_string($_POST['category'])."'";
            }
        }
        $res = pg_query($conn, "SELECT Foodtype, Manufacturer, Description, Price 
        FROM food " . $where . " ORDER BY Category ASC");
            echo "<table id=\"myTable\" border='1'>";
            while ($a = pg_fetch_row($res)) {
            echo "<tr>";
        for ($j = 0; $j < pg_num_fields($res); $j++) {
        echo "<td>" . $a[$j] . "</td>";
        }
        echo "<td><form id='cart' name='cart' method='POST' action='addToBasket.php'>
        <input type='submit' name='Select' id='Select' value='Add To Basket'>
        </form></td>";
        echo "</tr>
";
        }
    echo "</table>
";

I will need another Where conditional

$where = " WHERE Price= '".pg_escape_string($_POST['price'])."'";

Is there a better way to do a filter for price range?

Answer 1

First php isnt my strength so take it just as guidelines.

Just put some id number for each option

<select name="price" id="price">
    <option value="1">All Products</option>
    <option value="2">Less than £1</option>
    <option value="3">More than £1, Less than £5</option>
    <option value="4">More than £5, Less than £10</option>

Then you have the where

$where = " WHERE Category = '".pg_escape_string($_POST['category'])."'";

switch ($_POST['price']) {
     case 2: 
           $where = $where." and Price BETWEEN 0.00 AND 1.00";
           break;
     case 3: 
           $where = $where." and Price BETWEEN 1.00 AND 5.00";
           break;
     case 4: 
           $where = $where." and Price BETWEEN 5.00 AND 10.00";
           break;
     default:
           break;
}

$res = pg_query($conn, "SELECT Foodtype, Manufacturer, Description, Price 
    FROM food " . $where . " ORDER BY Category ASC");

NOTE:

BETWEEN works like 0.00 <= price AND price <= 1.00 so if one item price is exactly 1.00 will also appear on both the first and second range.

NOTE 2:

A better option is create a table PriceRange in database this way you can update or add new ranges very easy without have to change the php page.

RangeID    LowerPrice   UpperPrice
  1            0         1.000.000.000
  2            0              1
  3            1              5 
  4            5             10

Your query will be something like

SELECT Foodtype, Manufacturer, Description, Price 
FROM food f
JOIN PriceRange p
  ON f.Price BETWEEN p.LowerPrice AND p.UpperPrice
WHERE Category = $_POST['category']
  AND RangeID = $_POST['price']