php mysql query: multiple drop down options

Question

I have a search box that the user can select a $location a $type and a $rating.

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

This works fine if the user selects and option from all 3 drop down boxes- however how do I make the msql query check the database if the user only selects a location for example.

I have a "Any" option on all 3 drop downs incase they wish to leave a dropdown blank.

Thanks

Answer 1

$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()); 
}

Need to make sure there is a search criteria set before running the SQL though.

Answer 2

I run into this all the time and this set up won't work. It fails when only one option is chosen. The query becomes:

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

The easy fix is to simply at WHERE 1 to the beginning of the query and then you can add the AND 'Rating' = '$rating' etc. in any way you find most convenient.

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

Answer 3

Based on Aaron W. answer, here is a solution that retrieves all rows when all options are '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";

Answer 4

Build the query dymanically instead, adding conditions one at a time. If "Any" is selected, then don't include that part of the condition.

Answer 5

$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());

Also, this is a SQL INJECTION disaster. Please use an escaping function, such as mysql_real_escape_string.

Answer 6

$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 .= ';';