can someone PLEASE help me with drops downs, auto-populating from database

[jbrill]

I posted a few days ago about this same topic in the php forum but never got any real answers so im going to post absolutely everything i have in order to get some answers. I am trying to use a "category" drop down (populated from category table in database), once an item is selected, i would like it to populate the sub-category drop down ( populated from subcategory table in database).

 

Im really stuck here and need some help.

here is my database table info for the two drop downs.

 

table : category

Structure: cat (basic id field) and category ( actual name of the category)

 

table : subcategory

Structure: cat ( corresponds with the above "cat" id field) , subc (the main subcategory table id) and subcat ( the real name of the subcategory)

 

 

any help would be greatly appreciated! i really need to get this done!

thanks so much in advance! 

[RichardRotterdam]

there are 2 options

1. select all database records and put them in javascript arrays or variables

2. use Ajax

[jbrill]

what's the easiest/ best way to go? and any idea where i could find some code...

[arianhojat]

1. if u want to do it old school:

<?php
//fill out your db vars please
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect17!"); 
$db="yourDBname";
mysql_select_db($db) or die ("Unable to select database!"); 

$submitBtn = isset($_POST['submitBtn']) ? $_POST['submitBtn']: NULL;
$catID = isset($_POST['category']) ? $_POST['category']: NULL;
$subcatID = isset($_POST['subcategory']) ? $_POST['category']: NULL;

//error checking be4 process and display form
if(  isset($submitBtn) && (!isset($catID)) )
$error['catIDerror1'] = 'Form was submitted but no category chosen';

if(  isset($submitBtn) && (!isset($subcatID))  )
$error['subcatIDerror1'] = 'Form was submitted but no subcategory chosen';

//process
if( isset($submitBtn) && (!isset($error)) )// if form was submitted and no errors on form (if errors array wasnt set)
{
//do any processing on the form since he clicked submit button, INSERT values into database etc. if thats what your form does
}

//display form
if( isset($error) )//post errors to user so he can fix
{
echo 'Errors occured:<br/>';
foreach($error as $err)
{
   echo '*** Error:'. $err . '<br/>';
}
}

echo '<select name="category" id="category" onchange="this.form.submit();">';
$Query =  "SELECT * FROM category";
$result = mysql_query($Query) or die ("Error in query: $Query. ".mysql_error()); 
while($row = mysql_fetch_array($result)) 
{
$catID_temp = $row['cat'];
$categoryDesc_temp= $row['category'];
echo '<option value="'.$catID_temp .'" '. (($catID_temp==$catID)? ' selected="selected"':'') .'>'.$categoryDesc_temp.'</option>'; //...populate options from database while loop with a query... if form was submitted it also checked if current value in loop $catID_temp equals the value submitted $catID, and if so selects it
}
echo '</select>';

echo '<select name="subcategory" id="subcategory">';
if( isset($catID) ) //if not null, aka the form was posted via submit button or select dropdown onchange. either way a category was selected be4 and user submitted form itself now or just selected the category drop down which also posts the page
{
$Query =  "SELECT * FROM subcategory WHERE cat=".$catID; //the posted catID variable
$result = mysql_query($Query) or die ("Error in query: $Query. ".mysql_error()); 
while($row = mysql_fetch_array($result)) 
{
  $subcatID_temp = $row['subc'];
  $subcategoryDesc_temp= $row['subcat'];
  echo '<option value="'.$subcatID_temp .'"  '. (($subcatID_temp==$subcatID)? ' selected="selected"':'') .'>'.$subcategoryDesc_temp.'</option>';  //...populate options from a database while loop etc with a query
}
}
echo '</select>';


echo '<input type="submit" name="submitBtn" value="submit form" />';

?>

 

 

2. new school:

USE Prototype JS library (prototype.js google it):

And look up how do to AJAX drop downs, the page doesn't need to be refreshed/POSTed if you do this.

 

I have a general AJAX example here:

javascript calls another php page, which returns XML to your javascript, javascript then loops through XML values to populate something on your page. my example doesnt talk about drop down specifically, but might be good to look at to understand it. Prototype bascially simplifies 'AJAX', so u can call a simple function to do all the funky stuff the behind scenes

[adam84]

This can be two different ways:

 

1. PHP - Reloading

The first way is once the user select an option from the 1st combo box, you can resubmit the page with the selected value (probably the id of the selected item). Once the page is submitted and it is loading you can check to see if the GET/POST variable isset. If yes then run the SQL command to populate the 2nd combo box. If its not set then either the variable was never sent or an error occurred.

 

2. Ajax - No page refresh

This method is a little my in depth. It starts off the same except that this method involves JavaScript where the first method doesn’t. Create the 1st combo box. Then instead of creating the 2nd combo box, use either a <DIV> or <SPAN> tag. When the user selects an item from the 1st combo box, use the ONCHANGE event in the <SELECT> tag to call a JavaScript function with the 1st combo box value. The JavaScript function will use Ajax to call a php file, which you will send in the users selected value and load the results into the <DIV>/<SPAN> you create.

 

In the php file, retrieve the variable, create and run the SQL command and to create the results (echo "<SELECT> (loop through the results) </SELECT>").

- More difficult, i can explain a little more clearly if this is what you wanted

 

[jbrill]

I would really like to go the ajax way as long as i an figure it out. Im pretty new to php and new javascript aswell

[adam84]

ajax works by pretty much having two javascript functions and calling the one sendRequest function with your information

[tomfmason]

Good drop down menus are somewhat difficult especially for someone that is new to web development. I would use something like Adobe's Spry Framework to start out with. It has most of what you are going to need including a drop down menu widget. It should be a good place to get you started. It is really pretty easy to work with and the js is straight forward and makes it easy to hand roll your own if you want to.

[arianhojat]

heres example of AJAX in 2 dynamic dropdowns, NOTE: this with Prototype framework which actually makes code easier and 'shorter'.

I also dont do any error checking on values returned, like sometimes my xml might return a value i dont like and want to handle differently, so i have the javascript update the page or alert me and say 'The subcategory description for catID=10 is empty' for example.

There are other frameworks maybe more specialized to work with php, xajax?, but i prefer doing it this way:

 

//Your main HTML form
<?php
	echo '<script type="text/javascript" src="javascript/prototype.js"></script>';//download this file 
	echo '<script type="text/javascript" src="javascript/func.js"></script>';//your file with custom 'how to handle stuff' fucntions

	//..........
	echo '<select name="category" id="category" size="10" onchange="changedCategory(this);">';

	$Query = "SELECT catID, description FROM category ORDER BY description ASC";
	$result = mysql_query($Query) or die ("Error in query: $Query. ".mysql_error()); 		
	while($row = mysql_fetch_array($result)) 
	{
		$description = $row['description'];
		$catID= $row['catID'];
		echo '<option value="'.$catID.'">'.htmlspecialchars( $description ) .'</option>';
	}

	echo '</select>';

	echo '<select name="subcategory" id="subcategory" size="10">';

	echo '</select>';	
?>

//your func.js file:
function changedCategory(selectField)
{
var catID = selectField.value;

clearList( document.getElementById("subcategory") ); //this is a helper function i use to clear the list, be4 i add stuff to it (cause it might be filled from previous AJAX request)

makeTempOption();//just a helper function to add an option saying 'Getting info....' be4 gets actual data


var rand = Math.floor(Math.random()*1000); //appends a random variable to AJAX call so the url is different each time. Otherwise it might return the xml file cached/stored in browsers 'memory' from previous request
var url = "getInfo.php"; //this is a php file u make to return the subcategories based on category value u send it in the AJAX request
var params = "catID=" + escape(catID) +"&random="+ rand;//escape() needed if it was text data being sent to escape special characters in the variable
var myAjax = new Ajax.Request( url, {    method:'post', parameters: params, onSuccess: changeCategorySuccess,    onFailure: ajaxFailure }	); //what js fucntions to call if successfully gets response from your script or fails

}

function changeCategorySuccess(transport)
{

var responseXML = transport.responseXML; //the XML text your php file gives back to your javascript
var subcategories = responseXML.getElementsByTagName('s');//name of xml nodes to loop through

clearList( document.getElementById("subcategory") ); //right now it probably has "GETTING INFO..." option in the list to let user know, its getting values, so need to clear it be4 add new stuff

for( var i=0; i < subcategories.length; i++ )
{
	var subcategory = subcategories[i];

	var id = subcategory.getElementsByTagName('i')[0].firstChild.nodeValue; //the ID
	var desc = subcategory.getElementsByTagName('d')[0].firstChild.nodeValue;//the description for current subcategory			

	var optionTemp = document.createElement('option');
	optionTemp.setAttribute("value", id); 
	var textNode = document.createTextNode( desc );
	optionTemp.appendChild( textNode );		
	document.getElementById('subcategory').appendChild( optionTemp ); 

}
}
function ajaxFailure (transport)
{	
alert('Unsuccessful: ' + '[Error ' + transport.status + ' -- ' + transport.statusText +']'); 
}


function makeTempOption()
{
	var optionTemp = document.createElement('option');
	optionTemp.setAttribute("value", '-100'); 
	var textNode = document.createTextNode( 'GETTING INFO...' );
	optionTemp.appendChild( textNode );		
	document.getElementById('requirements').appendChild( optionTemp ); 
}
function clearList(listID)
{
var optionsList = listID.getElementsByTagName('option');

    for (i = optionsList.length - 1; i>=0; i--)
{		
	listID.removeChild( optionsList[i] );
}

}


//your getInfo.php file
<?php 
$catID = $_POST['catID'];

$host = "localhost"; $user = "xxx"; $pass = "yyy";
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect3!"); 
$db = "yourDBname";
mysql_select_db($db) or die ("Unable to select database!"); 

$xml = "<info>";	

$Query = 'SELECT s.subcatID, s.description FROM category c
JOIN subcategory_cat sc ON c.catID=sc.catID
JOIN subcategory s ON sc.subcatID=s.subcatID
WHERE c.catID='. $catID .'
ORDER BY s.description ASC';
$result = mysql_query($Query) or die ("Error in query: $Query. ".mysql_error());
while( $row = mysql_fetch_array($result) )
{
	$catID_temp = $row['subcatID'];
	$catID_description = $row['description'];
	$xml .= '<s>';//i keep the xml names short so xml file is less data to transfer back to you
		$xml .= '<i>'. $catID_temp .'</i>';
		$xml .= '<d>'. htmlspecialchars($catID_description) .'</d>';
	$xml .= '</s>';
}
$xml .= "</info>";

echo $xml; //response sent back to javascript code
?>