0

Hi Im working on using 2 drop down menu to filter mysql query. Here is the drop down menu

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

i can use this query

<?php
$dState = $_POST["Locations"];
$dJob = $_POST["Jobs"];

$sqlQuery = "SELECT * FROM mytable WHERE state=\"$dState\" AND job=\"$dJob\"";
?>

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.

What would be a good way to code this?
maybe something like if conditions
if($Locations !== 'all'){
if($Jobs !== 'all'){

please help

3
Contributors
5
Replies
9
Views
5 Years
Discussion Span
Last Post by jstfsklh211
1

If you are looking for relational dropdown then, the below code will suit your needs. You need to use jquery-ajax for doing that. The code is mentioned below:-

<?php

include("config/config.php");

if ($_REQUEST['submit'] == "Submit")
{
	//Here your submit code will come
}

//Ajax code

if ($_REQUEST['load_state'] == 1)
{
	$country_id = $_REQUEST['val'];

	$state_query = mysql_query("select state_id, state from state where country_id = '".$country_id."' ");
	
	$select_element = 'State : <select id="state" name="state" onChange="showCity(this.value)"><option value="">Choose</option>';

	while($fetch_state = mysql_fetch_array($state_query))
	{
		
		$select_element.="<option value=".$fetch_state[0].">".$fetch_state[1]."</option>";
	 }
	 $select_element.='</select>&nbsp;';

	 echo $select_element;
	 exit();
}

if ($_REQUEST['load_city'] ==1)
{
	$state_id = $_REQUEST['values'];

	$city_query = mysql_query("select city_id, city from city where state_id = '".$state_id."' ");

	$select_element = 'City : <select id="city" name="city"><option value="">Choose</option>';

	while($fetch_city = mysql_fetch_array($city_query))
	{
		
		$select_element.="<option value=".$fetch_city[0].">".$fetch_city[1]."</option>";
	 }
	 $select_element.='</select>&nbsp;';
	 echo $select_element;
	exit();
}
//EO //Ajax code

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Title of the document</title>
<script type="text/javascript" src="js/jquery.js"></script>
<script type="text/javascript">


function showState(val)
{

	//alert (val);
	$.get('', {load_state:1, val:val}, function(data)
	{
		//Put an animated GIF image insight of content
		$("#state_display").html('<img src="images/ajax-loader.gif" />');
		$("#state_display").html(data);
		$("#state_display").show();
		
	}
	);
}

function showCity(values)
{
	//alert (values);
	$.get('',{load_city:1, values:values}, function (data)
	{
			$("#city_display").html('<img src="images/ajax-loader.gif" />');
			$("#city_display").html(data);
			$("#city_display").show();		
	});
}




</script>
<style type="text/css">
#country_display{float:left;}
#state_display{float:left;}
#city_display{float:left;}

</style>

</head>

<body>
<div id="frm">
	<form method="post" action="">

	<div id="country_display">
			Country : <select id="country" name="country" onChange="showState(this.value)">
		<option value="">Choose</option>
		<?php

			echo $sql="select country_id, country from countries";
			$res=mysql_query($sql) or die(mysql_error());
				
			while($row=mysql_fetch_array($res))
			{	
				echo "<option value=".$row[0].">".$row[1]."</option>";
			}
	
		?>
		</select>&nbsp;
		</div>
			
		<div id="state_display">
		 State : <select id="state" name="state" onChange="showCity(this.value)">
		<option value="">Choose</option>
		<?php

			echo $sql="select state_id, state from state";
			$res=mysql_query($sql) or die(mysql_error());
				
			while($row=mysql_fetch_array($res))
			{	
				echo "<option value=".$row[0].">".$row[1]."</option>";
			}

		?>
		</select>&nbsp;
		</div>

		<div id="city_display">
		City : <select id="city" name="city">
		<option value="">Choose</option>
		<?php

			echo $sql="select city_id, city from city";
			$res=mysql_query($sql) or die(mysql_error());
				
			while($row=mysql_fetch_array($res))
			{	
				echo "<option value=".$row[0].">".$row[1]."</option>";
			}

		?>
		</select>
		</div>
	</form>
<div>
	
<input type="submit" value="Submit" name="submit" />
</div>

</body>
</html>
Votes + Comments
nice but post that jquery script..
0

The table structure are as follows :-

tbl countries:-
country_id int(11) auto_increment
country varchar(35)

tbl state:-
state_id int(11) auto_increment
state varchar(45)
country_id tinyint(4)


tbl city:-
city_id int(11) auto_increment
city varchar(40)
state_id tiny_int(4)
country_id tiny_int(4)

0

wow, thanks for your quick reply. but not looking for relational drop down menu.
im working on static drop down menu. I just need the code to filter mysql results
based on the 2 drop down menu. because each of the menu has "all" option, thats where
im confused on writing the code.

0

leave the value of all set to empty string
<option value="">All</option>
in your php you need to check each time for each var if they are set and append an "and"
something like

$locations = (empty($_POST["locations"]) ? false : $_POST["locations"]);

$filters = "";
if($locations) {
    $filters .= (empty($filters) ? "" : " and ") . $locations;
}
$sqlQuery = "SELECT * FROM mytable " . (empty($filters) ? "" : " where " . $filters);

Edited by jstfsklh211

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.