I need to populate two drop down lists from mysql

As for making the form auto-refresh. You have two choices...the less complex method of just performing an onSelect="top.location='thispage.php?cid='+this.value;"

or the more complicated, but slick looking method of using AJAX.

I'll spare any examples of AjAX as that would totally go beyond the scope of this issue.

~A~

I agree with adrian_brooks. It wouldn't hurt for your city table columns to consist of:

______________________________________
|  city_id   |  country_id   |           city_name         |
----------------------------------------------------------------

Every city is assigned to a specific country, but also has its own city_id. Yes, technically the city is identified by its country but if you are somehow saving this information for users or doing searches, the city has to be uniquely identified somehow...so best to give it its own city_id.

So when you first hit the page:

1. Select your country (which has _ID as its value and Country as its label / text)

2. Page auto-refreshes with selected _ID (country id) and populates the city drop down.

3. Select your city.

4. Do whatever else you need to do....

<?php
 
 
-- db connection stuff goes here --
 
$query = "SELECT DISTINCT Country FROM Resale ";
$result = mysql_query($query) or die ( ' error in query ' );
 
$country_options = "";
$city_options = "";
 
while ($row = mysql_fetch_array($result)) {
 
    $id = $row["_ID"];
    $Country = $row["Country"];
    $country_options .= "<option value=\"$id\" onSelect=\"top.location='".$_SERVER['PHP_SELF']."?country_id=".$id."';\">".$Country."</option>";
}
 
if ( isset($_GET['country_id']) ) {
    
    $query = "SELECT city_id, city_name FROM cities WHERE country_id={$_GET['country_id']}";
    $result = mysql_query($query) or die ( ' error in query ' );
 
    while ($row=mysql_fetch_array($result) {
        $city_options .= "<option value=\"{$row['city_id']}\">{$row['city_name']}</option>";
    }
 
}
 
?>
 
<form method="GET">
 
<select name="selectcountry">
<option value="0">Choose Country</option>
<?php echo $country_options; ?>
</select>
 
<select name="selectcity">
<option value="0">Choose City</option>
<?php echo $city_options; ?>
</select>
 
</form>

Select allOpen in new window

perfecto

sdaugherty:

I tried the code you posted, but I think there are some errors in it.

I can't get it to work, the second dropdown doesn't get the data from the database.
I have 2 tables: hesk_programma and hesk_project. Inside the hesk_project table there is also a ProgrammaID (from the table1 hesk_programma, it also contains a ProjectID but this field has a different ID).

Here is the code I use (changed it a bit):

<?php
 
 
mysql_connect("localhost", "root", "");
mysql_select_db("bestellingen");
 
$query = "SELECT DISTINCT Programma FROM hesk_programma ";
$result = mysql_query($query) or die ( ' error in query ' );
 
$Programma_options = "";
$Project_options = "";
 
while ($row = mysql_fetch_array($result)) {
 
    $id = $row["ProgrammaID"];
    $Programma = $row["Programma"];
    $Programma_options .= "<option value=\"$id\" onSelect=\"top.location='".$_SERVER['PHP_SELF']."?ProgrammaID=".$id."';\">".$Programma."</option>";
}
 
if ( isset($_GET['ProgrammaID']) ) {
    
    $query = "SELECT * FROM hesk_projecten WHERE ProgrammaID={$_GET['ProgrammaID']}";
    $result = mysql_query($query) or die ( ' error in query ' );
 
    while ($row=mysql_fetch_array($result)) {
        $Project_options .= "<option value=".$row['ProjectID'].">".$row['Project']."</option>";
    }
 
}
 
?>
 
<form method="GET">
 
<select name="selectcountry">
<option value="0">Kies Programma</option>
<?php echo $Programma_options; ?>
</select>
 
<select name="selectcity">
<option value="0">Kies Project</option>
<?php echo $Project_options; ?>
</select>
 
</form>

Select allOpen in new window

delphibeginner:

Are you saying the page loads fine but the second drop down doesn't populate with Projects?

Also, is the code snippet you attached the actual code your using? I noticed that in your first SQL query you are not grabbing the "ProgrammaID" column. Could just be a typo on your part but if thats the actual code then your HTML option fields well never have their value attributes populated with values, in turn making keeping the second option field from working.

It should look like this if it doesn't already:

SELECT DISTINCT Programma, ProgrammaID FROM hesk_programma

Lemme know if that works...

Thanks for your reply.

But still the second dropdown remains at its default setting (kies project).

It looks like the page isn't refreshing at all.

Here the code I use now.

<?php
 
 
mysql_connect("localhost", "root", "");
mysql_select_db("bestellingen");
 
$query = "SELECT Programma, ProgrammaID FROM hesk_programma ";
$result = mysql_query($query) or die ( ' error in query ' );
 
$Programma_options = "";
$Project_options = "";
 
while ($row = mysql_fetch_array($result)) {
 
    $id = $row["ProgrammaID"];
    $Programma = $row["Programma"];
    $Programma_options .= "<option value=\"$id\" onSelect=\"top.location='".$_SERVER['PHP_SELF']."?ProgrammaID=".$id."';\">".$Programma."</option>";
}
 
if ( isset($_GET['ProgrammaID']) ) {
    
    $query = "SELECT * FROM hesk_projecten WHERE ProgrammaID={$_GET['ProgrammaID']}";
    $result = mysql_query($query) or die ( ' error in query ' );
 
    while ($row=mysql_fetch_array($result)) {
        $Project_options .= "<option value=".$row['ProjectID'].">".$row['Project']."</option>";
    }
 
}
 
?>
 
<form method="GET">
 
<select name="selectcountry">
<option value="0">Kies Programma</option>
<?php echo $Programma_options; ?>
</select>
 
<select name="selectcity">
<option value="0">Kies Project</option>
<?php echo $Project_options; ?>
</select>
 
</form>

Select allOpen in new window

@ sdaugherty:

I've changed the code and the database a little like you had posted.

It's now like this and with the outcome in html below it, looks like with onSelect part I'm missing something.

<?php
 
 
mysql_connect("localhost", "root", "");
mysql_select_db("bestellingen");
 
$query = "SELECT * FROM hesk_programma ";
$result = mysql_query($query) or die ( ' error in query ' );
 
$Programma_options = "";
$Project_options = "";
 
while ($row = mysql_fetch_array($result)) {
 
    $id = $row["_id"];
    $Programma = $row["Programma"];
    $Programma_options .= "<option value=\"$id\" onSelect=\"top.location='".$_SERVER['PHP_SELF']."?Programma_id=".$id."'\">".$Programma."</option>";
}
 
if ( isset($_GET['Programma_id']) ) {
    
    $query = "SELECT Project_id, Project FROM hesk_projecten WHERE Project_id={$_GET['Programma_id']}";
    $result = mysql_query($query) or die ( ' error in query ' );
 
    while ($row=mysql_fetch_array($result)) {
        $Project_options .= "<option value=\"{$row['Project_id']}\">{$row['Project']}</option>";
    }
 
}
 
?>
 
<form method="GET">
 
<select name="selectprogramma">
<option value="0">Kies Programma</option>
<?php echo $Programma_options; ?>
</select>
 
<select name="selectproject">
<option value="0">Kies Project</option>
<?php echo $Project_options; ?>
</select>
 
</form>
 
 
 
 
<form method="GET">
 
<select name="selectprogramma">
<option value="0">Kies Programma</option>
<option value="" onSelect="top.location='/test.php?Programma_id='">Algemene kosten</option><option value="" onSelect="top.location='/test.php?Programma_id='">Autokosten</option><option value="" onSelect="top.location='/test.php?Programma_id='">Beheer & administratie</option><option value="" onSelect="top.location='/test.php?Programma_id='">Brandpreventieweek</option><option value="" onSelect="top.location='/test.php?Programma_id='">Euro Skin Bank</option><option value="" onSelect="top.location='/test.php?Programma_id='">Facilitaire dienst</option><option value="" onSelect="top.location='/test.php?Programma_id='">Fondsenwerving</option><option value="" onSelect="top.location='/test.php?Programma_id='">Informatie Centrum</option><option value="" onSelect="top.location='/test.php?Programma_id='">Investering EBS</option><option value="" onSelect="top.location='/test.php?Programma_id='">Investering NBS</option><option value="" onSelect="top.location='/test.php?Programma_id='">Kwaliteit van Leven</option><option value="" onSelect="top.location='/test.php?Programma_id='">Marketing & Communicatie</option><option value="" onSelect="top.location='/test.php?Programma_id='">Preventie</option><option value="" onSelect="top.location='/test.php?Programma_id='">VSBN</option><option value="" onSelect="top.location='/test.php?Programma_id='">Onderzoek</option><option value="" onSelect="top.location='/test.php?Programma_id='">Zorg</option></select>
 
<select name="selectproject">
<option value="0">Kies Project</option>
</select>
 
</form>

Select allOpen in new window

okay I am stupid. I figured it out. We want to change that onSelect event to onChange and attach it to the Select box itself, not each individual option inside of it. I duplicated your table environment and got it working, so if you have any other problems...I can walk you through getting it working.

here is a url to the working example:
http://dev.circlenerdz.com/selct-box-populate/

Lemme know if this solves the problem? It should....haha

<?php
// Database Connection
mysql_connect("localhost", "root", "") or die('could not connect to mysql');
mysql_select_db("bestellingen") or die('could not connect to database');
 
// Get Programmers from database
$query = "SELECT * FROM hesk_programma";
$result = mysql_query($query) or die ( ' error in query ' );
 
// Initialize arrays to hold selct box options
$Programma_options = array();
$Project_options = array();
 
// Build programmer options
while ($row = mysql_fetch_array($result)) {
    $Programma_options[] = "<option value=\"".$row["Programma_id"]."\">".$row["Programma"]."</option>";
}
 
// Execute code inside here if a programmer has been selected
if ( isset($_GET['Programma_id']) ) {
   
	// Get Projects based on Programmer selected
    $query = "SELECT Project_id, Project FROM hesk_projecten WHERE Project_id='".$_GET['Programma_id']."'";
    $result = mysql_query($query) or die ( ' error in query ' );
	
	// Build Project options
    while ($row=mysql_fetch_array($result)) {
        $Project_options[] = "<option value=\"".$row['Project_id']."\">".$row['Project']."</option>";
    }
 
}
?>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
 
<body>
 
<select name="selectprogramma" onchange="window.location='<?=$_SERVER['PHP_SELF']?>?Programma_id='+this[this.selectedIndex].value;">
	<option value="0">Kies Programma</option>
	<?php echo implode("\n\t", $Programma_options); ?>
</select>
 
<select name="selectproject">
	<option value="0">Kies Project</option>
	<?php echo implode("\n\t", $Project_options); ?>
</select>
 
</body>
</html>

Select allOpen in new window

color-coded-example.PNG

oops sorry. Typo for the working example, it is: http://dev.circlenerdz.com/select-box-populate/

Think I saw your post on another forum. Here is updated code to keep the first select box's selected value after the page refresh with minimul code added and NOT having to use ajax (although ajax would be more elegant down the road):

<?php
// Database Connection
mysql_connect("localhost", "circlene_shloss", "php123abc") or die('could not connect to mysql');
mysql_select_db("circlene_bestellingen") or die('could not connect to database');
 
// Get Programmers from database
$query = "SELECT * FROM hesk_programma";
$result = mysql_query($query) or die ( ' error in query ' );
 
// Initialize arrays to hold selct box options
$Programma_options = array();
$Project_options = array();
 
// Build programmer options
while ($row = mysql_fetch_array($result)) {
	$id = $row["Programma_id"];
	$selected = (isset($_GET["Programma_id"]) && ($id == $_GET["Programma_id"]) ) ? "selected=\"selected\"":"";
	$Programma_options[] = "<option value=\"".$row["Programma_id"]."\" ".$selected.">".$row["Programma"]."</option>";
}
 
// Execute code inside here if a programmer has been selected
if ( isset($_GET['Programma_id']) ) {
   
    // Get Projects based on Programmer selected
    $query = "SELECT Project_id, Project FROM hesk_projecten WHERE Project_id='".$_GET['Programma_id']."'";
    $result = mysql_query($query) or die ( ' error in query ' );
	
    // Build Project options
    while ($row=mysql_fetch_array($result)) {
        $Project_options[] = "<option value=\"".$row['Project_id']."\">".$row['Project']."</option>";
    }
 
}
?>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
 
<body>
 
<select name="selectprogramma" onchange="window.location='<?=$_SERVER['PHP_SELF']?>?Programma_id='+this[this.selectedIndex].value;">
	<option value="0">Kies Programma</option>
	<?php echo implode("\n\t", $Programma_options); ?>
</select>
 
<select name="selectproject">
	<option value="0">Kies Project</option>
	<?php echo implode("\n\t", $Project_options); ?>
</select>
 
</body>
</html>

Select allOpen in new window

Hi there,

Thanks for your solution, it works great, only two questions remaining.

first: In the programma_id and project_id I have 2 id's with & between 2 characters, for example: B&A or M&C, when selecting those in the dropdown the dropdown resets itself and I now its because of the & character. Any solution for that?

Second: In the database I have an other column with the name "projectnummer", each of the items from project (above), have a identical number, how can I put this number into a input field when selecting an item from the second dropdown list?

Thanks again for you assistence.

first: I would create another column soley for an ID (integer, auto_increment) so you don't have to worry about weird characters like the & sign. I've never liked using strings as ID's anyways unless you know for sure they would NEVER contain weird characters.

second: You will need to add an onChange event to the second drop down that does the same thing as the first drop down, but passes the Programma_id as well as the Project_id to the page. Then up in the php you'll want to do the same kind of check for the $_GET['Programma_id'], but for the Project_id.

Once you have that stuff handled, do a query for the projectnummer column  based on the project_id selected, and build an input box with that projectnummer value in it.

This is where AJAX starts to become a bit easier to implement, cause you don't have to refresh the page over and over. You can make separate php files that do these checks and queries and call them in the background.