php - 使用 SQL 命令为价格范围创建下拉框

Question

问题

我在过滤表格时遇到问题，我已经针对产品类别对其进行了过滤，但在查看数字时我不确定如何进行过滤。

预期结果

当用户按下提交按钮时，我希望看到的是表格更改为提交时的内容。例如，在下拉框中有一个选项“0 - 1”，这意味着价格范围小于一磅。如果我按下此按钮，我希望看到表格发生变化，只显示价格低于 1 英镑的产品。

实际结果是什么

目前，每次我尝试新事物时都会遇到不同的错误，我首先尝试复制类别选项，但没有成功。

我可以做这样的事情吗？

对于下拉框

<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>

不知道该用什么来表示值(value)。

SQL 代码

$'?' = 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");

这是我当前用于实现该表的代码

<?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>\n";
        }
    echo "</table>\n";

我需要另一个 Where 条件

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

有没有更好的方法来过滤价格范围？

Answer 1

首先 php 不是我的强项，所以把它作为指导。

只需为每个选项输入一些编号

<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>

然后你有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");

注意:

BETWEEN像0.00 <= price AND price <= 1.00一样工作所以如果一件商品的价格正好是 1.00也将出现在第一个和第二个范围内。

注意 2:

更好的选择是创建一个表 PriceRange在数据库中，您可以通过这种方式非常轻松地更新或添加新范围，而无需更改 php 页面。

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

您的查询将类似于

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']