动态填充mysql数组到下拉菜单

Question

I have a database that I want to get data out onto a website. It contains states listed by name and id. Counties listed by id, namne , and state that contains thems ID and then clubs that exist , with a reference to the county id's that they exist in and columns for their actual data.

What I've got :

A drop down menu that populates itself with state id and name.

What I'd like to accomplish:

On selection of state , let's say ny , take it's id and use this in gathering another mysql array for the county drop down. I'd like it to dynamically occur on selection of state , maybe even giving a count of results next to the drop down.

$resstate = mysql_query("SELECT * FROM state ORDER by longstate;") or die("Note: " . mysql_error());
State:
   <select name="State" size=1>
     <?
        while( $rs = mysql_fetch_array( $resstate ) ) {
            echo "<option value=" .$rs['id'] . ">" . $rs['longstate'] . "</option>";
        }
   echo "</select>";
?>

I know I could use a JavaScript onChange="this.form.submit()" on the first drop down, but it's my understanding that I'd then be making a new page at that point and don't know if I could keep the functionality of the state drop down, say if you accidentally chose new Hampshire when you wanted New York.

here's an example of the current array filling the drop down :

http://snowmobileamerica.com/countytest.php

----EDIT---

Using Dagons Advice , I looked into Ajax.

I made a php file that's supposed to query the database based on a reference to getcounty.php?q=

The file is created as follows :

<?php
$q=$_GET["q"];

$cn=mysql_connect("localhost","user","password") or die("Note: " . mysql_error());

mysql_select_db("snowusa_clubs", $cn);

$sql="SELECT * FROM county WHERE state_id = '".$q."' ORDER by name";

$result = mysql_query($sql);


echo "<select name="County" size=1>";

while($rc = mysql_fetch_array($result))
{
echo "<option value=" .$rc['id'] . ">" . $rc['name'] . "</option>";
 }

 echo "</select>";

mysql_close($cn);
 ?> 

If i try to run it manually http://www.snowmobileamerica.com/getcounty.php?q=33 I get a 500 internal server error...

Any ideas where I went wrong?

Answer 1

not able to comment yet. but for the second question try:

<?php
$q=$_GET["q"];

$cn=mysql_connect("localhost","user","password") or die("Note: " . mysql_error());
echo "Conn ok<br>";
mysql_select_db("snowusa_clubs", $cn);
echo " Database opened<br>"; 
$sql="SELECT * FROM county WHERE state_id = '$q' ORDER by name";

$result = mysql_query($sql);
echo " Database queried <br>";

echo "<select name='County' size=1>";

while($rc = mysql_fetch_array($result))
{
echo "<option value='" .$rc['id'] . "'>" . $rc['name'] . "</option>";//added single quotes in the value
 }
echo "</select> ";

mysql_close($cn);
?>