1

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();
?>
Improve this question
5
  • ugh, sorry. try an alternate variable name instead of 'new'...(in the changeSecond function) Commented Mar 18, 2011 at 1:42
  • Also, you'll need to tweak the script to accomodate your 'All' case Commented Mar 18, 2011 at 1:48
  • @Robot: It still doesn't work. Selecting a value from the 1st dropdown displays the following on the browser "; while($row=mysql_fetch_row($data)) { echo ""; } echo ""; } ?> . I am not able to understand the issue here. Can there be a problem with the Database query? Commented Mar 22, 2011 at 0:46
  • That looks like the php is getting closed early...is there a rogue ? after the <select id=\"second\" perhaps Commented Mar 22, 2011 at 1:07
  • scratch that ^...is your script still exactly as it appears above? It looks like there is a quotation mark missing somewhere so it's echoing everything it shouldn't, and hiding what it should be echoing. maybe an extra \ to give second\">\"; Commented Mar 22, 2011 at 1:14

5 Answers 5

Reset to default
5

code for the main file in which you want to populate drop down based on other in below example i show localities in child dropdown for that city that is selected in parent drop down

    <script type="text/javascript">
    function city_locality(val)
    {
    // alert (val);
    url="<?php echo $this->baseurl ?>/components/com_ezportal/includes/query.php";
    data="stid="+val;
$.post(url,data,function(data){
$("#locid").html(data);
});     
    }
    </script>
    <select name="filter_1state" id="filter_1state" onChange="city_locality(this.value)">
      <option value="0">-- Select City --</option>
      <option value="1">Lahore</option>
      <option value="2">Karachi</option>
      <option value="3">Islamabad</option>
      <option value="4">Quetta</option>
      <option value="5">Multan</option>
    </select>

code for the file query.php

    <?php 
    $link = mysql_connect('localhost', 'root', '');
    mysql_select_db('mydatabase');
    if (isset($_POST['stid']))
    {
        $stid = $_POST['stid'];
        $query= mysql_query("SELECT id,ezcity FROM tbl_locality WHERE stateid = '".$stid."' GROUP BY ezcity"); 
    ?>
        <option value="0">-- Select Locality --</option>
    <?php
        while($row = mysql_fetch_array($query))
        {
    ?>
        <option value="<?php echo $row['id']?>"><?php echo $row['ezcity']?>        </option> 
        <?php
        }
    }
    ?>
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You are a start and I am going to modify your answer and repost it because I saw something which I just eliminated and it worked for me
2

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

Improve this answer

4 Comments

Hi, I tried your suggestions but still having the same issue i.e. the second dropdown does not populate at all. Is there any way I can debug the code? . Infact, I dont think the onChange call take place because the link second_script.php?first= does not show up on selecting a value from the 1st dropdown.
what do you mean doesn't show up? what if you replace the code (temporarily) in changeSecond to pop an alert box? (to confirm the function is being called)...also, maybe post your code as is so we can get a better sense of what's not working
oh, are you running these from a server, or locally?
locally. And replacing the onChange="javascript: alert('On Load event fired!')" does popup an alert window. Added my code. Please suggest me how to go about
2

You could either have all the dropdown needed preloaded and hidden, and show them when the 'change' event is triggered in the first dropdown, or have two dropdowns and empty it on that same 'change' event.

If you choose the second approach you should buffer the data to insert in the dropdown list, partially or totally

Improve this answer

Comments

1

You can use ajax for this purpose

eg1.html

    <html>
    <head><title></title>
    </head>
    <body>
    <form method="POST" action="" name="mainForm">
    <table>
        <tr>
            <td> Filter by: </td>
            <td>
                <div id="first">
                   <select id="first_dropdown">
                      <option value="All">All</option>
                      <option value="Degree">Degree</option>
                      <option value="City">City</option>
                   </select>
            </td>
         </tr>
         <tr>
            <td>&nbsp</td>
            <td>
               <div id="second">
                  <select id="second_dropdown">

                  </select>
               </div>
            </td>
         </tr>
      </table>
   </form>
</body>
</html>

    <script src = "https://code.jquery.com/jquery-3.1.0.min.js"></script>
    <script type="text/javascript">
    $(document).ready(function(){
        $('#first_dropdown').change(function(){
            $.ajax({
                'type' : 'post',
                'url' : 'getDropdownOptions.php',
                'data': 'first=' + $(this).val(),
                'success' : function(data) {
                    $('#second_dropdown').html(data);
                }
             });
          });
        });
    </script>

getDropdownOptions.php

    <?php

    $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);

        $rtn_data = '';
        while ($row = mysql_fetch_row($data)) {
            $rtn_data .= "<option value=\"" . $row[0] . "\">" . $row[0] . "        </option>";
        }
        echo $rtn_data;
        exit;
    }
    echo mysql_error();

    ?>
Improve this answer

Comments

1 
Divyesh here, your answer is
==============================
edit.php
==============================
<!-- edit.php -->
<?php
include("config.php");
$id=$_REQUEST['id'];
echo $id;
$query=mysqli_query($con,"SELECT * FROM register r
INNER JOIN country c ON r.cuid = c.cuid
INNER JOIN state s ON r.sid = s.sid
INNER JOIN city ct ON r.cid = ct.cid where id='$id'");

while($r=mysqli_fetch_array($query))
{

     $fn=$r['firstname'];
     $add=$r['address'];
     $gn=$r['gender'];
     $hobby=$r['hobby'];
     $h=explode(',',$hobby);
     $q=array('reading','traveling','cricket','drawing');
     $country=$r['cuid'];
     $state=$r['sid'];
     $city=$r['cid'];
     echo $gn;
     $edu= $r['education'];
     $email=$r['email'];
     $pass=$r['password'];
     $conpass=$r['conpassword'];
     $phno=$r['phoneno'];
}

?>
<html>
<head>
    <script src="jquery.js"></script>
<script type="text/javascript">
$(document).ready(function(){

    $('#country').on('change',function(){
        var countryID = $(this).val();
        if(countryID){
            $.ajax({
                type:'POST',
                url:'ajaxData.php',
                data:'cuid='+countryID,
                success:function(html){
                    $('#state').html(html);
                    $('#city').html(html); 
                }
            }); 
        }else{
            $('#state').html(html);
            $('#city').html(html); 
        }
    });

    $('#state').on('change',function(){
        var stateID = $(this).val();
        if(stateID){
            $.ajax({
                type:'POST',
                url:'ajaxData.php',
                data:'sid='+stateID,
                success:function(html){
                    $('#city').html(html);
                }
            }); 
        }else{
            $('#city').html(html); 
        }
    });
});
</script>
</head>
    <body>
        <form method="post" action="update.php">
            <table border="1" align="center">
            <caption>Edit user data</caption>
            <tr>
                <td>First name</td>
                <td><input type="text" name="fn" value="<?php echo $fn;?>"></td>
            </tr>
            <tr>
                <td>Address</td>
                <td><input type="text" name="add" value="<?php echo $add;?>"></td>
            </tr>
            <tr>
                <td>Gender</td>
                <td><input type="radio" name="gn" value="male" <?php echo ($gn=='male')?'checked':'' ; ?> size="17">Male
                <input type="radio" name="gn" value="female" <?php echo ($gn=='female')?'checked':'' ; ?> size="17">Female
                </td>
            </tr>
            <tr>
                <td>Hobby</td>
                <td><input type="checkbox" name="hobby[]" value="reading" <?php if(in_array('reading',$h)){echo ("checked:'checked'");}?> >reading
                <input type="checkbox" name="hobby[]" value="traveling" <?php if(in_array('traveling',$h)){echo ("checked:'checked'");}?> >traveling
                <input type="checkbox" name="hobby[]" value="cricket" <?php if(in_array('cricket',$h)){echo ("checked:'checked'");}?> >cricket
                <input type="checkbox" name="hobby[]" value="drawing" <?php if(in_array('drawing',$h)){echo ("checked:'checked'");}?> >drawing</td>
            </tr>
            <?php
            $query = mysqli_query($con,"SELECT * FROM country");

            //Count total number of rows
            $rowCount = mysqli_num_rows($query);
            ?>
            <td>Country</td>
            <td><select name="country" id="country">
                <option value="<?php echo $country;?>"><?php echo $country;?></option>
                <?php
                if($rowCount > 0)
                {
                    while($row = mysqli_fetch_array($query))
                    { 
                        echo '<option value="'.$row['cuid'].'">'.$row['country'].'</option>';
                    }
                }
                else
                {
                    echo '<option value="">Country not available</option>';
                }
                ?>
            </select>
            </td></tr>
            <tr>
            <td>State</td>
            <td>
            <select name="state" id="state">
                <option value="<?php echo $state;?>"><?php echo $state;?></option>

            </select>
            </td></tr>
            <tr>
            <td>City</td>
            <td>
            <select name="city" id="city">
                <option value="<?php echo $city;?>"><?php echo $city;?></option>

            </select>
            </td>
            </tr>
            <tr>
                <td>Education</td>
                <td><input type="text" name="edu" value="<?php echo $edu;?>"></td>
            </tr>
                <td>Email</td>
                <td><input type="text" name="email" value="<?php echo $email;?>"></td>
            </tr>
            <tr>
                <td>Password</td>
                <td><input type="text" name="pass" value="<?php echo $pass;?>"></td>
            </tr>
            <tr>
                <td>Confirm password</td>
                <td><input type="text" name="conpass" value="<?php echo $conpass;?>"></td>
            </tr>
            <tr>
                <td>Phone no</td>
                <td><input type="text" name="phno" value="<?php echo $phno;?>"></td>
            </tr>
            <tr>
                <td><input type="hidden" name="id" value="<?php echo $id;?>">
                <input type="submit" name="update" value="update"></td>
            </tr>
            </table>
        </form>
    </body>
</html>

================
<h3>ajaxData.php</h3>
================
<!--ajaxData.php-->
<?php
//Include database configuration file
include("config.php");

if(isset($_POST["cuid"]) && !empty($_POST["cuid"]))
{
    //Get all state data
    $query = mysqli_query($con,"SELECT * FROM state WHERE cuid = ".$_POST['cuid']."");

    //Count total number of rows
    $rowCount = mysqli_num_rows($query);

    //Display states list
    if($rowCount > 0)
    {
        echo '<option value="">Select state</option>';
        while($row =  mysqli_fetch_array($query))
        { 
            echo '<option value="'.$row['sid'].'">'.$row['state'].'</option>';
        }
    }
    else
    {
        echo '<option value="">State not available</option>';
    }
}

if(isset($_POST["sid"]) && !empty($_POST["sid"]))
{
    //Get all city data
    $query = mysqli_query($con,"SELECT * FROM city WHERE sid =".$_POST['sid']."");

    //Count total number of rows
    $rowCount = mysqli_num_rows($query);

    //Display cities list
    if($rowCount > 0)
    {
        echo '<option value="">Select city</option>';
        while($row = mysqli_fetch_array($query))
        { 
            echo '<option value="'.$row['cid'].'">'.$row['city'].'</option>';
        }
    }
    else
    {
        echo '<option value="">City not available</option>';
    }
}
?>
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.