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
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
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.
and your server side language is?
I think (from the tags and zones) that he wants this in javascript (AJAX or JQuery).
ASKER
Yes
Cluskitt is correct.
Cluskitt is correct.
sorry guys, Ajax and jQuery are not really what we call server side language
ASKER
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
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>
ASKER
leakim971: thats a great help, but how do I link the database fields into the code you provided?
Thanks
Thanks
check the second page on the link provided : How AJAX Works
ASKER
Will have a look shortly and post any questions, thanks :)
ASKER
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
Many Thanks
ID:36523311
ASKER
Ah yes appologies. Its PHP.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
here dropdown2.php (update the extension line 4 ID:36523818) :
You can open the page directly in your browser (ie: http://localhost/dropdown2.php?dropdown1_value=32) to see the JSON object
<?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);
?>
You can open the page directly in your browser (ie: http://localhost/dropdown2.php?dropdown1_value=32) to see the JSON object
ASKER
Ok excellent,
What did you mean by update the extension line 4 ID:XXX ?
What did you mean by update the extension line 4 ID:XXX ?
$.getJSON("dropdown1.php", function(data) {
$.getJSON("dropdown2.php", function(data) {
$.getJSON("dropdown2.php", function(data) {
ASKER
Oh im sorry, thanks for clearing that up.
Your help has been great so far, thanks.
Your help has been great so far, thanks.
ASKER
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
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?
ASKER
Sorry, its....
un:andy
pw:newborough147
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 :
could you add in both script :
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());
}
could you add in both script :
$n = mysql_num_rows($result);
if( $n == 0 ) die("no rows returned!");
ASKER
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!");
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!");
ASKER
Yep done that.
ok, there's a typo error, in both scripts replace :
$option[] = array("value"=>$row['week2 '],"text"= >$row['wee k2']);
by :
$options[] = array("value"=>$row['week2 '],"text"= >$row['wee k2']);
$option[] = array("value"=>$row['week2
by :
$options[] = array("value"=>$row['week2
and for the dropdown 1 :
$options[] = array("value"=>$row['ID'], "text"=>$r ow['traini ng_name']) ;
$options[] = array("value"=>$row['ID'],
ASKER
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.
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)"
}
]
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"}]
everything is alrightASKER
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
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
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
ASKER
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.
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'
ASKER
Ok so....
WHERE training_name='' . mysql_real_escape_string($ _REQUEST[" dropdown1_ value"])' );
WHERE training_name='' . mysql_real_escape_string($
ASKER
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 week1 FROM course_details WHERE training_name=' . mysql_real_escape_string($
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"]) ) . '\'';
ASKER
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"]) . '\'' );
$result = mysql_query('SELECT DISTINCT week2 FROM tablename WHERE training_name=\'' . mysql_real_escape_string($
ASKER
Same problem still :(
Invalid query: Table 'courses.tablename' doesn't exist
ASKER
Ooops. Corrected that error, still same problem though.
Thanks.
Thanks.
[{"value":null,"text":null },{"value" :null,"tex t":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...
<!--WHERE training_name='' . mysql_real_escape_string($
WHERE ID=' . mysql_real_escape_string($
not sure about what you modify...
ASKER
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....
$result = mysql_query('SELECT DISTINCT week2 FROM course_details WHERE training_name=\'' . mysql_real_escape_string($
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);
?>
$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']);
$options[] = array("value"=>$row['week1'],"text"=>$row['week1']);
ASKER
Needs to be week1 so I have changed the SELECT cause to be week1.
Same Problem
Same Problem
replace : $.getJSON("dropdown2.php?d ropdown1_v alue=" + $(this).val(), function(data) {
by : $.getJSON("dropdown2.php?d ropdown1_v alue=" + $(this).text(), function(data) {
by : $.getJSON("dropdown2.php?d
ASKER
Changed, still same problem.
replace : $.getJSON("dropdown2.php?d ropdown1_v alue=" + $(this).text(), function(data) {
by : $.getJSON("dropdown2.php?d ropdown1_v alue=" + $("option:selected",this). text() , function(data) {
by : $.getJSON("dropdown2.php?d
ASKER
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.
<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
ASKER
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
ASKER
Would I just have.....
<select id="dropdown1" name="CourseNames"></selec t>
<select id="dropdown1" name="CourseNames"></selec
of course
ASKER
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.
On my form I have the course name as:
<legend>Course Name<br /></legend>
<select id="dropdown1" type="text" name="CourseName"></select
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"=>$r ow['traini ng_name']) ;
by : $option[] = array("value"=>$row['train ing_name'] ,"text"=>$ row['train ing_name'] );
by : $option[] = array("value"=>$row['train
ASKER
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
http://www.deans-place.co.uk/activus/new/ddl.php
ASKER
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 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
not sure about the script for the first one
ASKER
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);
?>
$options[] = array("value"=>$row['train ing_name'] ,"text"=>$ row['train ing_name'] );
ASKER
Worked a treat.
Very last thing, our dropdown of course names has duplicated when it should obv be distinct?
Thanks.
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
by : SELECT DISTINCT training_name FROM course_details
ASKER
Lea i hope you do this for a living and get paid well to do it :)
ASKER
It works like a dream.
Thanks for all your help in this matter.
Thanks for all your help in this matter.