name_masked name_masked - 2 months ago 7
Ajax Question

Populating dropdown - PHP Ajax MySQL

I have 2 dropdowns on my HTML page : The first drop down contains the database column names based on which the the second dropdown would be populated i.e.

I have a single table with fields:

<Student Name, Degree, City>
and following would be the entries;

1. "A", "BS", "New York"
2. "B", "BS", "Chicago"
3. "C", "MS", "Boston"
4. "D", "MS", "New York"


So my first dropdown would contain the column names i.e. "Degree" and "City".

If I select "Degree", the 2nd dropdown should populate "BS" and "MS" and if I select "City", the 2nd dropdown should select "New York", "Boston" and "Chicago".

How can I go about with the implementation?

[Adding my code]:

the
changeSecond(first)
method remains exactly the same as you suggested

<body>
<form method="POST" action="" name="mainForm">
<table>
<tr>
<td> Filter by: </td>
<td>
<div id="first">
<select onChange="changeSecond(this.value)">
<option value="1">All</option>
<option value="2">Degree</option>
<option value="3">City</option>
</select>
</td>
</tr>
<tr>
<td>&nbsp</td>
<td>
<div id="second">
<select name="val">
<option value=""></option>
</select>
</div>
</td>
</tr>
</table>
</form>
</body>


And this is the second_script.php as you suggested:

<?
$link = mysql_connect("localhost", "root", "");

if (!$link)
{
die('Could not connect: ' . mysql_error());
}
if (mysql_select_db("myDatabase", $link))
{
$first=mysql_real_escape_string($_REQUEST["first"]);
$query="SELECT ".$first." FROM myTable GROUP BY ".$first;
$data=mysql_query($query);

echo "<select id=\"second\">";
while($row=mysql_fetch_row($data))
{
echo "<option value=\"".$row[0]."\">".$row[0]."</option>";
}
echo "</select>";
}
echo mysql_error();
?>

Answer

If you want a more dynamic solution (that will accommodate changes to the background DB) you can do something like this on your page:

<script>
        function changeSecond(first){
        var xmlhttp;
        if (window.XMLHttpRequest)
          {// code for IE7+, Firefox, Chrome, Opera, Safari
          xmlhttp=new XMLHttpRequest();
          }
        else
          {// code for IE6, IE5
          xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
          }
        xmlhttp.onreadystatechange=function()
        {
          if (xmlhttp.readyState==4 && xmlhttp.status==200)
            {
            var res=xmlhttp.responseText;
            document.getElementById("second").innerHTML=res;
            }
          }
        xmlhttp.open("GET","second_script.php?first="+first,true);
        xmlhttp.send();
        }
        </script>
...
<select onChange="changeSecond(this.value)">
<option value="Degree">Degree</option>
<option value="City">City</option>
</select>
<div id="second"><select><option value=""></option></select></div>

and then a script similar to:

<?php
//database connection
$first=mysql_real_escape_string($_REQUEST["first"]);
$query="SELECT ".$first." FROM tablename GROUP BY ".$first;
$data=mysql_query($query);
echo "<select>";
while($row=mysql_fetch_row($data)){
echo "<option value=\"".$row[0]."\">".$row[0]."</option>";
}
echo "</select>";
?>

I guess for real flexibility you'd also want to dynamically populate that first one using mysql_field_name in another script similar to above