CODEWITHSUNDEEP
phpjavascripthtmlmysql
Ankit Sharma
Ankit Sharma

Reputation: 396

How can i populate a dropdown list by selecting the value from another dropdown list?

I have build two drop downs (like state and city) by fetching the records of both drop downs from mysql database and am trying to build the tool in which, while selecting any value (i.e. any state) from first drop down, at that time in second drop down (in city) only those values (cities) under that value (state) selected in first drop down should be visible.

Here's my code:

<tr>    
        <td id='hed'><span style="font-family:verdana,geneva,sans-  serif">State</state></td>
        <td>
        <?php 
        $dbcon = mysql_connect("@ip","@username","@password");

        if($dbcon)
        {
            mysql_select_db("@database", $dbcon);
        }
        else
        {
            die('error connecting to the database');
        }

        $qry = "select @value(state) from @tablename  ";
        $result = mysql_query($qry) or die(mysql_error());

        $dropdown = "<select name='@valuename' id='officeItemList' style='cursor:pointer;cursor:hand;'>";
        while($row = mysql_fetch_array($result))
        {           
            $dropdown .= "\r\n<option value='{$row['@value']}' > {$row['@value']} </option>";
        }
        $dropdown .= "\r\n</select>"; 
        echo $dropdown;
        mysql_close($dbcon);
        ?>
        </td> 
    </tr>

        <tr>
        <td id='hed'><span style="font-family:verdana,geneva,sans-serif">City</span></td>
        <td colspan="1"> 
        <?php 
        $dbcon = mysql_connect("@ip","@username","@password");

        if($dbcon)  
        {
            mysql_select_db("@database", $dbcon);
        }  
        else
        {
            die('error connecting to the database');
        }  

        $qry = "select value2(city) from @tablename where ";
        $result = mysql_query($qry) or die(mysql_error()); 

        $dropdown = "<select name='@value2' id='officeItemList' style='cursor:pointer;cursor:hand;'>";
        while($row = mysql_fetch_array($result)) 
        {

            $dropdown .= "\r\n<option value='{$row['@value2']}' > {$row['@value2']} </option>";
        }
        $dropdown .= "\r\n</select>"; 
        echo $dropdown;
        mysql_close($dbcon);
        ?>      


        </td>
    </tr>

Upvotes: 13

Views: 4238

Answers (4)

Scriptlabs
Scriptlabs

Reputation: 498

There are 2 approaches i would suggest to you.

First one only uses php but needs to store all available cities per state before the page is rendered. This is a bad solution!

I would suggest using AJAX and a Javascript call to another PHP Script can solve your problem!

Use jQuery to get a javascript framework that makes live easier!

Your HTML:

<select id="select-state">
    <option value="0">Select a state</option>
    <option value="ohio">Ohio</option>
</select>
<select id="select-city">
    <option value="0">Select a state first</option>
</select>

Create a php script that makes the mysql query with a given state via $_GET["state"] (or $_POST).

Encode the result from mysql as a JSON Object and echo it!

{
    cities: [
        'City 1',
        'City 2',
        ...
    ]
}

php function: json_encode()

You then can do someting like:

$(function(){
    $('#select-state').on("change", function(){
        var state = $(this).val();
        if(state!=0) {
            $.get('your/path/to/cities/script.php?state='+state,function(data){
                if(data.cities) {
                    $('#select-city').empty(); //remove old entries first
                    for (var city in data.cities) {
                        $option = $('<option>').val(city);
                        $('#select-city').append($option);
                    }
                } 
            });
        }
    });
});

Upvotes: 0

Raffaele
Raffaele

Reputation: 20885

Three quick points:

  1. mysql is unsafe (you should use PDO or mysqli instead)
  2. Style should be specified with CSS, not inline
  3. You'd better using an asynchronous request, so the user experience feels quickier

Note that all of the three points above are easily achievable by using a framework (at least on the client side, like CakePHP or Zend, but you can also look for a Javascript one like Knockout.js for the UI interactions)

Upvotes: 0

Matanya
Matanya

Reputation: 6346

You can use AJAX to fetch the cities for the selected state. Something like:

$("select#state").change(
function(){
   var state = $(this).val();
   $.ajax({
  type: "GET",
  url: "get_cities.php/?state=" + state, 
// write a query according to the state selected and return the HTML for the OPTION's
  success: function(cities){
    $("select#cities").html(cities);
   }
}); 
}
);

You can also return a json object (in which case don't forget to add dataType:"json") and make the transition to HTML in the client-side, i.e inside the success function

Upvotes: 3

AlexZam
AlexZam

Reputation: 1196

That is the wrong way. Your PHP code is fully executed before showing the page to user. So second query can never know that user choses something.

Right way #1: Do it in two pages. First page contains first combo and when it is submitted second page is generated and shows the second combo.

Right way #2 although not optimal: Do it in one page. Load all possible records for second combo to some JS array. Place listener to first array. When user choses something fill second combo with right records from JS-array.

Right way #3 (most right of them): Do it in a page with AJAX-request in it. User selects a value in the first combo. Its listener sends a request to some server script which returns JSON-object with records for second combo.

Upvotes: 6

Related Questions