[SOLVED] Problems with dynamic drop down menu

[scarlson]

I am attempting to populate 2 drop down menus from my sql database.  I have found an example that I am using and it seems to work, somewhat.  Both drop down boxes auto populate but that is not exactly what I want.  I would like my first drop down box to auto-populate to list all available States to choose from.  Based off of the state selection, the second drop down box will populate with a list of cities in that state.  I am not sure what I need to do.  Here is the code I have so far:

 

This is pulling from 2 tables:  states and cities

 

<div id="leftcontent">
<p align="center"> </p>

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

<?php
$quer2=mysql_query("SELECT id, state FROM states order by state");

$cat=$_GET['id']; //This line is added to take care if your global variable is off
if(isset($cat) and strlen($cat) > 0){
$quer=mysql_query("SELECT city FROM cities where state_id=$cat order by city");
}else{$quer=mysql_query("SELECT city FROM cities order by city"); } 


echo "<form method=post name=f1 action='index.php'>";   /////Not sure what I use here
/// Add your form processing page address to action in above line. Example action=dd-check.php////
////////// Starting of first drop downlist /////////
echo "<select name='cat' onchange=\"reload(this.form)\"><option value=''>Select one</option>";
while($noticia2 = mysql_fetch_array($quer2)) {
if($noticia2['id']==@$cat){echo "<option selected value='$noticia2[id]'>$noticia2[state]</option>"."<BR>";}
else{echo "<option value='$noticia2[id]'>$noticia2[state]</option>";}
}
echo "</select>";

////////////////// This will end the first drop down list ///////////

////////// Starting of second drop downlist /////////
echo "<select name='subcat'><option value=''>Select one</option>";
while($noticia = mysql_fetch_array($quer)) {
echo "<option value='$noticia[city]'>$noticia[city]</option>";
}
echo "</select>";
////////////////// This will end the second drop down list ///////////
// add your other form fields here ////
echo "<input type=submit value=Submit>";
echo "</form>";




?>

<p align="center"> </p>
</div>

 

Any suggestions would be great.

[Daukan]

Using Ajax would be the easiest way. Google for ajax, there are lots of examples

[scarlson]

Well I googled for Ajax but I am pretty new to all of this stuff.  It doesn't seem like it should be to difficult to do but I am stuck.  I have tried something else from the code above and have different results.  Here is the new code:

 

Maybe giving you guys what my database looks like might help:

 

cities

id

state_id

city  (var char)

 

 

states

id

state (var char)

 

 

 

<div id="leftcontent">
<p align="center"> </p>


<?php
$id = $_GET['id'];
print"<table border=1><tr><td>";
echo'<form name="testform">';
$q = mysql_query("SELECT state FROM states");
echo"<select name=\"cat\" onChange=\"Load_id()\">";



while($row = mysql_fetch_array($q)) {
$selected = ($row["id"] == $id)? "SELECTED":"";
echo"<option value=\"".$row['state']."\"". $selected." >".$row['state']."</option>";
}
echo"</select>";
print"</td></tr><tr><td>";
print "<p>";
$q2 = mysql_query("SELECT city FROM cities WHERE state_id = $id");
echo"<select  name=\"type\">";
while($row = mysql_fetch_array($q2)) {
echo"<option value=\"".$row['city']."\">".$row['city']."</option>";
}
echo"</select></form>";
print"</td></tr></table>";
?>

<script type="text/javascript">
function Load_id()
{
var id = document.testform.cat.options[document.testform.cat.selectedIndex].value
var id_txt = "?id="
location = id_txt + id
}




?>

<p align="center"> </p>
</div>

 

What's happening now is the first drop down box is populated with the states (just what I want) the second drop down box is empty (perfect) but once i select a state, nothing happens to the second drop down box that should load the related cities.  What am I doing wrong?

 

Scott

[GingerRobot]

Try the following example. I've taken the javascript straight from http://www.ajaxfreaks.com/tutorials/1/0.php - i suggest you take a look there to understand a little more about ajax. This would be the page you want to show the drop downs on:

 

<script type="text/javascript">
function createRequestObject() { 

   var req; 

   if(window.XMLHttpRequest){ 
      // Firefox, Safari, Opera... 
      req = new XMLHttpRequest(); 
   } else if(window.ActiveXObject) { 
      // Internet Explorer 5+ 
      req = new ActiveXObject("Microsoft.XMLHTTP"); 
   } else { 
      // There is an error creating the object, 
      // just as an old browser is being used. 
      alert('Problem creating the XMLHttpRequest object'); 
   } 

   return req; 

} 

// Make the XMLHttpRequest object 
var http = createRequestObject(); 

function sendRequest(id) { 

   // Open PHP script for requests 
   http.open('get', 'getcities.php?id='+id); 
   http.onreadystatechange = handleResponse; 
   http.send(null); 

} 

function handleResponse() { 

   if(http.readyState == 4 && http.status == 200){ 

      // Text returned FROM the PHP script 
      var response = http.responseText; 

      if(response) { 
         // UPDATE ajaxTest content 
         document.getElementById("container").innerHTML = response; 
      } 

   } 

} 
</script>
<?php
//connect to database
$sql = "SELECT `id`,`state` FROM `states`";//select all our states
$result = mysql_query($sql) or die(mysql_error());
echo '<select name="state" onChange="sendRequest(this.value)">';//the select box with the javascript function to change the cities
while(list($id,$state) = mysql_fetch_row($result)){
echo '<option value="'.$id.'"'.'>'.$state.'</option>';
}
echo '</select>';
?>
<br /><br />
<div id="container">
<select name="cities">
<option value="unselected">Please select a state first</option>
</select>
</div>

 

And this is getcities.php:

<?php
//connect to database
$id = $_GET['id'];
$sql = "SELECT `id`,`city` FROM `cities` WHERE `state_id`=$id";//select the cities from the given state
$result = mysql_query($sql) or die(mysql_error());
//display the contents of our div tag
echo '<select name="cities">';
while(list($id,$city) = mysql_fetch_row($result)){
echo '<option value="'.$id.'">'.$city.'</option>';
}
echo '</select>';
?>

 

This is just a very quick example - but hopefully you'll get the general idea.

[scarlson]

Thank you so much.  I got this to work now.

[GingerRobot]

Good, glad to help. Can you mark the topic as solved please?

[scarlson]

Actually before I do that, I do have one problem.  The first state that is listed is Alabama but when I go to select it, it does not display the cities.  But, if I choose another state first then go back to Alabama it works fine.  Any ideas?

 

Scott

[GingerRobot]

So Alabama is selected by default? Either add an option as the first option saying 'please select a state' or select the cities which are from alabama and display them in the cities text box to start with, rather than the current 'please select a state' message.

 

Since the javascript action occurs when a change is made, having alabama first means it wont load the cities.

[scarlson]

What I am trying to do is your first suggestion of placing a "Select state" option first but I am not sure yet where to place that. I have tried a few places so far but it hasn't done the trick yet.

 

Can you point me in the right place?

[scarlson]

<?php
//connect to database
$sql = "SELECT `id`,`state` FROM `states`";//select all our states
$result = mysql_query($sql) or die(mysql_error());
echo '<select name="state" onChange="sendRequest(this.value)">';//the select box with the javascript function to change the cities

while(list($id,$state) = mysql_fetch_row($result)){
echo '<option value="'.$id.'"'.'>'.$state.'</option>'; [color=red]//How do i add a "select state" option first????[/color]
}
echo '</select>';
?>
<br /><br />
<div id="container">
<select name="cities">
<option value="unselected">Please select a state first</option> 
</select>
</div>

 

marked in red in the above code is where I believe I need to make a change but don't know what to do.

 

Scott

[GingerRobot]

Nearly - you need to put it outside the loop:

 

<?php
//connect to database
$sql = "SELECT `id`,`state` FROM `states`";//select all our states
$result = mysql_query($sql) or die(mysql_error());
echo '<select name="state" onChange="sendRequest(this.value)">';//the select box with the javascript function to change the cities
echo '<option value="none">Select a State</option>';

while(list($id,$state) = mysql_fetch_row($result)){
echo '<option value="'.$id.'"'.'>'.$state.'</option>'; [color=red]//How do i add a "select state" option first????[/color]
}
echo '</select>';
?>
<br /><br />
<div id="container">
<select name="cities">
<option value="unselected">Please select a state first</option> 
</select>
</div>

[scarlson]

Thank you.