dynamic drop down boxes

Creamy Goodness

hey guys,

i want to create a page that has three drop down boxes on it.

they will all need to be filled by a mySql query (not a problem) but here's where my problem starts (in my head thinking anyways )

i have the first box populated by something like this:

SELECT projectName from projects

i want the second box then to be populated by something like this:

SELECT projectNumber from project WHERE projects = '<what the user selected in box 1>'.

then similarly for the third box i want it to be populated by a query like so:

SELECT status FROM projects WHERE project='<what the user picked in box 1>' && projectNumber ='<what the user picked in box2>'.

and then to create a query that will implement all the above choices.

SELECT * FROM projects WHERE project='value1' && projectNumber='value2' && status='value3'.

i'm using php to access the database.

any help is greatly appreciated.

Atomic Pineapple

have a similiar problem myself in Java at the minute, tried searching the net and sun and countless java forums and no solution seemed to work, In the end the deadline for my project ended and I didnt get it done, so I'm also interested in a solution to this, sorry I cant be of any help

Goodshape

I did something similar recently, using Javascript to call a PHP function based on the list item selected, which then returns a second list (or input box.. or, actually, whatever you want it to).

This was my solution :

HTML :

<select name="r1" onchange="searchSelect(this.value,'1');">
	<option value="">(Please Select)</option>
	<option value="1" SELECTED>Option 1</option>
	<option value="4" >Option 2</option>
	<option value="3" >Option 3</option>
	<option value="2" >Option 4</option>
	<option value="5" >Option 5</option>
</select>

<div id="searchRowInner1">&nbsp;</div>

The above HTML will pass the value of the select option (numbers 1 to 5) and the number 1 (which relates to searchRowInner1) to the Javascript funtion, searchSelect.


Javascript :

function searchSelect(thevar,rownum)
  {
  var xmlHttp;
  var nextrow;
  nextrow = rownum + 1;
  try
    {
    // Firefox, Opera 8.0+, Safari
    xmlHttp=new XMLHttpRequest();
    }
  catch (e)
    {
    // Internet Explorer
    try
      {
      xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
      }
    catch (e)
      {
      try
        {
        xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
        }
      catch (e)
        {
        alert("Your browser does not support AJAX!");
        return false;
        }
      }
    }
    xmlHttp.onreadystatechange=function()
      {
      if(xmlHttp.readyState==4)
        {
         document.getElementById('searchRowInner'+rownum).innerHTML=xmlHttp.responseText;
        }
      }
    xmlHttp.open("GET","index.php?t=searchSelect&rownum="+rownum+"&qs="+thevar,true);
    xmlHttp.send(null);
  }

Then the Javascript sends those values to index.php as GET variables, and fills the correct row with the HTML generated as a result. Simple if ($_GET == 1) style stuff exists within index.php ...which should allow you to run the correct SQL query, or do whatever else you need to, before generating new HTML.

It's a bit rough and ready as it is but it could give you a starting point, or at least demonstrate one possible method.


//edit
In my generated HTML I also included a new select box with a new searchRowInner and incrementally increased the searchRowInner number and any other number relating to it -- meaning the javascript can then fill in searchRowInner2, searchRowInner3, etc.

Creamy Goodness

hmm, was kind of thinking and hoping it there'd be a non ajax solution, i've never done it before and i'm not the best at javascripting either.

i took a tutorial off w3schools and i'm working on something at the moment, i've got a feeling i should get it working but if not i shall be back.

Thanks guys.

randombar

Hi Cremo,
I reckon I'd have about the same knowledge as yourself at the javascript.
I would really recommend getting into the ajax, helps a lot and really isnt that difficult to get your head around even with basic javascript knowledge!
Gary

Liam Byrne

You can do it without AJAX, but it involves refreshing the whole page, which is viewed as nasty nowadays.....

My recommendation is to do it with AJAX, but basically the non-AJAX version has an onclick that submits the form to the same page, sets the currently-selected item in the box the user clicked on and loads the second select box (if the appropriate GET variable is set).

Not pretty, but it works.

P.S. Check out jQuery for doing AJAX calls much easier!!!

Adam

Yep, basically your options are AJAX or pages being resent with each drop down selection which, as has been stated, is considered fairly nasty. On top of which, using the page refresh method will still require javascript, unless you want your users to have to select an option and then hit a submit button to send the page, which is even uglier. So your best option is AJAX imo, and isbecoming more and more mainstream these days and needs to be embraced rather than shied away from.

Liam Byrne

Mirror wrote: »

On top of which, using the page refresh method will still require javascript, unless you want your users to have to select an option and then hit a submit button to send the page, which is even uglier.

That last bit isn't really an issue, Mirror, as AJAX will require JavaScript anyways....

Adam

Liam Byrne wrote: »

That last bit isn't really an issue, Mirror, as AJAX will require JavaScript anyways....

Ah, this was my point, the choice was either the three refreshes or two javascript based alternatives, with AJAX being the more user friendly option imo. I went for dinner half way through the post so probably didn't make as much sense as I had hoped!

Liam Byrne

Mirror wrote: »

Ah, this was my point, the choice was either the three refreshes or two javascript based alternatives, with AJAX being the more user friendly option imo. I went for dinner half way through the post so probably didn't make as much sense as I had hoped!

So, to clarify :

Preferred Option:
AJAX (i.e. Javascript) with no page refresh

Option 2:
onchange trigger (i.e. Javascript) to refresh page (probably the preferred method pre-ajax)

Option 3:
select + submit button to refresh page

What I was getting at, though, is that if you have JavaScript I wouldn't really bother considering Option 2 at all......I only said it because it was specifically asked if AJAX was the only option.

Option 3 is even messier/less intuitive than Option 2, and should be the immediate "forget about this one", but as Mirror said it's probably useful as a no-JavaScript fallback, if one is required....

Webmonkey

Cremo you should learn AJAX - it is surprizenly easy and very repeatitive once you know it once. You'll have AJAX learned in the time you spend messing with another solution and you can use AJAX in later projects

Creamy Goodness

lol i've nothing against AJAX it was just i thought with a problem like this - which is so common in web dev - that there might of been a more easier solution.

i've started the AJAX i'm running into a little bit of a snag.

i have the first drop down list working perfectly, giving me a list of projects.

when a user selects a project a second drop down list appears containing a list of certain products that are associated with that project.

when a user selects from the second drop down list, a table is displayed (showTable.php).

this works grand but the only problem is that the select drop down box disappears when i select from the second box.

here's my code

ajaxexample.php: (site i call the ajax from).
[php]
<?php
include 'dbconnect.inc.php';
echo '<html>
<head>
<script src="select.js"></script>
</head>
<body>

<form>
' . selectProjectBox() . '
</form>

<p>
<div id="txtHint"><b>User info will be listed here.</b></div>
</p>

</body>
</html>';




//
//PHP function to display drop down box.
//
//

function selectProjectBox()
{
$query = "SELECT project FROM trs GROUP BY project";
echo "<select id=\"project\" onchange=\"showProduct(this.value)\">\n<option selected=\"Selected\">-</option>\n";
$result = mysql_query($query) or die ( mysql_error() );
while( $row = mysql_fetch_array($result) )
{
$selectOption = $row;
$html.="<option id=\"$selectOption\">$selectOption</option>\n";
}
$html.="</select>\n";
echo $html;
}
[/php]


select.js: (my ajax script).

var xmlHttp

function showProduct(project)
{ 
	xmlHttp=GetXmlHttpObject()
	if (xmlHttp==null)
	{
 		alert ("Browser does not support HTTP Request")
 		return
	}

	var url="showTable.php"
	url=url+"?project="+project
	url=url+"&sid="+Math.random()
	xmlHttp.onreadystatechange=stateChanged 
	xmlHttp.open("GET",url,true)
	xmlHttp.send(null)
}

function showTable()
{
	var project = document.getElementById("project").value;
	var product = document.getElementById("product").value;

	xmlHttp =  GetXmlHttpObject();
	if(xmlHttp == null)
	{
		alert("Browser does not support HTTP Request");
		return;
	}
	
	var url = "showTable.php";
	url=url+"?project="+project+"&product="+product;
	url=url+"&sid="+Math.random();
	xmlHttp.onreadystatechange=stateChanged ;
	xmlHttp.open("GET",url,true);
	xmlHttp.send(null);
}

function stateChanged() 
{ 
	if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
	{ 
		document.getElementById("txtHint").innerHTML=xmlHttp.responseText 
	} 
}

function GetXmlHttpObject()
{
	var xmlHttp=null;
	try
 	{
 		// Firefox, Opera 8.0+, Safari
 		xmlHttp=new XMLHttpRequest();
 	}
	catch (e)
 	{
 		//Internet Explorer
 		try
  		{
  			xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
  		}
 		catch (e)
  		{
  			xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
 	 	}
 	}
	return xmlHttp;
}

showTable.php: (populates the second drop down list and displays table when a option is selected from second list).
[php]
<?php

include 'dbconnect.inc.php';
include 'header.html';

$project=$_GET["project"];
$product=$_GET["product"];

if( isset($project) && !( isset($product)) )
{
$sql="SELECT product FROM project WHERE project = '".$project."' GROUP BY product";

$result = mysql_query($sql);

echo "<select id=\"product\" onchange=\"showTable()\">\n<option selected=\"selected\">-</option>\n";

while($row = mysql_fetch_array($result))
{
$selectOption = $row;
$html.="<option id=\"$selectOption\">$selectOption</option>\n";
}

}

$html.="</select>\n";
echo $html;





if( isset($project) && isset($product) )
{
$query = "SELECT ID, project, product, productNumber, slogan, origin, status, newstatus, submitDate FROM project WHERE project='$project' && product='$product'";

$result = mysql_query($query) or die ( mysql_error() );

//display the tr's in a table.
echo '<div id="tr_div">
<table class="stats sortable" id="tr_table">
<tr>
<td class="hed"><b>ID</b></td>
<td class="hed"><b>Project</b></td>
<td class="hed"><b>Product</b></td>
<td class="hed"><b>Product No.</b></td>
<td class="hed"><b>Slogan</b></td>
<td class="hed"><b>Origination</b></td>
<td class="hed"><b>Status</b></td>
<td class="hed"><b>New Status</b></td>
<td class="hed"><b>Submitted Date</b></td>
</tr>
';
while( $row = mysql_fetch_array($result) )
{
echo "<tr><td>" . $row . "</td><td>" . $row . "</td><td>" . $row . "</td><td>" . $row . "</td><td nowrap=\"nowrap\">" . $row . "</td><td>" . $row . "</td><td>" . $row . "</td><td>" . $row . "</td><td>" . $row . "</td></tr>\n" ;
}
echo "</table>";
}
?>
[/php]

Webmonkey

You are forgetting your simi colons here:

	xmlHttp.onreadystatechange=stateChanged 
	xmlHttp.open("GET",url,true)
	xmlHttp.send(null)

This could cause javascript error setting the content of the Div to nothing hense why table dissapears. Open the Error console on Firefox to see what is going on.


Edit - you appear to be missing lots of simi-colons - I'm sure you've noticed that :P

Adam

Webmonkey wrote: »

You are forgetting your simi colons here:

	xmlHttp.onreadystatechange=stateChanged 
	xmlHttp.open("GET",url,true)
	xmlHttp.send(null)

This could cause javascript error setting the content of the Div to nothing hense why table dissapears. Open the Error console on Firefox to see what is going on.


Edit - you appear to be missing lots of simi-colons - I'm sure you've noticed that :P

Just because I'd be embarrassed if it were me... - It's semi - colons

Creamy Goodness

yup i knew those semi-colons were missing

it's not the table that disappears, it's the second drop down box,

i've modified from my previous question with 3 boxes to just two so it's now like this.

select box 1 >> select a project.
select box 2 >> select a product (populated depending on select in box 1)

print table of information.

i've attached some images to clear this up:

1.bmp - this is that starting point.

2.bmp - this is after user selects from first box.

3.bmp - this is after user selects from second box

Adam

It's quite simple really. Look at your file showTable.php. You have specified that if project is set and product is not, then display the second select box. You then go on to say if both are set, display the table. So the criteria required to display the table (i.e. product being set) is actually preventing the select box from being displayed because it requires product to not be set. Just check that $project is set on the first if() statement, should solve your problem.

Webmonkey

Mirror wrote: »

Just because I'd be embarrassed if it were me... - It's semi - colons

I can't spell

Creamy Goodness

lol i had just noticed that about 30 seconds ago, d'oh.

all is well now guys, thanks.

no doubt i'll probably be back with another problem soon.

Adam

No probs, good luck with it!

Creamy Goodness

bit odd but this doesn't work in IE6 (needs too because i'm going to have ~50 people all using IE6 using it)

when you select an option from the first box, the second box appears but doesn't get populated.

is there any debugger i can get for IE? i have firebug for firefox and usually will put a warning or error there that when fixed will make the site work in IE but not so this time, all i'm getting are sucessful GET request confimations.

phil

I haven't gone down through the code, so maybe someone can help you specifically, but IMHO in future get a Javascript library to cut down on the cross-browser issues and the learning curve associated with writing your own javascript from scratch.

I can't recommend JQuery enough for this.

Adam

I don't know js in the true sense of knowing, but a quick google gave me this:

"In Internet Explorer, you create the object using new ActiveXObject("Msxml2.XMLHTTP") or new ActiveXObject("Microsoft.XMLHTTP") depending on the version of MSXML installed."

Perhaps then, try the alternative ActiveXObject("Microsoft.XMLHTTP")?

Liam Byrne

Try this:

http://remysharp.com/2007/01/20/auto-populating-select-boxes-using-jquery-ajax/

mewso

Depending on the amount of options the other way to do it would be to fill the selects with all options and group them (<optgroup label="group1">) according to their parent then use script on the client side to filter these results using arrays to hold the items for each group and so on. Ajax is fine but try turning of script in firefox on this site for example just to see how the web is slowly being strangled by it's over use. Also a topic rarely discussed in these forums is unobtrusive scripting. It would be really nice to see more people advocating it.

Creamy Goodness

ok i found the problem (half the battle - other is to fix it ) it turns out that the value in the first select box isn't being passed to the javascript function using showProduct(this.value);

totally strange

Creamy Goodness

ok still having problems in IE 6.0

i call a javascript function like so:

<select id="project" name="project" onchange="showSubsystem(this.value);">
*options go in here*
</select>

and i have the javascript function like so:

showSubsystem(projectvalue)
{
   alert(projectvalue);
}

when i run that i get an empty alert box. but when it comes to firefox i get an alert box with whichever value is inside projectvalue.

can anyone shed any light on this as it's got me completely stumped.

mewso

Much as I hate inline script I would suggest you try:-

onchange="showSubsystem(this.options[this.selectedIndex].value);"

Adam

Try changing "this.value" to just "this" in the html.

Creamy Goodness

musician wrote: »

Much as I hate inline script I would suggest you try:-

onchange="showSubsystem(this.options[this.selectedIndex].value);"

when i use the above i get the same alert box as before.

Mirror wrote: »

Try changing "this.value" to just "this" in the html.

when i use just "this" i get [Object] in the alert box.

mewso

How about showSubsystem(this); and

showSubsystem(projectvalue)
{
alert(projectvalue.options[projectvalue.selectedIndex].value);
}

Creamy Goodness

musician wrote: »

How about showSubsystem(this); and

showSubsystem(projectvalue)
{
alert(projectvalue.options[projectvalue.selectedIndex].value);
}

nope still get the same, blank alert in IE non blank in FF.