Solved

I need to populate two drop down lists from mysql

Posted on 2009-03-30
13
968 Views
Last Modified: 2013-12-12
I wish to make a form with  two drop down lists ' Country' and 'City'.  I need Country to be populated by all countries in the 'Country' column of my table.

And then the second drop down list 'City' will be populated according to the selection of the country field. I am sure this is quite simple but I am still farely new with php.

The code below works fine for the first drop down but i dont know how to get the selected country to use in the sql query for the second drop down.

The Database is called 'Resale'

Please help,

Oh and how do I make the form auto refresh once the country option is choosen


<?
 
 
-- bd connection stuff goes here --
 
$query = "SELECT DISTINCT Country FROM Resale ";
$result = mysql_query($query)
or die ( ' error in query ' );
 
 
while ($row=mysql_fetch_array($result)) {
 
    $id=$row["_ID"];
    $Country=$row["Country"];
    $options.="<OPTION VALUE=\"$id\">".$Country;
}
?>
 
<form method="get">
<SELECT NAME=selectcountry>
<OPTION VALUE=0>Choose
<?=$options?>
</SELECT>
</OPTION>
 
<?php echo $options ?>

Open in new window

0
Comment
Question by:bazconnolly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 12

Accepted Solution

by:
adrian_brooks earned 500 total points
ID: 24023122
I guess the first thing to assess is whether each city record has a field that will hold a foreign key integer value that corresponds to the primary key field of the countries table.

So basically, In your countries table, you would have;

_____________________________
|  ID  |           country_name               |
-------------------------------------------
|  1   |   Afghanistan                          |
|  2   |   Belgium                                 |
|  3   |   Canada                                 |
|  4   |   etc...etc                                |
-------------------------------------------

In your Cities table, you would have something similar to this;

_____________________________
|  ID  |           city_name                      |
-------------------------------------------
|  3   |   Montreal                                |
|  3   |   Toronto                                 |
|  1   |   Kabul                                    |
|  1   |   Ghazni                                  |
|  2   |   Durbuy                                  |
|  2   |   Geraardsbergen                   |
-------------------------------------------

So, let's suppose that you populated the first dropdown box with all the countries so that the value in the options is the country_id and the text for that option is the country name. Now, a user selects Afghanistan in the first dropdown selector.

The resulting query would be;

$sql = "SELECT * FROM cities WHERE ID = {$_GET['cid']};";

Now, when that query is ran, every city that has the value of Afghanistan's primary key or ID value will be returned, thus giving you all the cities just for Afghanistan.

Hope that helped. :)

~A~
0
 
LVL 12

Expert Comment

by:adrian_brooks
ID: 24023149
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~
0
 

Expert Comment

by:sdaugherty
ID: 24024056
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>

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Closing Comment

by:bazconnolly
ID: 31564514
perfecto
0
 
LVL 1

Expert Comment

by:delphibeginner
ID: 24136674
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>

Open in new window

0
 

Expert Comment

by:sdaugherty
ID: 24142447
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...
0
 
LVL 1

Expert Comment

by:delphibeginner
ID: 24145260
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>

Open in new window

0
 
LVL 1

Expert Comment

by:delphibeginner
ID: 24156088
@ 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>

Open in new window

0
 

Expert Comment

by:sdaugherty
ID: 24181985
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>

Open in new window

color-coded-example.PNG
0
 

Expert Comment

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

Expert Comment

by:sdaugherty
ID: 24182065
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>

Open in new window

0
 

Expert Comment

by:delphiuser2009
ID: 24182751
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.
0
 

Expert Comment

by:sdaugherty
ID: 24186614
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.
0

Featured Post

WordPress Tutorial 2: Terminology

An important part of learning any new piece of software is understanding the terminology it uses. Thankfully WordPress uses fairly simple names for everything that make it easy to start using the software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

635 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