Solved

Dynamically updating dropdown boxes

Posted on 2011-09-12
64
213 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:deanlee17
  • 33
  • 29
  • 2
64 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 36522968
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
 
LVL 82

Expert Comment

by:leakim971
ID: 36523311
and your server side language is?
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 36523352
I think (from the tags and zones) that he wants this in javascript (AJAX or JQuery).
0
 

Author Comment

by:deanlee17
ID: 36523560
Yes


Cluskitt is correct.
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36523731
sorry guys, Ajax and jQuery are not really what we call server side language
0
 

Author Comment

by:deanlee17
ID: 36523743
leakim971: ? Does it matter then? This is in the ajax section
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36523818
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
 

Author Comment

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

Thanks
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36524807
check the second page on the link provided : How AJAX Works
0
 

Author Comment

by:deanlee17
ID: 36524869
Will have a look shortly and post any questions, thanks :)
0
 

Author Comment

by:deanlee17
ID: 36525404
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
 
LVL 82

Expert Comment

by:leakim971
ID: 36526505
ID:36523311
0
 

Author Comment

by:deanlee17
ID: 36527560
Ah yes appologies. Its PHP.
0
 
LVL 82

Accepted Solution

by:
leakim971 earned 500 total points
ID: 36528747
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
 
LVL 82

Expert Comment

by:leakim971
ID: 36528774
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
 

Author Comment

by:deanlee17
ID: 36528808
Ok excellent,

What did you mean by update the extension line 4 ID:XXX ?
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36528817
$.getJSON("dropdown1.php", function(data) {

$.getJSON("dropdown2.php", function(data) {
0
 

Author Comment

by:deanlee17
ID: 36528819
Oh im sorry, thanks for clearing that up.

Your help has been great so far, thanks.
0
 

Author Comment

by:deanlee17
ID: 36529478
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
 
LVL 82

Expert Comment

by:leakim971
ID: 36530588
login/password?
0
 

Author Comment

by:deanlee17
ID: 36532468
Sorry, its....

un:andy
pw:newborough147
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36532581
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
 

Author Comment

by:deanlee17
ID: 36532656
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
 
LVL 82

Expert Comment

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

Open in new window

0
 

Author Comment

by:deanlee17
ID: 36532987
Yep done that.
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36533337
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
 
LVL 82

Expert Comment

by:leakim971
ID: 36533339
and for the dropdown 1 :
    $options[] = array("value"=>$row['ID'],"text"=>$row['training_name']);
0
 

Author Comment

by:deanlee17
ID: 36534301
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
 
LVL 82

Expert Comment

by:leakim971
ID: 36535189
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
 

Author Comment

by:deanlee17
ID: 36535198
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
 
LVL 82

Expert Comment

by:leakim971
ID: 36535206
"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
 

Author Comment

by:deanlee17
ID: 36535212
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 82

Expert Comment

by:leakim971
ID: 36535233
don't forget to put quote WHERE Training Name='IOSH Directing Safely'
0
 

Author Comment

by:deanlee17
ID: 36535334
Ok so....

WHERE training_name='' . mysql_real_escape_string($_REQUEST["dropdown1_value"])' );
0
 

Author Comment

by:deanlee17
ID: 36535343
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
 
LVL 82

Expert Comment

by:leakim971
ID: 36535347
$result = mysql_query('SELECT DISTINCT week2 FROM tablename WHERE training_name=\'' . mysql_real_escape_string($_REQUEST["dropdown1_value"]) ) . '\'';
0
 

Author Comment

by:deanlee17
ID: 36535636
We have lost the second drop down now. No data in there.
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36535650
correction, use :
$result = mysql_query('SELECT DISTINCT week2 FROM tablename WHERE training_name=\'' . mysql_real_escape_string($_REQUEST["dropdown1_value"]) . '\'' );
0
 

Author Comment

by:deanlee17
ID: 36535662
Same problem still :(
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36535678
Invalid query: Table 'courses.tablename' doesn't exist
0
 

Author Comment

by:deanlee17
ID: 36535728
Ooops. Corrected that error, still same problem though.

Thanks.
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36535738
[{"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
 

Author Comment

by:deanlee17
ID: 36535749
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
 
LVL 82

Expert Comment

by:leakim971
ID: 36535762
$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
 

Author Comment

by:deanlee17
ID: 36535784
Needs to be week1 so I have changed the SELECT cause to be week1.

Same Problem
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36535792
replace : $.getJSON("dropdown2.php?dropdown1_value=" + $(this).val(), function(data) {
by : $.getJSON("dropdown2.php?dropdown1_value=" + $(this).text(), function(data) {
0
 

Author Comment

by:deanlee17
ID: 36535809
Changed, still same problem.
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36535882
replace : $.getJSON("dropdown2.php?dropdown1_value=" + $(this).text(), function(data) {
by : $.getJSON("dropdown2.php?dropdown1_value=" + $("option:selected",this).text() , function(data) {
0
 

Author Comment

by:deanlee17
ID: 36535923
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
 
LVL 82

Expert Comment

by:leakim971
ID: 36535934
I don't understand
0
 

Author Comment

by:deanlee17
ID: 36535969
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
 

Author Comment

by:deanlee17
ID: 36535974
Would I just have.....

<select id="dropdown1" name="CourseNames"></select>
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36535977
of course
0
 

Author Comment

by:deanlee17
ID: 36539349
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
 
LVL 82

Expert Comment

by:leakim971
ID: 36539359
replace : $option[] = array("value"=>$row['ID'],"text"=>$row['training_name']);
by : $option[] = array("value"=>$row['training_name'],"text"=>$row['training_name']);
0
 

Author Comment

by:deanlee17
ID: 36541142
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
 

Author Comment

by:deanlee17
ID: 36541222
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
 
LVL 82

Expert Comment

by:leakim971
ID: 36542496
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
 

Author Comment

by:deanlee17
ID: 36542778
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
 
LVL 82

Expert Comment

by:leakim971
ID: 36542797
$options[] = array("value"=>$row['training_name'],"text"=>$row['training_name']);
0
 

Author Comment

by:deanlee17
ID: 36542847
Worked a treat.

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

Thanks.
0
 
LVL 82

Expert Comment

by:leakim971
ID: 36542857
replace : SELECT DISTINCT ID, training_name FROM course_details
by : SELECT DISTINCT training_name FROM course_details
0
 

Author Comment

by:deanlee17
ID: 36542864
Lea i hope you do this for a living and get paid well to do it :)
0
 

Author Comment

by:deanlee17
ID: 36542898
It works like a dream.

Thanks for all your help in this matter.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

jQuery is a JavaScript library that greatly simplifies JavaScript programming. AJAX is an acronym formed from "Asynchronous JavaScript and XML."  AJAX refers to any communication between client and server, when the human client does not observe a…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now