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]

First attempt

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]

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.

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 misunderstandin g 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.