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     Table
deanlee17Asked:
Who is Participating?
 
leakim971PluritechnicianCommented:
no worry, here a basic code, the mysql part is a copy/paste from php.net, the "important" part is the last line of code and of course the mysql query itself (update the tablename)

here dropdown1.php (update the extension line 4 ID:36523818)  :

<?PHP
$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 ID, training_name FROM tablename');
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

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

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/dropdown1.php) to see the JSON object
0
 
CluskittCommented:
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.
0
 
leakim971PluritechnicianCommented:
and your server side language is?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
CluskittCommented:
I think (from the tags and zones) that he wants this in javascript (AJAX or JQuery).
0
 
deanlee17Author Commented:
Yes


Cluskitt is correct.
0
 
leakim971PluritechnicianCommented:
sorry guys, Ajax and jQuery are not really what we call server side language
0
 
deanlee17Author Commented:
leakim971: ? Does it matter then? This is in the ajax section
0
 
leakim971PluritechnicianCommented:
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

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

Thanks
0
 
leakim971PluritechnicianCommented:
check the second page on the link provided : How AJAX Works
0
 
deanlee17Author Commented:
Will have a look shortly and post any questions, thanks :)
0
 
deanlee17Author Commented:
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
0
 
leakim971PluritechnicianCommented:
ID:36523311
0
 
deanlee17Author Commented:
Ah yes appologies. Its PHP.
0
 
leakim971PluritechnicianCommented:
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
0
 
deanlee17Author Commented:
Ok excellent,

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

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

Your help has been great so far, thanks.
0
 
deanlee17Author Commented:
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


0
 
leakim971PluritechnicianCommented:
login/password?
0
 
deanlee17Author Commented:
Sorry, its....

un:andy
pw:newborough147
0
 
leakim971PluritechnicianCommented:
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

0
 
deanlee17Author Commented:
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
0
 
leakim971PluritechnicianCommented:
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
$n = mysql_num_rows($result);
if( $n == 0 ) die("no rows returned!");

Open in new window

0
 
deanlee17Author Commented:
Yep done that.
0
 
leakim971PluritechnicianCommented:
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']);
0
 
leakim971PluritechnicianCommented:
and for the dropdown 1 :
    $options[] = array("value"=>$row['ID'],"text"=>$row['training_name']);
0
 
deanlee17Author Commented:
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.
0
 
leakim971PluritechnicianCommented:
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

0
 
deanlee17Author Commented:
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
0
 
leakim971PluritechnicianCommented:
"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
0
 
deanlee17Author Commented:
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.

0
 
leakim971PluritechnicianCommented:
don't forget to put quote WHERE Training Name='IOSH Directing Safely'
0
 
deanlee17Author Commented:
Ok so....

WHERE training_name='' . mysql_real_escape_string($_REQUEST["dropdown1_value"])' );
0
 
deanlee17Author Commented:
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
0
 
leakim971PluritechnicianCommented:
$result = mysql_query('SELECT DISTINCT week2 FROM tablename WHERE training_name=\'' . mysql_real_escape_string($_REQUEST["dropdown1_value"]) ) . '\'';
0
 
deanlee17Author Commented:
We have lost the second drop down now. No data in there.
0
 
leakim971PluritechnicianCommented:
correction, use :
$result = mysql_query('SELECT DISTINCT week2 FROM tablename WHERE training_name=\'' . mysql_real_escape_string($_REQUEST["dropdown1_value"]) . '\'' );
0
 
deanlee17Author Commented:
Same problem still :(
0
 
leakim971PluritechnicianCommented:
Invalid query: Table 'courses.tablename' doesn't exist
0
 
deanlee17Author Commented:
Ooops. Corrected that error, still same problem though.

Thanks.
0
 
leakim971PluritechnicianCommented:
[{"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...
0
 
deanlee17Author Commented:
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

0
 
leakim971PluritechnicianCommented:
$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']);
0
 
deanlee17Author Commented:
Needs to be week1 so I have changed the SELECT cause to be week1.

Same Problem
0
 
leakim971PluritechnicianCommented:
replace : $.getJSON("dropdown2.php?dropdown1_value=" + $(this).val(), function(data) {
by : $.getJSON("dropdown2.php?dropdown1_value=" + $(this).text(), function(data) {
0
 
deanlee17Author Commented:
Changed, still same problem.
0
 
leakim971PluritechnicianCommented:
replace : $.getJSON("dropdown2.php?dropdown1_value=" + $(this).text(), function(data) {
by : $.getJSON("dropdown2.php?dropdown1_value=" + $("option:selected",this).text() , function(data) {
0
 
deanlee17Author Commented:
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.
0
 
leakim971PluritechnicianCommented:
I don't understand
0
 
deanlee17Author Commented:
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
0
 
deanlee17Author Commented:
Would I just have.....

<select id="dropdown1" name="CourseNames"></select>
0
 
leakim971PluritechnicianCommented:
of course
0
 
deanlee17Author Commented:
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.
0
 
leakim971PluritechnicianCommented:
replace : $option[] = array("value"=>$row['ID'],"text"=>$row['training_name']);
by : $option[] = array("value"=>$row['training_name'],"text"=>$row['training_name']);
0
 
deanlee17Author Commented:
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
0
 
deanlee17Author Commented:
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.
0
 
leakim971PluritechnicianCommented:
the second work fine : http://www.deans-place.co.uk/activus/new/dropdown2.php?dropdown1_value=IOSH%20Directing%20Safely

not sure about the script for the first one
0
 
deanlee17Author Commented:
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

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

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

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

Thanks for all your help in this matter.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.