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

Did you ever find a resolution to this?

d2burke
– d2burke

2010-09-30 03:35:36 +00:00
Commented Sep 30, 2010 at 3:35 — d2burke
– d2burke, Commented Sep 30, 2010 at 3:35

Aaron W. · Accepted Answer · 2010-09-17 11:46:19Z

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.

answered Sep 17, 2010 at 11:46

Aaron W.

9,2952 gold badges36 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RobertPitt Over a year ago

What if $searches[] are empty ?

Aaron W. Over a year ago

I left that up to the OP, but M42 handled one scenario: stackoverflow.com/questions/3734955/…

d2burke · Accepted Answer · 2010-09-17 12:00:58Z

1

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

edited Sep 17, 2010 at 12:00

answered Sep 17, 2010 at 11:55

d2burke

4,1213 gold badges41 silver badges52 bronze badges

2 Comments

Aaron W. Over a year ago

if $searches count is only one, it won't put the AND anywhere. It will just be WHERE Location = '$location'. It will break if there aren't any values in the array, that's why it's being checked.

d2burke Over a year ago

I see, my apologies Aaron (quip removed)

Toto · Accepted Answer · 2010-09-17 12:06:29Z

1

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

answered Sep 17, 2010 at 12:06

Toto

91.7k63 gold badges97 silver badges135 bronze badges

Comments

Michael Madsen · Accepted Answer · 2010-09-17 11:44:54Z

0

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

answered Sep 17, 2010 at 11:44

Michael Madsen

55.1k8 gold badges75 silver badges83 bronze badges

Comments

cypher · Accepted Answer · 2010-09-17 11:49:22Z

0

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

answered Sep 17, 2010 at 11:49

cypher

7,0024 gold badges34 silver badges48 bronze badges

Comments

Tokk · Accepted Answer · 2010-09-17 11:51:08Z

-1

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

answered Sep 17, 2010 at 11:51

Tokk

4,5223 gold badges35 silver badges48 bronze badges

1 Comment

user243901 Over a year ago

will this work? you havent got "AND" between WHERE parameters and no space seperators...

Collectives™ on Stack Overflow

php mysql query: multiple drop down options

6 Answers 6

2 Comments

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related