multiple dropdown menu to filter mysql query

[ychan623]

Hi all, I'm working on a webpage with 2 dropdown menu and a submit button. both the dropdown menu has a default value="" selected. I am trying to figure out a way to filter mysql query. Here is the drop down menu

<form action="showJobs.php" method="post">
<select name="Locations">
<option value="" selected="selected">All Locations</option>
<option value="arizona">Arizona</option>
<option value="alaska">Alaska</option>
</select>
<select name="Jobs">
<option value="" selected="selected">All jobs</option>
<option value="Carpenter">Carpenters</option>
<option value="Plumbers">Plumbers</option>
</select>
<input type="submit" value="search jobs" />
</form>

i can use this query

$Locations = $_POST["Locations"];
$Jobs = $_POST["Jobs"]; 
$sqlQuery = "SELECT * FROM mytable WHERE location='"$Location"' AND job='"$Jobs"'";

However, instead of requiring both a 'location' and a 'job,' the user has the option of selecting just one or the other, so that if the user selects "Arizona" and leaves the 'jobs' menu as is (for example, at the default option of 'All jobs'), we would be returned all of the carpenters, plumbers, and auto mechanics in Arizona. Or if the state was left at the default, and 'carpenters' was selected, then all the carpenters in Arizona, Alaska, and Alabama would be returned.
I try to use this code below, but it does not filter using the dropdown menus, instead it pull all records

$whereClauses = array(); 
if (! empty($_POST['Locations'])) $whereClauses[] ='Locations='.mysql_real_escape_string($_POST['Locations']);
if (! empty($_POST['Jobs'])) $whereClauses[] ='Jobs='.mysql_real_escape_string($_POST['Jobs']);
$where = '';
if (count($whereClauses) > 0) { $where = ' WHERE '.implode(' AND ',$whereClauses); } 
$sql = mysql_query("SELECT * FROM mytable".$where);

need help!

[t_thakar]

you may need to use javascript to disable the dropdown menu

http://www.javascriptkit.com/javatutors/deform3.shtml

[LethargicCoder]

Your login seems to be OK, it's just the sql you're building that seems off. I'm actually surprised you get a result and not a DB error.

Your first code snippet shows that your column names are location and job but in your second snippet you have your column names defined locations and jobs (plural). You are also missing the quotes from your values.

try changing

if (! empty($_POST['Locations'])) $whereClauses[] ='Locations='.mysql_real_escape_string($_POST['Locations']);
if (! empty($_POST['Jobs'])) $whereClauses[] ='Jobs='.mysql_real_escape_string($_POST['Jobs']);

to

if (! empty($_POST['Locations'])) $whereClauses[]="location='".mysql_real_escape_string($_POST['Locations'])."'";
if (! empty($_POST['Jobs'])) $whereClauses[]="job='".mysql_real_escape_string($_POST['Jobs'])."'";