Member Avatar for ychan623

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

  • 4 Contributors
  • 6 Replies
  • 2K Views
  • 6 Years Discussion Span
  • Latest Post Latest Post by teja_2
Member Avatar for javedsai

r u looking for making relational dropdown?

Member Avatar for javedsai

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>
commented: nice but post that jquery script.. +0
Member Avatar for javedsai

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)

Member Avatar for ychan623

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.

Member Avatar for jstfsklh211

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
Member Avatar for teja_2

if someone help for me it will be very thankful...my requirement is i have a locations dropdown..
ex:
bangalore
delhi
mumbai
India
if i select india total bangalore,delhi,mumbai data have to display..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.