Creating a mySQL query using PHP form dropdowns - If user ignores dropdown, do not filter by that parameter

Question

I am creating a simple MySQL query that will be built from the user selecting options from 2 dropdowns.

The issue I am having is that I would like each drop down to default that if they do not actually choose an option, do not filter by that dropdown parameter.

So, if they come in, and simply hit submit without choosing from a dropdown they should see everything. If they come in and pick from only one of the dropdowns, the query will basically ignore filtering by the other dropdown.

I tried making <OPTION VALUE='any'>Choose but my query didn't know what to do with the 'any' and just shows no results.

Here is my code. Thank you very much for whatever help you can provide.

FORM

<form method="POST"  action="<?php echo $_SERVER['REQUEST_URI']; ?>">


<select name="GameType"> 
<OPTION VALUE='any'>Choose Game Type
<option value="Game1">Option 1</option>
<option value="Game2">Option 2</option>
<option value="Game3">Option 3</option>
</select>


<select name="Instructor"> 
<OPTION VALUE='any'>Choose Instructor
<option VALUE="InstructorA">Instructor A</option>
<option value="InstructorB">Instructor B</option>
<option value="InstructorC">Instructor C</option>
</select>

<input type='submit' value='Search Videos'>  
        </form>

MYSQL

<?PHP

 connection stuff

$db_handle = mysql_connect($server, $user_name, $password);
$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {
$SQL = "SELECT * FROM Videos WHERE GameType=\"{$_POST['GameType']}\" AND Instructor=\"{$_POST['Instructor']}\"";
$result = mysql_query($SQL);

while ($db_field = mysql_fetch_assoc($result)) {

echo $db_field['ShortDescription'] . ", ";
echo $db_field['LongDescription'] . ", ";
echo $db_field['GameType'] . ", ";
echo $db_field['NumberOfPlayers'] . ", ";
echo $db_field['Instructor'] . ", ";
echo $db_field['Stakes'] . ", ";
echo $db_field['UserPermissionLevel'] . ", ";
echo $db_field['DateCreated'] . "<BR>";

}

mysql_close($db_handle);

}
else {
print "Database NOT Found ";
mysql_close($db_handle);
}




?>

Daniel Szabo · Accepted Answer · 2010-03-27 05:57:13Z

3

Last week, I implemented a solution to a problem similar to this by using a technique found on mikesdotnetting . In it, he describes testing for a null entry from the user in the WHERE clause, then moving to criteria matching.

So, to liken it to your example:

"SELECT * FROM Videos 
  WHERE (GameType IS NULL OR GameType = $_POST['GameType']) 
    AND (Intructor IS NULL OR Instructor = $_POST['Instructor'])"

Also note that this technique will require you to nullify your selected option in your :

<OPTION VALUE=''>Choose Game Type</option>

answered Mar 27, 2010 at 5:57

Daniel Szabo

7,2817 gold badges52 silver badges70 bronze badges

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

Comments

Your Common Sense · Accepted Answer · 2010-03-27 05:47:26Z

1

you can do something like this:

$w=array();
if (!empty($_GET['rooms'])) $w[]="rooms='".mysql_real_escape_string($_GET['rooms'])."'";
if (!empty($_GET['space'])) $w[]="space='".mysql_real_escape_string($_GET['space'])."'";
if (!empty($_GET['max_price'])) $w[]="price < '".mysql_real_escape_string($_GET['max_price'])."'";

if (count($w)) $where="WHERE ".implode(' AND ',$w); else $where='';
$query="select * from table $where";

Note that this code has written for the empty value (<OPTION VALUE=''>)

Also note that your code suffer terribly from the SQL injection

answered Mar 27, 2010 at 5:47

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

3 Comments

user303043 Over a year ago

is there an SQL Injection issue if there is no free form entry? Just dropdown selections?

Your Common Sense Over a year ago

@user303043 ahaha funny question. yes, there is

Lotus Notes Over a year ago

Yes. You shouldn't trust anything coming from client-side. An attacker could simply build their own HTML page ripe with malicious code on their own server and set its action to YOUR processing script.

jjclarkson · Accepted Answer · 2010-03-27 05:50:09Z

0

Build the SQL query in PHP leaving out the part in the where clause if "any" is selected.

answered Mar 27, 2010 at 5:50

jjclarkson

5,9647 gold badges42 silver badges63 bronze badges

Collectives™ on Stack Overflow

Creating a mySQL query using PHP form dropdowns - If user ignores dropdown, do not filter by that parameter

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related