Dynamic drop down box

Codewalkers Forums Sponsor:

October 29th, 2009, 02:22 PM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

Dynamic drop down box

Hey,

I need to have 2 drop-down boxes, where the second depends by the selection made in first.

I'm done with populating the 1st drop-down box from MS sql server. Now when the client selects in first drop-down box the second box has to be populated with one related to first which I pull out the details from same SQL server.

I want both the boxes on same PHP page.

Any help!

October 29th, 2009, 07:21 PM

MatthewJ

Contributing User

Join Date: May 2007

Location: Davenport, Iowa

Posts: 564

Time spent in forums: 1 Week 21 h 23 m 45 sec
Reputation Power: 3

http://www.skillfusion.com/articles/ajaxDropdown.php

October 30th, 2009, 09:13 AM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

Hey,

I'm totally blank about Ajax. Is it not possible to do with PHP? coz I need to populate the second box from MS SQL db depending on first box selection.

Thanks!

October 30th, 2009, 11:07 AM

MatthewJ

Contributing User

Join Date: May 2007

Location: Davenport, Iowa

Posts: 564

Time spent in forums: 1 Week 21 h 23 m 45 sec
Reputation Power: 3

You can do it with php by reloading the page which get a little tougher if you want the dropdowns on the same page...

it will be easier with AJAX, just bite the bullet as they say and run through the tutorial. There really isn't anything too "high tech" about AJAX, I'm sure after running through the tutorial you will feel the same way

. If you get stuck on it, come back and ask some more questions.

And technically, you are still using PHP to do it... you are just using ajax to send the second query and retrieve the results.

October 30th, 2009, 11:43 AM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

Hey,

I came across the below code for AJAX in a forum and tried for my requirement but still I couldn't make it work.

drop.php

PHP Code:


		
			
<?php

//db connection 

$link = mssql_connect('$server', 'username', 'password');



if(!$link){

die ('Something went wrong with MS SQL connection');

}



mssql_select_db('Exam', $link);

$options = "";





$q = "select CourseId, course_title from  enrl_Course ORDER BY course_title";

$result = mssql_query($q);

mssql_close($link);



while($row = mssql_fetch_array($result)){

$id = $row["CourseId"];

$title = $row["course_title"];

$options .= "<OPTION VALUE=\"$CourseID\">".$id.",".$title;

}

mssql_close($link);

?>



<html>

<head>

<script type="text/javascript" src="scripts.js"></script>

</head>

<body>

<h5>Select course:</h5>

<form action="page3.php" method="post" id="SelectForm">

<select name ="CourseID" id="course"><OPTION VALUE=0><?=$options?>

</select>

<select name="SessionID" id="sessionroster"><OPTION VALUE=0><?=$options1?>

</select>

<br /><input type="submit" value="Continue">

</form>

</body>

</html>

P.S: action="page3.php" is for different purpose.

The above PHP part works absolutely fine. It populates my Select course drop-down box read from DB.

Next I'm looking for:
The second drop box depends on CourseID of the first box and retrieves the content from same db for second box too.

Here's second PHP code: (drop3.php)

PHP Code:


		
			
<?php

error_reporting(E_ALL & ~E_NOTICE);

ini_set('display_errors', 1);

($courseID = @$_GET['CourseID']) or $courseID = null;

if(!is_numeric($courseID || $courseID <= 0) {

die("<error>Invalid Course ID</error>");

}



//db connection 

$link = mssql_connect('$server', 'username', 'password');



if(!$link){

die ('Something went wrong with MS SQL connection');

}



mssql_select_db('Exam', $link);

$options1 = "";





$query = "select CourseStudentID,course_startdate,course_hours,

            course_Currentenrollment, Comp_PhoneNumber

from  Enrl_CourseStudent

WHERE c_courserid = {$courseID} ORDER BY course_startdate";

$result = mssql_query($query);

$options1 = "";



while($row = mssql_fetch_array($result)){

$id = $row["CourseStudentId"];

$start = $row["course_startdate"];

$hours = $row["crss_hours"];

$options1 .= "<OPTION VALUE=\"$SessionID\">".$id1.",".$start.",".$hours;

}

mssql_close($link);

?>

AJAX code ( I copied from forum/another source ) script.js

Code:

window.onload = function()
{
UpdateCourse();
UpdatesessionRoster(1);
}

function CreateHttpRequest(){
if(window.XMLHttpRequest) {
return new XMLHttpRequest();
}

else if(ActiveXObject) {
return new ActiveXObject("Microsoft.XMLHTTP");
}
else
{
throw new Error("This browser does not support AJAX.");
}
}

function UpdateCourse()
{
//create an ajax option
var xmlHttp = CreateHttpRequest();

//create the action that will be taken when the response has been recieved 
xmlHttp.onreadystatechange = function();
{
if(xmlHttp.readyState == 4 && xmlHttp.responseXML){
var catElement = document.getElementById('course');
while (catElement.options.length > 0) {
catElement.remove(0);
}

//Add the new options 
var optionElements = xmlHttp.responseXML.getElementByTagName('option');  
for(var i=0; i < optionElements.length; i++){
var id = optionElements[i].getElementsByTagName('id')[0].firstChild.nodeValue;
				var name = optionElements[i].getElementsByTagName('name')[0].firstChild.nodeValue;
				catElement.add(new Option(name, id), null);
			}
		}
	}
	
	// Send the request
	xmlHttp.open("GET", "drop.php", true);
	xmlHttp.send(null);
}

function sessionRoster(pcourseID)
{
//create the AJAX object
var xmlHttp = CreateHttpRequest();

//create an action that will be taken when the response has been received
xmlHttp.onreadystatechange = function()
{
if(xmlHttp.raedyState == 4 && xmlHttp.responseXML) {
//load and clear the category element
var rosterElement = document.getElementById('sessionroster');
while (rosterElement.options.length > 0) {
rosterElement.remove(0);
}
//add the new options to the course element
var optionElements = xmlHttp.responseXML.getElementByTagName('option');  
if (optionElements.length == 0) {
				out.innerHTML += xmlHttp.responseText + "<br />";
			}
			for(var i = 0; i < optionElements.length; i++) {
				var id = optionElements[i].getElementsByTagName('id')[0].firstChild.nodeValue;
				var name = optionElements[i].getElementsByTagName('name')[0].firstChild.nodeValue;
				rosterElement.add(new Option(name, id), null);
			}
		}
	}
	
	// Send the request
	xmlHttp.open("GET", "drop3.php?CourseID=" + pcourseID, true);
	xmlHttp.send(null);
}

I want both drop boxes on same page, so when an option is selected in firstbox the second box should be populated with contents related to the CourseID from 1st.

Not sure what I'm missing.

Any Help!

November 1st, 2009, 11:11 AM

MatthewJ

Contributing User

Join Date: May 2007

Location: Davenport, Iowa

Posts: 564

Time spent in forums: 1 Week 21 h 23 m 45 sec
Reputation Power: 3

From looking at it quick, the first thing I see is that you are running your js function to update the second select menu on page load in script.js(which you call scripts.js in your code).

On page load, there should not be anything selected in the first box. I would think that you want to run the js function from the onchange event of the first select menu. So you're telling it, "when someone changes the first menu to something, run the code to get its sub-options from the database.

November 2nd, 2009, 10:42 AM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

Quote:

"when someone changes the first menu to something, run the code to get its sub-options from the database.

This is what exactly, I'm trying to do. Since I'm not good at jQurery and AJAX, I'm finding it kinda difficult to go ahead

November 2nd, 2009, 10:59 AM

MatthewJ

Contributing User

Join Date: May 2007

Location: Davenport, Iowa

Posts: 564

Time spent in forums: 1 Week 21 h 23 m 45 sec
Reputation Power: 3

My suggestion would be to take the time to go through a few basic AJAX tutorials to try and wrap your head around what it is you're trying to do...

It is always a good idea to understand what your code is doing. If I write the solution for you, you're going to be in the same place you are now except you'll have code running in your application that you don't understand. If it breaks, then what?

The concept behind what you're trying to do is not very difficult, what is difficult is trying to combine code snippets from different forums into your own code without understanding what those snippets are doing.

If you follow the link I posted (I know the example page is missing) and download the sample files(.zip), you should be able to look pretty quickly at how they did it and get yours going... In fact, the only thing you would need to do as far as I can tell is replace their data lookup page with your own and rename some things.

If you're still having trouble, I will take a look after work tonight and see if I can give you some direction.

Matt

November 2nd, 2009, 11:18 AM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

Hey Matthew,

Thanks for your suggestion. I'm now looking into AJAX only, I too always prefer to know the entire working of the code before I could run.
I'll run into the .zip file in the link and let you know If I have any questions.

#10

November 2nd, 2009, 01:46 PM

IAmALlama

Join Date: Apr 2007

Location: Seattle, WA

Posts: 1,937

Time spent in forums: 1 Week 5 Days 1 h 54 m 18 sec
Reputation Power: 4

If you don't have a lot of options to switch around you could also just echo out the options into javascript and switch the select boxes with those values. Check out something like this to see what I mean.

Code:

<html>
<head>
<script type='text/javascript'>
var o1 = new Array('test','what','foo','bar');
var o2 = new Array('test2','what2','foo2','bar2');
var o3 = new Array('test3','what3','foo3','bar3');
var o4 = new Array('test4','what4','foo4','bar4');

function sb1Changed(changedTo){
    var Options = new Array();
    var selectBox2 = document.getElementById("selectBox2");
    selectBox2.disabled = false;
    switch(changedTo){
        case 'o1':
            Options = o1;
            break;
        case 'o2':
            Options = o2;
            break;
        case 'o3':
            Options = o3;
            break;
        case 'o4':
            Options = o4;
            break;
        default:
            Options[0] = "Choose from SB1 first";
            selectBox2.disabled = true;
    }
    var o = new Option;
    selectBox2.options.length = 0;
    for(i=0;i<Options.length;i++){
        var o = new Option(Options[i], Options[i]);
        selectBox2.options.add(o);
    }

}
</script>
</head>
<body>
SB1 <select name='selectBox1' id='selectBox1' onChange='javascript:sb1Changed(this.value);'>
  <option>Select an option</option>
  <option value='o1'>Option 1</option>
  <option value='o2'>Option 2</option>
  <option value='o3'>Option 3</option>
  <option value='o4'>Option 4</option>
</select><br />
SB2 <select name='selectBox2' id='selectBox2' disabled='true'>
  <option>Choose from SB1 first</option>
</select>
</body>
</html>

it wouldn't be hard to just echo out the arrays and case statements and let javascript do the work. no need for ajax. However if your lists are fairly large, I wouldn't suggest doing this simply because it will inflate your page size pretty quick. In that case ajax would be the best method because would be loading only what the visitor needs/wants instead of every possible option.

#11

November 2nd, 2009, 02:07 PM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

Hey,

I think AJAX would be better for my case coz I have lots of stuffs to read from the db and populate my second box depending on the first box selection.

Javascript might give me page load ( I meant the 2nd dropbox here ) problem.

So working on AJAX for now to see, whether that will give me a solution in my case.

As mentioned by Matthew, it's only the linking part which kinda tricky and difficult for me.

#12

November 3rd, 2009, 08:55 AM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

Hey,

I'll post what I can do with the example by placing my requirements in it.

drop.php ( code for populating first drop box from db )

PHP Code:


		
			
<?php

error_reporting(E_ALL & ~E_NOTICE);

ini_set('display_errors', 1);



$link = mssql_connect('server', 'username', 'password');



if(!$link)

{

die("Something wrong with the MS SQL connection");

}



mssql_select_db('Exam', $link);

$options = "";



$q = "select CourseId, c_title from Enrl_Course  ORDER BY c_title";

$result = mssql_query($q);



while($row = mssql_fetch_array($result)){

$id = $row["CourseId"];

$title = $row["c_title"];



$options .="<OPTION VALUE=\"$course\">".$id.",".$title;

}



mssql_close($link);

?>









<html>

<head>

<script src="Ajax.js"></script>

</head>



<body>

Select a course:<select name="courseid" id="courseid"  onChange="return courseOnChange()">

<OPTION VALUE=0><?=$options?></select>



<br>

<br>

Select the Roster:<select name="sessionRoster" id="sessionRoster">

</select>

</body>

</html>

ajax-drop.php ( populating 2nd drop-box from db, based on CourseId from 1st box in where statement )

PHP Code:


		
			
<?php

error_reporting(E_ALL & ~E_NOTICE);

ini_set('display_errors', 1);



$link = mssql_connect('server', 'username', 'password');



if(!$link)

{

die('Something went wrong with while connecting to MS SQL');

}



mssql_select_db('Exam', $link);

$options1 = "";



$courseid = $_GET['$id'];







$query = "select SessionID,cr_startdate,c_hours

            [B]WHERE c_coursemasterid = '$courseid' [/B]AND crss_startdate >= getDate()";

$result = mssql_query($query);



while($row = mssql_fetch_array($result)){

$id = $row["SessionId"];

$start = $row["c_startdate"];

$hours = $row["c_hours"];

$options1 .= "<OPTION VALUE=\"$sessionRoster\">".$id.",".$start.",".$hours;

}



mssql_close($link);

?>

Ajax.js ( code I tried the max to use the example .zip I downloaded )

Code:

// Global object for XMLHTTP request object for the server

var XmlHttpObj;

//instance of the XMLHTTPRequest object, which varies with the broswer

function CraeteXmlHttpObj()
{

//Instance of request object for IE broswer
try{
XmlHttpObj = new ActiveXObject("Msxml2.XMLHTTP");
}
catch(e)
{
try
{
XmlHttpObj = new ActiveXObject("Microsoft.XMLHTTP");
}

catch(oc)
{
XmlHttpObj = null;
}
}

//Instance of request object  for mozilla broswer 

if(!XmlHttpObj && typeof XMLHttpRequest != "undefined")
{
XmlHttpObj = new XMLHttpRequest();
}
}



function courseOnChange(){
var courseid = document.getElementById("courseid");

//get selected course from dropdown list

var selectedCourse = courseid.options[courseid.selectedIndex].value;

//url of the page that will send the php data back to the client browser

var requestUrl;


//php URL 

requestUrl = "ajax-drop.php" + encodeURIComponent(selectedCourse);


CreateXmlHttpObj();

//to verify whether varaiable was successfully initialized

if(XmlHttpObj){

//to recieve the data back form the server on StateChangeHandler

XmlHttpObj.onreadystatechange = StateChangeHandler;

//interacts with the server
XmlHttpObj.open("GET", requestUrl, true);

//sends the request to the server
XmlHttpObject.send(null);
}
}



//callback fn for statechangeHandler

function StateChangeHandler()
{

//4 indicates response being recieved from server is compeleted
if(XmlHttpObj.readyState == 4)
{

//to recieve valid response
if(XmlHttpObj.status == 200)
{
PopulatesessionRoster(XmlHttpObj.responseXML.docum  entElement);
}
else
{
alert("Problem retrieving data from the server, status code: " + XmlHttpObj.status);
}
}
}


function PopulatesessionRoster(){


//db code should go here, but not sure how to do it.
}

Please, I'm nearing my deadline. Help me, figure out what I'm missing in my ajax code while populating my second box.

Any help would be great!

#13

November 3rd, 2009, 01:38 PM

MatthewJ

Contributing User

Join Date: May 2007

Location: Davenport, Iowa

Posts: 564

Time spent in forums: 1 Week 21 h 23 m 45 sec
Reputation Power: 3

First, a few things...

I used my mysql test db as I only have a production mssql, that should be pretty easy to change.

Also, I tried to replace all comments to make them align with my changes, if I missed one sorry.

Lastly, I made a rough estimate of your data, but you should be able to look over what the files are doing and see where to change to your fields etc.

index.php

PHP Code:


		
			
<?php

$conn = mysql_connect('localhost', 'root', '');

$db = mysql_select_db('test');



$sql = "SELECT coursename, courseid FROM courses";

$res = mysql_query($sql, $conn);

$row = mysql_fetch_assoc($res);



?>

<html>



    <head>

        <title>Ajax Drop-down List Example</title>

        <script src="js/AjaxCode.js"></script>

    </head>





    <h2>Ajax Cascading Drop-down Example</h2>

    <br>

    <a href="http://devzone.skillfusion.com/">SkillFusion DevZone</a>

    <br>

    <br>

    select a course:

    <br>

    <select name="cList" id="cList" onChange="return CourseListOnChange()">

        <?php do { ?>

            <option value="<?php echo $row['courseid']; ?>"><?php echo $row['coursename']; ?></option>

        <?php } while($row = mysql_fetch_assoc($res)); ?>

    </select>

    

    <br>

    <br>



    <!-- drop down #2, list contents depend on value selected in drop-down #1 -->

    <!-- code in the file js/AjaxCode.js will populate this drop-down list -->

    <select name="courseList" id="courseList" >

    </select>



</html>

xml_data_provider.php

PHP Code:


		
			
<?php



    Header("Content-type: text/xml"); 

    // get query string params

    //This will be your id

    $filter = $_GET['filter'];

    

    //Get the data from the database here

    $conn = mysql_connect('localhost', 'root', '');

    $db = mysql_select_db('test');

    

    $sql = "SELECT * FROM courses WHERE courseid = ".mysql_real_escape_string($_GET['filter']);

    

    $res = mysql_query($sql, $conn);

    $row = mysql_fetch_assoc($res);



    // build xml content for client JavaScript

    $xml = '<courseoptions>';

    do {

        

        $xml .= '<option id="'.$row['id'].'">'.$row['courseid'].', '.$row['startdate'].', '.$row['hours'].'</option>';

        

    } while ($row = mysql_fetch_assoc($res));



    $xml .= '</courseoptions>';



    // send xml to client

    echo( $xml );

    

?>

AjaxCode.js

Code:

// declare a global  XMLHTTP Request object
var XmlHttpObj;

// create an instance of XMLHTTPRequest Object, varies with browser type, try for IE first then Mozilla
function CreateXmlHttpObj()
{
	// try creating for IE (note: we don't know the user's browser type here, just attempting IE first.)
	try
	{
		XmlHttpObj = new ActiveXObject("Msxml2.XMLHTTP");
	}
	catch(e)
	{
		try
		{
			XmlHttpObj = new ActiveXObject("Microsoft.XMLHTTP");
		} 
		catch(oc)
		{
			XmlHttpObj = null;
		}
	}
	// if unable to create using IE specific code then try creating for Mozilla (FireFox) 
	if(!XmlHttpObj && typeof XMLHttpRequest != "undefined") 
	{
		XmlHttpObj = new XMLHttpRequest();
	}
}

// called from onChange or onClick event of the first dropdown list
function CourseListOnChange() 
{
    var cList = document.getElementById("cList");
    
    // get selected course from dropdown list
    var selectedCourse = cList.options[cList.selectedIndex].value;
    
    // url of page that will send xml data back to client browser
    var requestUrl;
    requestUrl = "xml_data_provider.php" + "?filter=" + encodeURIComponent(selectedCourse);
    
	CreateXmlHttpObj();
	
	// verify XmlHttpObj variable was successfully initialized
	if(XmlHttpObj)
	{
        // assign the StateChangeHandler function ( defined below in this file)
        // to be called when the state of the XmlHttpObj changes
        // receiving data back from the server is one such change
		XmlHttpObj.onreadystatechange = StateChangeHandler;
		
		// define the iteraction with the server -- true for as asynchronous.
		XmlHttpObj.open("GET", requestUrl,  true);
		
		// send request to server, null arg  when using "GET"
		XmlHttpObj.send(null);		
	}
}


// this function called when state of  XmlHttpObj changes
// we're interested in the state that indicates data has been
// received from the server
function StateChangeHandler()
{
	// state ==4 indicates receiving response data from server is completed
	if(XmlHttpObj.readyState == 4)
	{
		// To make sure valid response is received from the server, 200 means response received is OK
		if(XmlHttpObj.status == 200)
		{			
			PopulateCourseList(XmlHttpObj.responseXML.document  Element);
		}
		else
		{
			alert("problem retrieving data from the server, status code: "  + XmlHttpObj.status);
		}
	}
}

// populate the contents of the course info dropdown list
function PopulateCourseList(courseNode)
{
	
	courseList.options.length = 0;

	var courseNodes = courseNode.getElementsByTagName('option');
	
	// populate the dropdown list with data from the xml doc
	for (var count = 0; count < courseNodes.length; count++)
	{
   		textValue = GetInnerText(courseNodes[count]);
		idValue = courseNodes[count].getAttribute("id");
		optionItem = new Option( textValue, idValue,  false, false);
		courseList.options[courseList.length] = optionItem;
	}
}

// returns the node text value 
function GetInnerText (node)
{
	 return (node.textContent || node.innerText || node.text) ;
}

I just used two tables, one called courses, one called courseinfo... courses consisted of id, courseid, coursename and courseinfo consisted of id, courseid, startdate, hours

That should give you a pretty good idea.

Last edited by MatthewJ : November 3rd, 2009 at 01:42 PM.

#14

November 3rd, 2009, 02:27 PM

Naughty

Contributing User

Join Date: Jul 2009

Posts: 83

Time spent in forums: 13 h 53 m 8 sec
Reputation Power: 1

Hey,

Alright first drop box is working. I think my where statement in the second drop box is wrong. I'll clearly state which db and table I retrieve the contents from.

I have a db called "EXAM" and I retrive the contents for 1st drop-box from the table "Enrl_CourseMaster", this is where I populate with courseid, course_title and location.

For 2nd drop-box:

Same db "EXAM" but table is Enrl_CourseSession
this is where I want to populate in accordance with courseid from previous box.

So the where statement I gave was like below:
WHERE c_coursemasterid = Enrl_CourseMaster.courseid = ".mssql_real_escape_string($_GET['filter']);"

Am I doing it in right way, what is that I'm missing here.

#15

November 3rd, 2009, 03:33 PM

MatthewJ

Contributing User

Join Date: May 2007

Location: Davenport, Iowa

Posts: 564

Time spent in forums: 1 Week 21 h 23 m 45 sec
Reputation Power: 3

You should be able to test xml output being used to load the second dropdown by loading the xml_data_provider.php page with one of your course id's like:

http://www.yoursite.com/xml_data_provider.php?filter=yourid

as long as your xml populates the query should be okay.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: