SQL 查询空字段，选择“全部”选项，如 * - SQL query empty field, select ALL option like *

一个php搜索页面使用SQL查询根据设置的条件搜索属性，在下面的例子中，选择了"哈里森路"：

search.php?city=1&rooms=3&rooms_upper=7&price_lower=55&price_upper=100&streetname=Harrison%20Road

我正在寻找一种方法将街道名称留空，但仍选择所有记录，如下所示：

 search.php?city=1&rooms=3&rooms_upper=7&price_lower=55&price_upper=100&streetname=

我尝试像其他 SQL ocmmands 一样使用" * "符号来指定 SELECT ALL，但在这种情况下它不起作用。

问题是搜索不显示任何结果，而街道选项留空。

我希望使用空街道条件运行搜索的原因是，搜索.php在用户使用页面上的下拉选项选择特定街道之前加载。

我希望它使用指定的第一个条件搜索所有记录：客房， rooms_upper， price_lower， price_upper

标准搜索页面加载保留了尽可能广泛的搜索条件（3个<房间><7个）和（55英镑><租金><75英镑），以便在用户缩小搜索条件之前显示所有记录，如果需要，指定特定的"街道名称"。>

非常感谢！让克劳德

完整的SQL在这里：

    $sql=mysql_query("SELECT main_table.housenumber, main_table.housenumber, main_table.streetname,
                      max(main_table.rent) AS reviews_rent, main_table.rooms AS reviews_rooms,main_table.average, 
                      houses.houseID, houses.path, houses.rooms AS houses_rooms,houses.rent AS houses_rent
                    FROM main_table 
                      LEFT JOIN houses ON main_table.housenumber = houses.housenumber 
                           AND main_table.streetname = houses.streetname
                    WHERE main_table.streetname='$page_streetname' 
                        AND main_table.city=$city 
                        AND main_table.verify='1' 
                        AND main_table.rooms>='$rooms' 
                        AND main_table.rooms<='$rooms_upper' 
                        AND main_table.rent>=$price_lower 
                        AND main_table.rent<=$price_upper
                    GROUP BY main_table.housenumber, main_table.streetname 
                    ORDER BY average DESC, houseID DESC, reviewID DESC;");

我想在 WHERE 子句中保留街道名称，但如果它留空，我不想限制搜索。

如何动态创建查询。

在 WHERE 子句中设置不带街道名称但包含 sprintf 字符串标记的基本查询。然后，如果 $page_streetname 具有值，则将街道名称选择动态添加到查询中，或者如果不添加任何内容。

$q = "
SELECT main_table.housenumber, 
       main_table.streetname, 
       Max(main_table.rent) AS reviews_rent, 
       main_table.rooms     AS reviews_rooms, 
       main_table.average, 
       houses.houseid, 
       houses.path, 
       houses.rooms         AS houses_rooms, 
       houses.rent          AS houses_rent 
FROM   main_table 
       LEFT JOIN houses 
              ON main_table.housenumber = houses.housenumber 
                 AND main_table.streetname = houses.streetname 
WHERE  %s
       main_table.city = $city 
       AND main_table.verify = '1' 
       AND main_table.rooms >= '$rooms' 
       AND main_table.rooms <= '$rooms_upper' 
       AND main_table.rent >=$ price_lower 
       AND main_table.rent <=$ price_upper 
GROUP  BY main_table.housenumber, 
          main_table.streetname 
ORDER  BY average DESC, 
          houseid DESC, 
          reviewid DESC";
$q = isset($page_streetname) 
          ? sprintf( $q, "main_table.streetname = '$page_streetname' AND " )
          : sprintf( $q, '');

这很容易用 PHP 解决（不需要sprintf()）：

<?php
$page_streetname = empty($page_streetname)
  ? null
  : "AND main_table.streetname = '{$page_streetname}'"
;
$result = mysql_query(
  "SELECT
    main_table.housenumber,
    main_table.housenumber,
    main_table.streetname,
    max(main_table.rent) AS reviews_rent,
    main_table.rooms AS reviews_rooms,
    main_table.average,
    houses.houseID,
    houses.path,
    houses.rooms AS houses_rooms,
    houses.rent AS houses_rent
  FROM main_table
    LEFT JOIN houses
      ON main_table.housenumber = houses.housenumber
        AND main_table.streetname = houses.streetname
  WHERE
    main_table.city = '{$city}'
    AND main_table.verify = 1 
    AND main_table.rooms >= '{$rooms}'
    AND main_table.rooms <= '{$rooms_upper}'
    AND main_table.rent >= {$price_lower}
    AND main_table.rent <= {$price_upper}
    {$page_streetname}
  GROUP BY
    main_table.housenumber,
    main_table.streetname 
  ORDER BY
    average DESC,
    houseID DESC,
    reviewID DESC"
);
?>