I dont know how to look up this really so here is my explanation.

I am making an admin section to add profiles and when I enter the city and state I would like it dynamically pull up through my mysql database. So when I click COLORADO from my dropdown menu, I would like another drop down menu with cities in just colorado appear.

hopefully that makes sense

so you click a state from a state drop down menu, and than it will bring up the cities from the selected state drop down menu.

I know how to do all the php mysql stuff, just dont know the javascript part.

hope someone can help

    hey iBuddy,
    I do alot of this kind of stuff, I put this together pretty fast but I think you will get the idea.

    <html>

<head>
  <title></title>
  <script language="JavaScript" type="text/javascript">
     function get_city(){
        document.city.submit();
     }
     function set_data(strState, strCity){
        document.submit.state.value = strState;
        document.submit.city.value = strCity;
     }
  </script>
</head>

<body>
<form name='city' action='#' method='POST'>
<select onclick="get_city()" name="lstState" size="1">
   <option value="COLORADO">COLORADO</option>
</select>
<select name="lstCity" size="10">
<?php
$state = $_POST['state'];
if(isset($state)){
   $sql = "select citys from tblStates where state = '$state'";
   $result = mysql_query($sql);

while ($row = mysql_fetch_array ($result)) {
   $city = $row['citys'];
   print"<option value='$city'>$city</option>";
}
print"<script language='JavaScript' type='text/javascript'>set_data('$state', '$city')</script>";
}
?>
</select>
</form>
<form name="submit" action="next.php" method="POST">
   <input type="hidden" name="state" value="" />
   <input type="hidden" name="city" value="" />
   <input type="submit" value="Submit" />
</form>

</body>

</html>

      FYI... AJAX will do the same without resubmitting the form.. It does, however, require that you have 3 files... You main Form page(php), a Javascript file, and a 2nd PHP page that executes just the SQL Query you want...

      So...

      PHP Form

      <html> 
<head> 
<title></title> 
<script src='get_city.js'></script>
</head> 

<body> 
<form name='form' id='form' action='action_page.php' method='POST'> 
<div><label for="State">State : </label>
<select onchange="get_city(this.value)" name="State" id="State"> 
   <option value="COLORADO">COLORADO</option> 
</select> 
</div>
<div>
<label for="City">City : </label>
<select name="City" id="City"> 
    <option value='Select One'>Select One</option>
</select>
</div>

<input type="submit" value="Submit" /> 
</form> 
</body> 

</html>

      Javascript file named get_city.js (in same directory)

      var xmlHttp

function get_city(state)
  {
  xmlHttp=GetXmlHttpObject();
  if (xmlHttp==null)
     {
     alert("Your browser does not support AJAX!");
     return false;
    }

  var cityList = document.getElementById("City");
  var optionItem;

   if (state.length==0) 
       {
       // Clear existing list
       for (var count= cityList.options.length=1; count >-1; count--)
           {
           cityList.options[count] = null;
           }

   textValue = "Select One";
   optionItem = new Option(textValue, textValue, false, false);
   cityList.options[0] = optionItem;

   return false;
   }

   var url='get_city.php';   // your php SQL file
   url=url+"?q="+state;
   xmlHttp.onreadystatechange=stateChanged;
   xmlHttp.open("GET",url,false);
   xmlHttp.send(null);
   var result = xmlHttp.responseText;
   var cities = result.split(",");  // you'll be returning your values in a csv value
   var size = cities.length;

   //clear existing values
   for (var count= cityList.options.length=1; count >-1; count--)
      {
      cityList.options[count] = null;
      }

  // create new list
  for (var count = 0; count<size; count++)
     {
     textValue = cities[count];
     optionItem = new Option(textValue, textValue, false, false);
     cityList.options[cityList.length] = optionItem;
     }
     cityList.options[0].selected = true;  // sets the first value as selected.
}

// Watches for change in state of "State" field
function stateChanged()
   {
   if(xmlHttp.readyState==4)
     {
     document.getElementById("City").innerHTML=xmlHttp.responseText;
     }
}

// Tests for AJAX Compatibility of browsers.
function GetXmlHttpObject()
   {
   var xmlHttp=null;
   try 
     {
     // Firefox, Opera 8.0+, Safari
     xmlHttp=new XMLHttpRequest();
     }
   catch (e)
     {
     try
       {
       xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
       }
     catch (e)
        {
        xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
        }
     }
     return xmlHttp;
}

      And then your simple PHP SQL file named get_city.php

      <?php
$q = $_GET['q'];
// Open your database here.....
$host = "host name";
$user = "username";
$password = "password";

$db = mysql_connect($host, $user, $password);
$database = "database name";
mysql_select_db($database, $db);

$strSQL = "SELECT city FROM table_name WHERE state='" . $q . "' ORDER BY city";
$result = mysql_query($strSQL);
$rows = mysql_num_rows($result);
$counter=0;
while ($row = mysql_fetch_assoc($result)) {
   if($counter==0) {
        $html = $row['city'];
  } else {
        $html .= "," . $row['city'];
  }
  $counter++;
}
echo $html;
?>

      These will all be small file sizes, but unless I made a typo here (I'm at work so I can't test it) it should work fine. You'll just need to tweak it with your database & field info..

      Good Luck

        Write a Reply...