dynamic drop down box?

Question

I got a database table called category as shown:

enter image description here

I am trying to do a dynamic drop down box and the index script is shown as:

<?php

try {

$objDb = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$objDb->exec('SET CHARACTER SET utf8');

$sql = "SELECT * 
        FROM `category`
        WHERE `master` = 0";
$statement = $objDb->query($sql);
$list = $statement->fetchAll(PDO::FETCH_ASSOC);

    } catch(PDOException $e) {
echo 'There was a problem';
    }

    ?>
    <!DOCTYPE HTML>
   <html lang="en">
   <head>
<meta charset="utf-8" />
<title>Dependable dropdown menu</title>
<meta name="description" content="Dependable dropdown menu" />
<meta name="keywords" content="Dependable dropdown menu" />
<link href="/css/core.css" rel="stylesheet" type="text/css" />
<!--[if lt IE 9]>
<script src="http://html5shiv.googlecode.com/svn/trunk/html5.js"></script>
<![endif]-->
<script src="/js/jquery-1.6.4.min.js" type="text/javascript"></script>
  <script src="/js/core.js" type="text/javascript"></script>
  </head>
  <body>

  <div id="wrapper">

<form action="" method="post">

    <select name="gender" id="gender" class="update">
        <option value="">Select one</option>
        <?php if (!empty($list)) { ?>
            <?php foreach($list as $row) { ?>
                <option value="<?php echo $row['id']; ?>">
                    <?php echo $row['name']; ?>
                </option>
            <?php } ?>
        <?php } ?>
    </select>

    <select name="category" id="category" class="update"
        disabled="disabled">
        <option value="">----</option>
    </select>

    <select name="colour" id="colour" class="update"
        disabled="disabled">
        <option value="">----</option>
    </select>       
</form>
</div>
</body>
</html>

The update.php is shown as:

<?php
if (!empty($_GET['id']) && !empty($_GET['value'])) {

$id = $_GET['id'];
$value = $_GET['value'];

try {

    $objDb = new PDO('mysql:host=localhost;dbname=test', 'root', '');
    $objDb->exec('SET CHARACTER SET utf8');

    $sql = "SELECT * 
            FROM `category`
            WHERE `master` = ?";
    $statement = $objDb->prepare($sql);
    $statement->execute(array($value));
    $list = $statement->fetchAll(PDO::FETCH_ASSOC);

    if (!empty($list)) {

        $out = array('<option value="">Select one</option>');

        foreach($list as $row) {
            $out[] = '<option        
value="'.$row['id'].'">'.$row['name'].'</option>';
        }

        echo json_encode(array('error' => false, 'list' => implode('', 
$out)));

    } else {
        echo json_encode(array('error' => true));
    }

} catch(PDOException $e) {
    echo json_encode(array('error' => true));
}

} else {
echo json_encode(array('error' => true));
}

The 2nd drop down box is not showing the values dependent on the 1st drop down box as shown:

enter image description here

Can someone help me please.

I don't understand what you are trying to do! Just post relevant code as rendered client side, not the php server side code. BTW, are you using only one table? Purpose of this? — A. Wolff, Jun 04 '13 at 17:39
when the 1st drop down box is selected, the 2nd drop down box should show jumper and shirt. Whichever you select for example, if i now select jumper the 3rd drop down should show the color black. — munue, Jun 04 '13 at 17:45
I got this from this tutorial at http://www.youtube.com/watch?v=ClRZzquik1c — munue, Jun 04 '13 at 17:46
@munue You do not need to submit a form to populate those drop-down boxes. You want each dropdown to be populated based on the choice of the previous dropdown, right? If so, you should use jQuery AJAX instead. My example, below, has a lot of code, but if you carefully follow it through you will see that it does exactly what you want. You will need to use jQuery to replace the 2nd and 3rd dropdown boxes with the new code (you do this in the AJAX `success: function() {blah blah blah}` code). Also, you will need to have two `$.ajax()` code blocks, one for dropdown #1, the other for dropdown #2. — cssyphus, Jun 04 '13 at 18:35

cssyphus · Accepted Answer · 2013-06-04T22:42:22.613

Here is an example that will do what you want. Essentially, you can use jQuery / AJAX to accomplish this.

I updated my example code to match your server login / table / field names, so if you copy/paste these two examples into files (call them tester.php and another_php_file.php) then you should have a fully working example to play with.

I modified my example below to create a second drop-down box, populated with the values found. If you follow the logic line by line, you will see it is actually quite simple. I left in several commented lines that, if uncommented (one at a time) will show you what the script is doing at each stage.

FILE 1 -- TESTER.PHP

<html>
    <head>
        <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
        <script type="text/javascript">
            $(function() {
//alert('Document is ready');

                $('#stSelect').change(function() {
                    var sel_stud = $(this).val();
//alert('You picked: ' + sel_stud);

                    $.ajax({
                        type: "POST",
                        url: "another_php_file.php",
                        data: 'theOption=' + sel_stud,
                        success: function(whatigot) {
//alert('Server-side response: ' + whatigot);
                            $('#LaDIV').html(whatigot);
                            $('#theButton').click(function() {
                                alert('You clicked the button');
                            });
                        } //END success fn
                    }); //END $.ajax
                }); //END dropdown change event
            }); //END document.ready
        </script>
    </head>
<body>

    <select name="students" id="stSelect">
        <option value="">Please Select</option>
        <option value="John">John Doe</option>
        <option value="Mike">Mike Williams</option>
        <option value="Chris">Chris Edwards</option>
    </select>
    <div id="LaDIV"></div>

</body>
</html>

FILE 2 - another_php_file.php

<?php

//Login to database (usually this is stored in a separate php file and included in each file where required)
    $server = 'localhost'; //localhost is the usual name of the server if apache/Linux.
    $login = 'root';
    $pword = '';
    $dbname = 'test';
    mysql_connect($server,$login,$pword) or die($connect_error); //or die(mysql_error());
    mysql_select_db($dbname) or die($connect_error);

//Get value posted in by ajax
    $selStudent = $_POST['theOption'];
    //die('You sent: ' . $selStudent);

//Run DB query
    $query = "SELECT * FROM `category` WHERE `master` = 0";
    $result = mysql_query($query) or die('Fn another_php_file.php ERROR: ' . mysql_error());
    $num_rows_returned = mysql_num_rows($result);
    //die('Query returned ' . $num_rows_returned . ' rows.');

//Prepare response html markup
    $r = '  
            <h1>Found in Database:</h1>
            <select>
    ';

//Parse mysql results and create response string. Response can be an html table, a full page, or just a few characters
    if ($num_rows_returned > 0) {
        while ($row = mysql_fetch_assoc($result)) {
            $r = $r . '<option value="' .$row['id']. '">' . $row['name'] . '</option>';
        }
    } else {
        $r = '<p>No student by that name on staff</p>';
    }

//Add this extra button for fun
    $r = $r . '</select><button id="theButton">Click Me</button>';

//The response echoed below will be inserted into the 
    echo $r;

To answer your question in the comment: "How do you make the 2nd drop down box populate fields that are only relevant to a selected option from the 1st drop down box?"

A. Inside the .change event for the first dropdown, you read the value of the first dropdown box:

$('#dropdown_id').change(function() { var dd1 = $('#dropdown_id').val(); }

B. In your AJAX code for the above .change() event, you include that variable in the data you are sending to the 2nd .PHP file (in our case, "another_php_file.php")

C. You use that passed-in variable in your mysql query, thereby limiting your results. These results are then passed back to the AJAX function and you can access them in the success: portion of the AJAX function

D. In that success function, you inject code into the DOM with the revised SELECT values.

That is what I am doing in the example posted above:

The user chooses a student name, which fires the jQuery .change() selector
Here is the line where it grabs the option selected by the user:

var sel_stud = $(this).val();
This value is sent to another_php_file.php, via this line of the AJAX code:

data: 'theOption=' + sel_stud,
The receiving file another_php_file.php receives the user's selection here:

$selStudent = $_POST['theOption'];
Var $selStudent (the user's selection posted in via AJAX) is used in the mysql search:

$query = " SELECT * FROM `category` WHERE `master` = 0 AND `name` = '$selStudent' ";

(When changing the example to suit your database, the reference to $selStudent was removed. But this (here, above) is how you would use it).
We now build a new <SELECT> code block, storing the HTML in a variable called $r. When the HTML is fully built, I return the customized code back to the AJAX routine simply by echoing it back:

echo $r;
The received data (the customized <SELECT> code block) is available to us inside the AJAX success: function() {//code block}, and I can inject it into the DOM here:

$('#LaDIV').html(whatigot);

And voila, you now see a second dropdown control customized with values specific to the choice from the first dropdown control.

Works like a non-Microsoft browser.

@ gibberish. How do you make the 2nd drop down box populate fields that are only relevant to a selected option from the 1st drop down box? For e.g. if you select Jon Doe from the 1st drop down, then only male should populate in the 2nd drop down. Or if you select Mike Williams then female should populate on 2nd drop down — munue, Jun 04 '13 at 20:10
Thanks everyone for the help but i managed to fix my problem. Basically I did not specify the path of the update.php in my core.js file — munue, Jun 04 '13 at 21:06

dynamic drop down box?

1 Answers1

Linked