Link to home
Start Free TrialLog in
Avatar of deanlee17
deanlee17

asked on

Dynamically updating dropdown boxes

Hi guys,

I have 2 dropdown boxes I need the second dropdown to be populated dynamically depending on the selection in the first combo box. Both combos need to be database driven. My database table looks like this...

I have attached an image of the database table. Combo one holds the training name, combo2 hold the week2 dates.

I'd like this to happen without having to refresh the page.

Many Thanks.
Dean     User generated image
Avatar of Cluskitt
Cluskitt
Flag of Portugal image

I have something similar to this in a web page. The way I handled it was, on the SelectedIndexChanged event of the first dropdownlist, I do the query (like "SELECT field2 FROM table WHERE field1=" & ddl1.SelectedValue), then re-assign DataSource for the ddl2, and DataBind it again.
Avatar of leakim971
and your server side language is?
I think (from the tags and zones) that he wants this in javascript (AJAX or JQuery).
Avatar of deanlee17
deanlee17

ASKER

Yes


Cluskitt is correct.
sorry guys, Ajax and jQuery are not really what we call server side language
leakim971: ? Does it matter then? This is in the ajax section
ok, here the code :

dropdown1 should return something like : [{ "value":1, "training_name":NEBOSH" }, { "value":32, "AAAA" }, and so on, all the distinct value]
dropdown2 should a similar content but for the value of dropdown1 (dropdown1_value parameter)

dropdown1 is your page code on the server returning the first content
dropdown2 is your page code on the server returning the second content

Want to learn Ajax?
A good link : http://www.w3schools.com/ajax/default.asp
<html><head><script src="http://code.jquery.com/jquery-1.6.2.js"></script><script>
	$(document).ready(function() {
		
		$.getJSON("dropdown1", function(data) {
			$.map(data, function(el,i) {
				$("#dropdown1").append("<option value='" + el.value + "'>" + el.text + "</option>")
			})
		})
		
		$("#dropdown1").change(function() {
			$.getJSON("dropdown2?dropdown1_value=" + $(this).val(), function(data) {
				$("#dropdown2").empty();
				$.map(data, function(el,i) {
					$("#dropdown2").append("<option value='" + el.value + "'>" + el.text + "</option>")
				})
			})
		})
	})
</script></head><body>
<select id="dropdown1"></select>
<select id="dropdown2"></select>
</body></html>

Open in new window

leakim971: thats a great help, but how do I link the database fields into the code you provided?

Thanks
check the second page on the link provided : How AJAX Works
Will have a look shortly and post any questions, thanks :)
Ok I understand that I now need to create a seperate file that makes the database connection and sends the values back. But im going to need some more help. Obv Combo1 needs all the available values in 'training name' and the second combo is the filtered one.

Many Thanks
ID:36523311
Ah yes appologies. Its PHP.
ASKER CERTIFIED SOLUTION
Avatar of leakim971
leakim971
Flag of Guadeloupe image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
here dropdown2.php (update the extension line 4 ID:36523818)  :

<?PHP

if( !isset($_REQUEST["dropdown1_value"]) ) die('Missing parameter');

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Not connected : ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db('foo', $link);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}

$result = mysql_query('SELECT DISTINCT week2 FROM tablename WHERE ID=' . mysql_real_escape_string($_REQUEST["dropdown1_value"]) );
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

$options = array(); 
while($row = mysql_fetch_assoc($result)){ 
    $option[] = array("value"=>$row['week2'],"text"=>$row['week2']); 
}

header('Content-type: application/json');
echo json_encode($options);

?>

Open in new window


You can open the page directly in your browser (ie: http://localhost/dropdown2.php?dropdown1_value=32) to see the JSON object
Ok excellent,

What did you mean by update the extension line 4 ID:XXX ?
$.getJSON("dropdown1.php", function(data) {

$.getJSON("dropdown2.php", function(data) {
Oh im sorry, thanks for clearing that up.

Your help has been great so far, thanks.
Ok we now have..

http://www.deans-place.co.uk/activus/new/ddl.php

Ive done everything uve asked so far. Database connection details are correct because this data comes live from the database....

http://www.deans-place.co.uk/activus/new/connection.php


login/password?
Sorry, its....

un:andy
pw:newborough147
http://www.deans-place.co.uk/activus/new/dropdown1.php
return an empty object, look like SELECT DISTINCT ID, training_name FROM tablename return nothing

http://www.deans-place.co.uk/activus/new/dropdown2.php?dropdown1_value=31
return nothing too.

strange...

after :
    die('Invalid query: ' . mysql_error());
}

Open in new window


could you add in both script :

$n = mysql_num_rows($result);
if( $n == 0 ) die("no rows returned!");

Open in new window

When you say after, do you mean....

if (!$result) {
    die('Invalid query: ' . mysql_error());
}
$n = mysql_num_rows($result);
if( $n == 0 ) die("no rows returned!");

OR...

if (!$result) {
die('Invalid query: ' . mysql_error());
$n = mysql_num_rows($result);
if( $n == 0 ) die("no rows returned!");
}


Thanks
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
$n = mysql_num_rows($result);
if( $n == 0 ) die("no rows returned!");

Open in new window

Yep done that.
ok, there's a typo error, in both scripts replace :
$option[] = array("value"=>$row['week2'],"text"=>$row['week2']);
by :
$options[] = array("value"=>$row['week2'],"text"=>$row['week2']);
and for the dropdown 1 :
    $options[] = array("value"=>$row['ID'],"text"=>$row['training_name']);
Hmmm strange,

It all initially seemed to be working, however when I added some new course names the changes were not reflected in the drop down lists, the table now looks like this....

http://www.deans-place.co.uk/activus/new/connection2.php?del=1&ID=32

Many Thanks.
I see all the values (6) in dropdown1 : http://www.deans-place.co.uk/activus/new/dropdown1.php 
[
    {
        "value": "1",
        "text": "NEBOSH National General Certificate in Occupational Health and Safety"
    },
    {
        "value": "34",
        "text": "Random Test"
    },
    {
        "value": "33",
        "text": "IOSH Directing Safely"
    },
    {
        "value": "31",
        "text": "New training name"
    },
    {
        "value": "23",
        "text": "IOSH Directing Safely"
    },
    {
        "value": "22",
        "text": "Site Manager Safety Training Scheme (SMSTS)"
    }
]

Open in new window

If I choose 22, dropdown2 is populated with :  http://www.deans-place.co.uk/activus/new/dropdown2.php?dropdown1_value=22
[{"value":"28 May - 01 Jun 2012","text":"28 May - 01 Jun 2012"}]

Open in new window

everything is alright

Try selecting IOSH Directing Safely

Does the second drop down give you 2 sets of dates? 05-09 Dec, the drop down should have 2 lots in the dropdown

Thanks
"IOSH Directing Safely" => 23

SELECT DISTINCT week2 FROM tablename WHERE ID=23

23      IOSH Directing Safely      Harlow      05 - 09 Dec 2011      10 - 19 Dec 2011      19 Dec 2011      £400000000
Ah I see a problem.

IOSH Directing Safely has 2 entries, so shall I change WHERE ID=
 to WHERE Training Name=

Each course name may have many sets of dates.

don't forget to put quote WHERE Training Name='IOSH Directing Safely'
Ok so....

WHERE training_name='' . mysql_real_escape_string($_REQUEST["dropdown1_value"])' );
I currently have....

$result = mysql_query('SELECT DISTINCT week1 FROM course_details WHERE training_name=' . mysql_real_escape_string($_REQUEST["dropdown1_value"]') );

But the second drop down is empty
$result = mysql_query('SELECT DISTINCT week2 FROM tablename WHERE training_name=\'' . mysql_real_escape_string($_REQUEST["dropdown1_value"]) ) . '\'';
We have lost the second drop down now. No data in there.
correction, use :
$result = mysql_query('SELECT DISTINCT week2 FROM tablename WHERE training_name=\'' . mysql_real_escape_string($_REQUEST["dropdown1_value"]) . '\'' );
Same problem still :(
Invalid query: Table 'courses.tablename' doesn't exist
Ooops. Corrected that error, still same problem though.

Thanks.
[{"value":null,"text":null},{"value":null,"text":null}]
<!--WHERE training_name='' . mysql_real_escape_string($_REQUEST["dropdown1_value"])' );
WHERE ID=' . mysql_real_escape_string($_REQUEST["dropdown1_value"]) );-->

not sure about what you modify...
That just a little copied out bit of code, it now reads....

$result = mysql_query('SELECT DISTINCT week2 FROM course_details WHERE training_name=\'' . mysql_real_escape_string($_REQUEST["dropdown1_value"]) . '\'' );

That whole files is....
<?PHP

if( !isset($_REQUEST["dropdown1_value"]) ) die('Missing parameter');

$link = mysql_connect('X', 'X', 'X');
if (!$link) {
    die('Not connected : ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db('courses', $link);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}

$result = mysql_query('SELECT DISTINCT week2 FROM course_details WHERE training_name=\'' . mysql_real_escape_string($_REQUEST["dropdown1_value"]) . '\'' );
 
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
$n = mysql_num_rows($result);
if( $n == 0 ) die("no rows returned!");


$options = array(); 
while($row = mysql_fetch_assoc($result)){ 
   $options[] = array("value"=>$row['week1'],"text"=>$row['week1']);  
}

header('Content-type: application/json');
echo json_encode($options);

?>

Open in new window

$result = mysql_query('SELECT DISTINCT week2 FROM course_details WHERE training_name=\'' . mysql_real_escape_string($_REQUEST["dropdown1_value"]) . '\'' );

$options[] = array("value"=>$row['week1'],"text"=>$row['week1']);
Needs to be week1 so I have changed the SELECT cause to be week1.

Same Problem
replace : $.getJSON("dropdown2.php?dropdown1_value=" + $(this).val(), function(data) {
by : $.getJSON("dropdown2.php?dropdown1_value=" + $(this).text(), function(data) {
Changed, still same problem.
replace : $.getJSON("dropdown2.php?dropdown1_value=" + $(this).text(), function(data) {
by : $.getJSON("dropdown2.php?dropdown1_value=" + $("option:selected",this).text() , function(data) {
Excellent. This now works perfectly. I now just need to add Names to the drop downs as they get posted to another file then emailed. For example another file in the form has...

<input type="text" name="org_email"/><br /><br />

So its picking up the posted name 'org_email.

Many Thanks, you have been an huge help.
I don't understand
The combos that I have created are part of a form that gets posted. So I need to name the combo boxes for posting reasons
Would I just have.....

<select id="dropdown1" name="CourseNames"></select>
of course
Ok my very last problem on this matter :)

On my form I have the course name as:
<legend>Course Name<br /></legend>
<select id="dropdown1" type="text" name="CourseName"></select><br /><br />

Which is then posted to another file, which is emailed to he site owner. However the Course ID is being taken across and not the course name. Any ideas?

In his email the course name comes across as:

CourseName: 34

Many Thanks.
replace : $option[] = array("value"=>$row['ID'],"text"=>$row['training_name']);
by : $option[] = array("value"=>$row['training_name'],"text"=>$row['training_name']);
Really sorry, but I seem to have lost the dropdowns now? Not sure what I have changed to do this.

http://www.deans-place.co.uk/activus/new/ddl.php
The database is not offline, realy cant figure out what ive changed other than 'ID' to 'training_name', but even when i switch them back I dont get any joy. Sorry to be a nuisance, but we are very close.
The script for dropdown 1 is...
<?PHP
$link = mysql_connect('mysql01.myhostcp.com', 'Andy', 'Newborough147');
if (!$link) {
    die('Not connected : ' . mysql_error());
}

// make foo the current db
$db_selected = mysql_select_db('courses', $link);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}

$result = mysql_query('SELECT DISTINCT ID, training_name FROM course_details');
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
$n = mysql_num_rows($result);
if( $n == 0 ) die("no rows returned!");


$options = array(); 
while($row = mysql_fetch_assoc($result)){ 
    $option[] = array("value"=>$row['training_name'],"text"=>$row['training_name']); 
}

header('Content-type: application/json');
echo json_encode($options);

?>

Open in new window

$options[] = array("value"=>$row['training_name'],"text"=>$row['training_name']);
Worked a treat.

Very last thing, our dropdown of course names has duplicated when it should obv be distinct?

Thanks.
replace : SELECT DISTINCT ID, training_name FROM course_details
by : SELECT DISTINCT training_name FROM course_details
Lea i hope you do this for a living and get paid well to do it :)
It works like a dream.

Thanks for all your help in this matter.