populate one dropdown, with the selection from another

soloWebDev · #1 (**permalink**) 05-07-07, 10:31 AM

I have 2 drop downs,
1) The intial value of the first drop down, is populated with all 50 of the USA states.
2) The second drop down, should be populated with the city's that are in the selected state.

The rows are all made in my database, and are ready for selection, but I am a bit confused on how to accomplish this task.

I am trying:

PHP Code:


			
$q = "SELECT state FROM table ORDER BY state";

$r = mysql_query($q) or die("Invalid query: $q");

echo "<select name=state>";

echo "<option value=''>Select a state</option>";

while($data = mysql_fetch_array($r))

{

echo "<option value=\"".$data['state']."\" >".$data['state']."</option>";

}

echo "</select>";

$selectedState = $data['state'];

// This is where i get mixed, with how to select the city's that are within the state that user selects.



//What I tried:

$q2 = "SELECT DISTINCT

city,

state

FROM table

ORDER BY city

WHERE state = $selectedState"; 

$r2 = mysql_query($q2) or die("Invalid query: $q2");

echo "<select name=city>";

echo "<option value=''>Select a city</option>";

while($data2 = mysql_fetch_array($r2))

{

echo "<option value=\"".$data2 ['city']."\" >".$data2 ['city']."</option>";

}

echo "</select>";

n3wb!e · #2 (**permalink**) 05-08-07, 01:16 AM

The simplest way is to submit the page using javascript whenever there is a change in the drop down box using 'onchange event'. You can save the selected value to some hidden variable and then populate the second drop down box using that hidden value. Obviously, it is much slower.
Or, you can use ajax which is a little bit complicated (well, i dont know much bout ajax. it might be simple. i dont know). Ofcourse, this is fast compared to the previous method. This has been discussed alot of times in this forum.

all the best.

job0107 · #3 (**permalink**) 05-08-07, 03:34 AM

This is just a barebones <form> inside a <div>. You can add around it anything you like.

Everything inside " if(!$selectedCity) " would be like page1.
And everything inside the last else statement would be like page2 with the variables $selectedState and $selectedCity available for use.

You will need two tables in your database to make this program work. And it's all done with PHP.

PHP Code:


			
<html>
<head>
<style>
table{
 border:1px solid #000000;
}
.td1{
text-align:center;
border:1px solid #000000;
}
.td2{
border:1px solid #000000;
border-right:1px solid #000000;
padding:0px 10px 0px 10px;
}
div{
border:1px solid #000000;
width:240px;
text-align:center;
padding-top:20px;
}
</style>
</head>
<body>
<?php
// You will need two tables in your database to make this program work. //

// Table states contains ( state ). Put the names of all the states in this table. //
$state = "states";

/* Table citys contains ( state, city ). 
I imagine this table will take a while to construct
if you want all the states and their cities. */
$city = "citys";

$selectedState = $_POST["state"];
$selectedCity = $_POST["city"];

if(!$selectedCity)
{
 echo "<div>";
 echo "<form action='#' method='post'>";
 $q = "SELECT * FROM $state";
 $r = mysql_query($q) or die("Invalid query: $q");
 echo "<span><select name='state'>";
 echo "<option value=''>Select a state</option>";
 while($data = mysql_fetch_array($r))
 {
  if($data['state'] == $selectedState)
  {
   echo "<option value='".$data['state']."' SELECTED>".$data['state']."</option>";
   }
  else
  {
   echo "<option value='".$data['state']."'>".$data['state']."</option>";
   }
  }
 echo "</select></span>";
 if($selectedState}
 {
  $q = "SELECT city FROM $city WHERE state = '$selectedState'";
  $r = mysql_query($q) or die("Invalid query: $q");
  echo "<span><select name='city'>";
  echo "<option value=''>Select a city</option>";
  while($data = mysql_fetch_array($r))
  {
   echo "<option value='".$data['city']."'>".$data['city']."</option>";
   }
  echo "</select></span>";
 }
 echo "<p><input type='submit' value='Submit'></p>";
 echo "</form>";
 echo "</div>";
 }
else
{
 // The rest of your program here //
 // The following line is just sample output //
 echo "<table><tr><td class='td1'>State</td><td class='td1'>City</td></tr><tr><td class='td2'>".$selectedState."</td><td class='td2'>".$selectedCity."</td></tr></table>";
 }
?>
</body>
</html>

job0107 · #4 (**permalink**) 05-08-07, 08:14 AM

This program is more like the one you first presented. It uses the one table you already have.

I also included the mysql_connect, mysql_select_db and ORDER BY statements to this one.

This is just a barebones <form> inside a <div>. You can add around it anything you like.

Everything inside " if(!$selectedCity) " would be like page1.
And everything inside the last else statement would be like page2 with the variables $selectedState and $selectedCity available for use.

PHP Code:


			
<html>
<head>
<style>
table{
 border:1px solid #000000;
}
.td1{
text-align:center;
border:1px solid #000000;
}
.td2{
border-left:1px solid #000000;
border-right:1px solid #000000;
padding:0px 10px 0px 10px;
}
div{
border:1px solid #000000;
width:240px;
text-align:center;
padding-top:20px;
}
</style>
</head>
<body>
<?php
$selectedState = $_POST["state"];
$selectedCity = $_POST["city"];
if(!$selectedCity)
{
 $link = mysql_connect("localhost", "***", "***") or die ("No database connection established ... " . mysql_error());
 mysql_select_db("*****");
 echo "<div>";
 echo "<form action='#' method='post'>";
 $q = "SELECT DISTINCT state FROM table ORDER BY state";
 $r = mysql_query($q) or die("Invalid query: $q");
 echo "<span><select name='state'>";
 echo "<option value=''>Select a state</option>";
 while($data = mysql_fetch_array($r))
 {
  if($data['state'] == $selectedState)
  {
   echo "<option value='".$data['state']."' SELECTED>".$data['state']."</option>";
   }
  else
  {
   echo "<option value='".$data['state']."'>".$data['state']."</option>";
   }
  }
 echo "</select></span>";
 if($selectedState}
 {
  $q = "SELECT city FROM table WHERE state = '$selectedState' ORDER BY city";
  $r = mysql_query($q) or die("Invalid query: $q");
  echo "<span><select name='city'>";
  echo "<option value=''>Select a city</option>";
  while($data = mysql_fetch_array($r))
  {
   echo "<option value='".$data['city']."'>".$data['city']."</option>";
   }
  echo "</select></span>";
 }
 echo "<p><input type='submit' value='Submit'></p>";
 echo "</form>";
 echo "</div>";
 mysql_close($link);
 }
else
{
 // The rest of your program here //
 // The following line is just sample output //
 echo "<table><tr><td class='td1'>State</td><td class='td1'>City</td></tr><tr><td class='td2'>".$selectedState."</td><td class='td2'>".$selectedCity."</td></tr></table>";
 }
?>
</body>
</html>

soloWebDev · #5 (**permalink**) 05-08-07, 09:05 AM

Quote:

Originally Posted by job0107

<?php
if($selectedState}
{
?>
[/php]

This just needed to be changed from
$selectedState} TO $selectedState)

Thank you Job for your hard work with that, it works great. Does anyone have any thoughts on how to do it with out a submit button?
I was thinking with a script like this

Code:

<SCRIPT language=JavaScript>
function reload(form)
{
var val=form.cat.options[form.cat.options.selectedIndex].value;
self.location='dd.php?cat=' + val ;
}

In which case I would need to jump over to the JavaScript section, but figured i would ask.
Thanks

job0107 · #6 (**permalink**) 05-08-07, 09:21 PM

Quote:

Does anyone have any thoughts on how to do it with out a submit button?

Yes that can be done very easily.

First of all, there was a bug in the last program. It only let you select one state. And if you tried to select a different state, it displayed the city only.
So I fixed that.

I swapped out the submit button for two hidden inputs. And modified the reception of the POST variables.

Then I added a javascript function for housekeeping and a name to the form.

And last but not least, I added the onchange event to the select elements and programmed them to submit the form. And added a command to perform the housekeeping in the last else statement.

I'm sure there are ways to make this program more streamlined, but for as little research as I have put into it, it works.

Here is the finished program. " I hope I got everything right this time.

"

PHP Code:


			
<html>
<head>
<style>
table{
 border:1px solid #000000;
}
.td1{
text-align:center;
border:1px solid #000000;
}
.td2{
border-left:1px solid #000000;
border-right:1px solid #000000;
padding:0px 10px 0px 10px;
}
div{
border:1px solid #000000;
width:240px;
text-align:center;
padding-top:20px;
}
</style>
<script>
function clear_form()
{
 document.getElementById('temp_state').value = '';
 document.getElementById('temp_city').value = '';
 }
</script>
</head>
<body>
<?php
$selectedState = $_POST["temp_state"];if(!$selectedState){$selectedState = $_POST["state"];}
$selectedCity = $_POST["temp_city"];if(!$selectedCity){$selectedCity = $_POST["city"];}
if(!$selectedCity)
{
 $link = mysql_connect("localhost", "***", "***") or die ("No connection established ... " . mysql_error());
 mysql_select_db("*****") or die ("Sorry, no such database ... " . mysql_error());
 echo "<div>";
 echo "<form name='theForm' action='#' method='post'>";
 $q = "SELECT DISTINCT state FROM table ORDER BY state";
 $r = mysql_query($q) or die("Invalid query: $q");
 echo "<span><select name='state' onchange='theForm.submit()'>";
 echo "<option value=''>Select a state</option>";
 while($data = mysql_fetch_array($r))
 {
  if($data['state'] == $selectedState)
  {
   echo "<option value='".$data['state']."' SELECTED>".$data['state']."</option>";
   }
  else
  {
   echo "<option value='".$data['state']."'>".$data['state']."</option>";
   }
  }
 echo "</select></span>";
 if($selectedState}
 {
  $q = "SELECT city FROM table WHERE state = '$selectedState' ORDER BY city";
  $r = mysql_query($q) or die("Invalid query: $q");
  echo "<span><select name='city' onchange='theForm.submit()'>";
  echo "<option value=''>Select a city</option>";
  while($data = mysql_fetch_array($r))
  {
   echo "<option value='".$data['city']."'>".$data['city']."</option>";
   }
  echo "</select></span>";
 }
 echo "<input id='temp_state' name='temp_state' type='hidden' value='$selectedState'>";
 echo "<input id='temp_city' name='temp_city' type='hidden' value='$selectedCity'>";
 echo "</form>";
 echo "</div>";
 mysql_close($link);
 }
else
{
 // The rest of your program here //
 // The following line is just sample output //
 echo "<table><tr><td class='td1'>State</td><td class='td1'>City</td></tr><tr><td class='td2'>".$selectedState."</td><td class='td2'>".$selectedCity."</td></tr></table>";
 if($selectedState && $selectedCity)
 {
  echo "<script>clear_form()</script>";
  }
 }
?>
</body>
</html>

soloWebDev · #7 (**permalink**) 05-09-07, 08:50 AM

Thank you Job for your hard work with that it worked great. I am going to work on adding a function that after a particular city is selected; it goes back to showing the state, in case the user chooses something different. But thank you for your hard work I am very appreciative for it.

soloWebDev · #8 (**permalink**) 05-09-07, 09:59 AM

Thank you everyone for the help, it was very helpful.
Please reference this thread for the complete puzzle on this topic.
LINK