Dynamic Drop downs - SQL backend 1

[Spidy6123]

Hi,

I'm trying to make a Province and City form with select lists.

For example, based on the Province selection.. will change the available values in City list.

I have the lists boxes pulling from SQL backend .. I just dont know how to apply the criteria.. I understand I have to use an onchange event to call the javascript that will repopulate and apply the criteria to the second box..

I was hoping that you guys could help me with the java part?

Here is my code:
Produce List:

$eclassf_Provincelist = "<select class='tbox' name='eclassf_province' onchange='repopulateAvailability()'>";
    $eclassf_arg = "select Distinct Country, Province from `rbidder_cities` where Country = 'Canada'";
    if ($sql->db_Select_gen($eclassf_arg, false))
    {
        $eclassf_current = "";
        while ($eclassf_row = $sql->db_Fetch())
        {
            if ($eclassf_current != $eclassf_row['Province'])
            {
                $eclassf_current = $eclassf_row['Province'];
                //$eclassf_Provincelist .= "<option value='0' disabled='disabled'>" . $eclassf_row['Province'] . "</option>";
            }
            $eclassf_Provincelist .= "<option value='" . $eclassf_row['Province'] . "'";
            if ($eclassf_row['Province'] == $eclassf_province)
            {
                $eclassf_Provincelist .= " selected='selected'";
            }

            $eclassf_Provincelist .= ">" . $eclassf_row['Province'] . "</option>";
            # print "<br>".$eclassf_current. "- " . $eclassf_row['eclassf_subname']  ;
        } // while
        $eclassf_Provincelist .= "</select>";
    }

    else
    {
        $eclassf_Provincelist .= "<option value='0' >".ECLASSF_51."</select>";
    }
    
    $eclassf_Citylist = "<select class='tbox' name='eclassf_City'>";
    $eclassf_arg = "select Province, City from `rbidder_cities` where Province = '$eclassf_province'";
    if ($sql->db_Select_gen($eclassf_arg, false))
    {
        $eclassf_current = "";
        while ($eclassf_row = $sql->db_Fetch())
        {
            if ($eclassf_current != $eclassf_row['City'])
            {
                $eclassf_current = $eclassf_row['City'];
                //$eclassf_Citylist .= "<option value='0' disabled='disabled'>" . $eclassf_row['City'] . "</option>";
            }
            $eclassf_Citylist .= "<option value='" . $eclassf_row['City'] . "'";
            if ($eclassf_row['City'] == $eclassf_City)
            {
                $eclassf_Citylist .= " selected='selected'";
            }

            $eclassf_Citylist .= ">" . $eclassf_row['City'] . "</option>";
            # print "<br>".$eclassf_current. "- " . $eclassf_row['eclassf_subname']  ;
        } // while
        $eclassf_Citylist .= "</select>";
    }

    else
    {
        $eclassf_Citylist .= "<option value='0' >".ECLASSF_51."</select>";
    }

 

[jpadie]

ok, here's the process.

1. create the first select box. give it an id of selectOne
2. create the second select box with no options. give it an id of selectTwo. or if you want it prepopulated then give it the options of the currently selected value of selectOne at window load time.
3. attach a function to the onChange or onBlur event of selectOne. the html would be something like: 'onchange="getSelectTwoValues(this.value);"'
4. create some javascript that looks like this and bung it in the head part of the page
change the url to the url of the php script referred to in point 5

var xo = null;
var url = '';

function createObject(){
	var browser = navigator.appName;
    if(browser == "Microsoft Internet Explorer"){
        xo = new ActiveXObject("Microsoft.XMLHTTP");
    }else{
        xo = new XMLHttpRequest();
    }
	
}
function getSelectTwoValues(val){
	if (!xo) {createObject();}
	xo.onreadystatechange = function () {
			if (xo.readyState == 4) {
			
					if(xo.responseText == ''){
						//do nothing
					} else {
						fillSelectTwo(xo.responseText)
					}
				
			}
		};
	try {
		xo.open("POST",url, true);
		} catch (e) {	
		console.log('problem in open command') ;
	}
	try {
		xo.setRequestHeader("Content-type", "application/x-[URL unfurl="true"]www-form-urlencoded");[/URL]
		xo.send('value='+val);
	} catch (E) {
		console.log('problem retrieving new chats');
	}
}
function fillSelectTwo(options){
 document.getElementById('selectTwo').innerHTML = options;
}

5. create a receiving php script that should look something like this:

if (!isset($_POST['value'])) die ('');
$sql = "select Province, City from `rbidder_cities` where Province = '".mysql_escape_string($_POST['value'])."'";
$result = mysql_query($sql) or die(mysql_error());
$return ='';
while ($row-mysql_fetch_assoc($result))){
 $return .= "\t\t<option value=\"{$row['City']}\">{$row['City']}</option>\r\n";
}
echo $return;
exit();

 

[Spidy6123]

Sorry for the extremely delayed response on this..

I'm sorry to sound thick.. I'm just not sure I even understand the concept..

So the connection to the SQL server is established where?

 

[Bertiethedog]

jPadie

I hope nobody minds as this isn't my thread.

I have been trying to do something very similar & have been looking for an example to modify.
Thanks very much

Richard

 

[jpadie]

Bertie

note that the console.log functions should be commented out. they are browser specific and for debugging.

 

[Bertiethedog]

I can't get the following code to trigger with a dropdown menu.

Its usually something blindingly obvious but can someone please enlighten me

Richard

*-* php code above here (session variables)
?>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"[URL unfurl="true"]http://www.w3.org/TR/html4/loose.dtd">[/URL]
<html>
<head>

<script language = "javascript">
var xo = null;
var url = '';

function createObject(){
    var browser = navigator.appName;
    if(browser == "Microsoft Internet Explorer"){
        xo = new ActiveXObject("Microsoft.XMLHTTP");
    }else{
        xo = new XMLHttpRequest();
    }
    
}
function getSelectTwoValues(val){
    if (!xo) {createObject();}
    xo.onreadystatechange = function () {
            if (xo.readyState == 4) {
					       
                    if(xo.responseText == '')
						{xo.innerHTML="Please wait";}
					else
						{fillSelectTwo(xo.responseText)}
                
            }
        };
    try {
        xo.open("POST",getjob.php, true);
        } catch (e) {    
        console.log('problem in open command') ;
    }
    try {
        xo.setRequestHeader("Content-type", "application/x-[URL unfurl="true"]www-form-urlencoded");[/URL]
        xo.send('value='+val);
    } catch (E) {
        console.log('problem retrieving new chats');
    }
}
function fillSelectTwo(options){
 document.getElementById('selectTwo').innerHTML = options;
}

 </script>
  </head>
 <form name="form1" method="post" action="">
   <p align="center" class="style1"><u>Creating Credit Notes </u></p>
   <p> Select Customer <select onchange="getSelectTwoValues(this.value);"  
 name="selectOne" id="selectOne">
     <option>test1</option>
     <option>test2</option>
   </select></p>

 

[jpadie]

without testing your code, here are a few thoughts:

1. set an explicit value attribute in your option tags
2. this anonymous function cannot be correct

if (xo.readyState == 4) {
                           
                    if(xo.responseText == '')
                        {xo.innerHTML="Please wait";}
                    else
                        {fillSelectTwo(xo.responseText)}
                
            }

xo is an xmlhttpobject. it does not have an innerHTML attribute. i think you should be addressing a node in the DOM rather than XO.
also, if readyState == 4 then 'please wait' is unlikely to be a helpful message - the response has come back and it's blank...

3. the call to the fillSelectTwo() function, should have a semicolon after it.

4. this won't work

        xo.open("POST",getjob.php, true);

the url should be in quotes

5. get rid of the console.log functions unless you are using firefox and have the firebug extension installed.

6. the function fillSelectTwo() references a node in the DOM with an id of 'selectTwo'. i can't see such a node in your code. if you're doing it the way that it looks like you are then you need to create the parent node in the html and then make sure that you are sending correctly formatted html <option>(s) back to the browser (and nothing else).

<select id="selectTwo" name="selectTwo">
</select>

 

[Bertiethedog]

Sorry about the slow response, I was expecting to be "notified" when a reply came in then I realised I didn't start the thread. duh

Thanks for your help jPadie I am sure that I can untangle it now.

Richard