How to use an html list box to populate another list box

[maxamis4]

Hello,

I am currently building a php website. My input form has a list box which I have populated using mysql. What i want to do is populate another list box using the first list box.

Both boxes will be pulling from a my sql database but one will use the first box as a filter. After doing some research in google I found that ajax would be the best way to do this. I however can not find a very simple step by step on how to do this.

I do understand that PHP is server side and i do understand that I need to reload the page. My problem is how do I force a reload on selection (obviously ajax is the way) and how to do it. Does anyone have any examples on how to do this?

[Dormilich]

if you want to use AJAX, then you don’t need a reload (the one excludes the other).

if you’re using reloads, it is just a matter of passing around the value of the first select to the builder of the second.

(common search term: combo box)

copied from my page (reload based, should give you an impression):

<?php
    $opt2 = $opt3 = "";
    try
    {
        if (isset($_POST['play']))
        {
            $opt2 = new Option(getAkte((int) $_POST['play']), "akt");
            $opt2->indentation = 6;
        }
        if (isset($_POST['akt']))
        {
            $opt3 = new Option(getSzenen((int) $_POST['akt']), "szene");
            $opt3->indentation = 6;
        }
    }
    catch (Exception $e)
    {
        echo '<p class="errmsg mitte">Keine Daten vorhanden.</p>';
    }
?>
            <fieldset>
                <legend>Szene</legend>
                <div class="tbl">
                    <label for="akt">Akt</label>
                    <br/>
                    <select name="akt" id="act" size="5" style="width: 6em;">
<?php echo $opt2; ?>
                    </select>
                </div>
                <div class="tbl">
                    <label for="scene">Szene</label>
                    <br/>
                    <select name="szene" id="scene" size="5" style="width: 6em;">
<?php echo $opt3; ?>
                    </select>
                </div>
            </fieldset>
 

[maxamis4]

Want to give my thanks to web3schools for the example code http://www.w3schools.com/php/php_ajax_database.asp


Dormilich tried to make sense on what you put up and then look around again. Seems like the option everyone recommended was ajax. This is what I have so far:

In the head of my code I have the following

The current page being view is blank_risk_form.php. i would like to stay on one page if possible

<SCRIPT language="javascript" type='text/javascript'>
 
 
//==================================COMBO AUTO GENERATE=====================================
    function showUser(str)
    {
        if (str=="")
          {
 
            document.getElementById("txtHint").innerHTML="";
              return;
          } 
        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)
            {
                document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
               }
         }
        xmlhttp.open("GET","blank_risk_form.php?dep_code="+str,true);
        xmlhttp.send();
    }
 
</SCRIPT>    
 
 

in the main body I have the following:

<?php
    //Include database connection details
    require_once('configrisks.php');
 
    //Array to store validation errors
    $errmsg_arr = array();
 
    //Validation error flag
    $errflag = false;
 
    //Connect to mysql server
    $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
    if(!$link) {
        die('Failed to connect to server: ' . mysql_error());
    }
 
    //Select database
    $db = mysql_select_db(DB_DATABASE);
    if(!$db) {
        die("Unable to select database");
    }
 
    //Function to sanitize values received from the form. Prevents SQL injection
    function clean($str) {
        $str = @trim($str);
        if(get_magic_quotes_gpc()) {
            $str = stripslashes($str);
        }
        return mysql_real_escape_string($str);
    }
 
 
    //************************************************************************************
    //                                        BUILD ARRAYS
    //====================================================================================
 
 
//================================ARRAY 3===================================
        //CREATE DROP DOWNS FOR FORM USAGE
    $sql = "SELECT code, des FROM tbl_department WHERE proj_code = 'risk' AND active = 1";
    $dep = mysql_query($sql);
    if($dep) 
    {
        $dep_num = mysql_num_rows($dep);
 
 
        //Loop through data array
        while($row=mysql_fetch_assoc($dep))
        {
            $dep_array_code[]=$row["code"];
            $dep_array_des[]=$row["des"];
        };
 
    }
    else
    {
 
        echo "Array3";
        die('Error reading user data');
    }
 
    //================================ARRAY 4===================================
    //CREATE DROP DOWNS FOR FORM USAGE
 
    $dep_code=$_GET['dep_code'];
    echo 'This is my department code'.$dep_code;
    echo '<br>';
 
    if(isset($dep_code))
    {
        $sql="SELECT id, realname, firstname FROM tbl_users WHERE ((active = 1) AND (st_manager=1 OR rsk_manager =1)) AND dep_code = '$dep_code' ORDER BY realname";
    }
    else
    {
        $sql="SELECT * FROM tbl_users WHERE id = -1"; 
    }
 
    $user = mysql_query($sql);
    if($dep) 
    {
        $user_num = mysql_num_rows($user);
 
 
        //Loop through data array
        while($row=mysql_fetch_assoc($user))
        {
            $user_array_id[]=$row["id"];
            $user_array_last[]=$row["realname"];
            $user_array_first[]=$row["firstname"];
 
        };
 
    }
    else
    {
 
        echo "Array4";
        die('Error reading user data');
    }
 
?>    
 

I set the value to negative one in order to keep the drop down empty. Don't know if thats even necessary but it just seemed right.

For the html part I did the following

<FORM NAME="blank_risk_form" id="blank_risk_form" method="post" action="submitnewrisk.php" onsubmit="return validate_form(this)">
 
 

Please note that the form is used for the rest of the page with other variables. I am showing this form in the case that someone says that I need a form.


This is the first drop down that returns the value that query's the second drop down and calls the javascript function. Here is where I don't see it working and where I need help

<th scope="col"><select name="cbo_subtsk" id="select" onchange="showUser(this.value)">
              <?php
                    //BUILD DROP DOWN LIST
 
                    //FOR STATEMENT 1 START
                        echo '<option value=""></option>';
                        for ($array_1index=0; $array_1index < $dep_num; $array_1index += 1) 
                        {
 
                                echo '<option value='; 
                                echo $dep_array_code[$array_1index];
                                echo '>'; 
                                echo $dep_array_des[$array_1index]; 
                                echo '</option>';
 
                        }//FOR STATEMENT 1 END
 
                    ?>
            </select></th>
 
 

The second drop down should populate in theory based on the one above which is where I am having problems

 
 <th scope="col"><span class="style4">
              <select name="cbo_owner" id="select9" >
                <?php
                    //BUILD DROP DOWN LIST
 
                    //FOR STATEMENT 1 START
                    echo '<option value=""></option>';
                        for ($array_1index=0; $array_1index < $user_num; $array_1index += 1) 
                        {
 
                                echo '<option value='; 
                                echo $user_array_id[$array_1index];
                                echo '>'; 
                                echo $user_array_last[$array_1index];
                                echo ", ";
                                echo $user_array_first[$array_1index];
                                echo '</option>';
 
                        }//FOR STATEMENT 1 END
 
                    ?>
              </select>
            </span></th>
 
 

At this point the only thing I can say is that I am not sure why its not working. Any assistance on how to trouble shoot it would be greatly appreciated it

[Dormilich]

i would like to stay on one page if possible

this approach doesn’t work with AJAX. you need to call another page.

doing the AJAX way, you query a script, which returns you the data you need for the second <select>. this may either be pure data (building the <option> elements in JS) or a ready made chunk of HTML to directly inject in the document.

[maxamis4]

@Dormilich
Based on what you have mentioned it seems that reload is the only way in order to stay on the same page. Do you happen to have a couple links that I could use to create this? I know your example outlines the post method but I am still a little blurry on how I can use the "on change" action to re-query the page. The other part I am worried about is the javascript validation that I have on the page. Would the reload trigger the validation part?

I get the concept that the validation is only triggered on submit based on the form so my guess is that it would not, but I would rather ask the question than lead anyone else to another question.

[Dormilich]

Based on what you have mentioned it seems that reload is the only way in order to stay on the same page.

no. you’re misunderstanding AJAX. AJAX is just a function that does a server request. because you do not do any page reloads, you stay at the same page. and because of that you can’t use the same page as AJAX’s URL target.