CODEWITHSUNDEEP
phpmysqldatabase
AJFMEDIA
AJFMEDIA

Reputation: 2123

php mysql query: multiple drop down options

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

Upvotes: 2

Views: 2454

Answers (6)

Toto
Toto

Reputation: 91415

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

Upvotes: 1

d2burke
d2burke

Reputation: 4111

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'":"";

Upvotes: 1

Tokk
Tokk

Reputation: 4502

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

Upvotes: -1

cypher
cypher

Reputation: 6992

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

Upvotes: 0

Aaron W.
Aaron W.

Reputation: 9299

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

Upvotes: 1

Michael Madsen
Michael Madsen

Reputation: 55009

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

Upvotes: 0

Related Questions