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 name="Jobs">
<option value="" selected="selected">All jobs</option>
<option value="Carpenter">Carpenters</option>
<option value="Plumbers">Plumbers</option>
<input type="submit" value="search jobs" />

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!

Edited by ychan623: n/a

6 Years
Discussion Span
Last Post by 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']);


if (! empty($_POST['Locations'])) $whereClauses[]="location='".mysql_real_escape_string($_POST['Locations'])."'";
if (! empty($_POST['Jobs'])) $whereClauses[]="job='".mysql_real_escape_string($_POST['Jobs'])."'";
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.