Solved

I need to populate two drop down lists from mysql

Posted on 2009-03-30
13
962 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article discusses four methods for overlaying images in a container on a web page
This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
In this tutorial viewers will learn how to style a corner ribbon overlay for an image using CSS Create a new class by typing ".Ribbon":  Define the class' "display:" as "inline-block": Define its "position:" as "relative": Define its "overflow:" as …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

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

20 Experts available now in Live!

Get 1:1 Help Now