php mysql查询:多个下拉选项

$searches = array();
if($location != 'any') $searches[] = "`Location` = '$location'";
if($type != 'any') $searches[] = "`Type` = '$type'";
if($rating != 'any') $searches[] = "`Rating` = '$rating'";
if(count($searches) > 0) {
    $result = mysql_query("SELECT * FROM Places WHERE " . implode(" AND ", $searches)) or die(mysql_error()); 
}

但在运行SQL之前,需要确保设置了搜索条件。

我一直碰到这个,这个设置不起作用。当只选择一个选项时失败。查询变成:

SELECT * FROM Places WHERE AND 'Rating' = '$rating'

简单的解决方法是在 WHERE 1 到查询的开头,然后可以添加 AND 'Rating' = '$rating'

$sql .= ($location)?" AND Location='$location'":"";
$sql .= ($type)?" AND Type='$type'"":"";
$sql .= ($rating)?" AND Rating='$rating'":"";

基于Aaron W.answer,这里有一个解决方案,在所有选项都为“any”时检索所有行:

$sql = "SELECT * FROM Places";
$searches = array();
if ($location != 'any') $searches[] = "`Location` = '$location'";
if ($type     != 'any') $searches[] = "`Type` = '$type'";
if ($rating   != 'any') $searches[] = "`Rating` = '$rating'";
if (count($searches) > 0) {
    $sql .= " WHERE " . implode(" AND ", $searches);
}
$sql .= ';';
echo "sql=$sql\n";

$result = mysql_query("
    SELECT * 
    FROM 
        Places 
    WHERE 
        (Location = '$location' OR '$location' = 'Any') 
    AND
        (Type ='$type' OR '$type' = 'Any')
    AND  
        (Rating = '$rating' OR '$rating' = 'Any')
")
or die(mysql_error());

还有,这是一个 SQL INJECTION 灾难请使用转义函数,例如 mysql_real_escape_string .

$query = 'SELECT * FROM Places ';
if($location != "Any" || $type != "Any"|| $rating != "Any")
    $query .= 'WHERE ';
if($location != "Any")
   $query .= 'location = "'.mysql_real_escape_string($location).'" ';
if($type!= "Any")
   $query .= 'type= "'.mysql_real_escape_string($type).'" ';
if($rating!= "Any")
   $query .= 'rating= "'.mysql_real_escape_string($rating).'" ';
$query .= ';';