?
Solved

Dynamic dependent list boxes with database inputs

Posted on 2006-05-10
4
Medium Priority
?
316 Views
Last Modified: 2013-12-13
Hi all the experts,

I have to build a dynamic drop-down dependent input form with data coming from a MySQL database. There is a database table named zipcodes with the following fields State, County, City. I have to build the dependent list boxes menu, when the list of Counties is defined by the selected State and the list of Cities is defined by the Selected County. I have found a similar solution from the expert forum as follows:

<?php

        #Fetch database connection
        require_once('Connections/myconn.php');
        mysql_select_db($database_myconn,$myconn);
        require_once('myfunctions.php');


$whatselect = "";      // always resetting all vaues is a good way to rebuff hackers
$sel_1 = "";
$sel_2 = "";
$sel_3 = "";
if ( isset( $_POST['whatselect'] )) $whatselect = $_POST['whatselect'];     // what does the form say
if ( isset( $_POST['sel_1'] )) $sel_1 = $_POST['sel_1'];
if ( isset( $_POST['sel_2'] )) $sel_2 = $_POST['sel_2'];
if ( isset( $_POST['sel_3'] )) $sel_3 = $_POST['sel_3'];

switch ( $whatselect  ) {
   case "firsttime":
   case "":
       $sel_1 = "not valid";
   case "sel_1":
       $sel_2 = "not valid";
   case "sel_2":
       $sel_3 = "not valid";
   case "sel_3":
       // just generate proper tabledata
}

// here we need to open the mysql connection and select a database. you should know how.

// and finally we start writing the page. remember this is a html file for the browser. make it proper html.
?>
<html>
<body>
<form action='' method='post'>
<input type='hidden' name='whatselect' value="firsttime">
<select name='sel_1' onchange="this.form.whatselect.value=this.name; this.form.submit();">
<?php
$sql_command1 = "
    SELECT sel1_choice, sel1_text
   FROM tableName
   GROUP BY sel1_choice
";
$sel1_res = mysql_query( $sql_command1 ) or die( mysql_error() );
while ( $row=mysql_fetch_array( $sel1_res ) ) {
    ?>
     <option value='<?php echo $row['sel1_choice'] ?>'<?php  if ( $sel_1==$row['sel1_choice'] ) echo " selected"; ?> >$row['sel1_text']</option>
     <?php
}
?>
</select>

<select name='sel_2' onchange="this.form.whatselect.value=this.name; this.form.submit();">
<?php
$sql_command2 = "
    SELECT sel2_choice, sel2_text
   FROM tableName
   WHERE sel1_choice='$sel_1'
   GROUP BY sel2_choice
";
$sel2_res = mysql_query( $sql_command2 ) or die( mysql_error() );
while ( $row=mysql_fetch_array( $sel2_res ) ) {
    ?>
     <option value='<?php echo $row['sel2_choice'] ?>'<?php  if ( $sel_2==$row['sel2_choice'] ) echo " selected"; ?> >$row['sel2_text']</option>
     <?php
}
?>
</select>

I still cannot figure out where I should put the fields' names into the code and would appreciate your help. Probably someone can give me a better solution.

Thanks

Andy
0
Comment
Question by:andy7789
  • 3
4 Comments
 
LVL 6

Accepted Solution

by:
Kosta earned 1000 total points
ID: 16655172
You can put your field names replacing sel1_choice, sel1_text, sel2_choice, sel2_text...

But I would suggest another approach. In your code, after making each choice (when user select state or country or city) the entire page will be reloaded. I whould place 2 IFRAME tags, one for each select, and load only the needed part. Something like this:

main.php:
<html>
<body>
<form action='country.php' method='post' target="country">
<select name="state">
<?php
// here we need to open the mysql connection and select a database. you should know how.
$q=mysql_query("SELECT State FROM zipcodes GROUP BY State");
while($r=mysql_fetch_array($q){
echo "<option>$r[State]</option>";
}
?>
</select>
<input type="submit" value="Get countryes">
</form>
<iframe name="country" width="200" height="300" frameborder="NO">
<br>
<iframe name="city" width="200" height="300" frameborder="NO">
</body>
</html>


country.php:
<html>
<body>
<form action='city.php' method='post' target="city">
<select name="country">
<?php
// here we need to open the mysql connection and select a database. you should know how.
$q=mysql_query("SELECT Country FROM zipcodes WHERE Country=$_POST[state] GROUP BY Country");
while($r=mysql_fetch_array($q){
echo "<option>$r[Country]</option>";
}
?>
</select>
<input type="submit" value="Get cityes">
<input type="hidden" value="<?=$_POST['state'] ?>" name="state">
</form>
</body>
</html>


city.php:
<html>
<body>
<form action='result.php' method='post' target="_top">
<select name="city">
<?php
// here we need to open the mysql connection and select a database. you should know how.
$q=mysql_query("SELECT City FROM zipcodes WHERE Country=$_POST[state] AND Country=$_post[country]  GROUP BY City");
while($r=mysql_fetch_array($q){
echo "<option>$r[City]</option>";
}
?>
</select>
<input type="submit" value="Get result">
<input type="hidden" value="<?=$_POST['state'] ?>" name="state">
<input type="hidden" value="<?=$_POST['city'] ?>" name="city">
</form>
</body>
</html>
0
 

Author Comment

by:andy7789
ID: 16655987
Thank you Kosta,

I like the idea, because it is not very good to load the whole database and overload the page. I have slightly changed the code and it poasses the 1st argument (state) to the 2nd ifame, but does not build the menu options, i.e., echo "<option>$r[County]</option>"; is not working properly. Here is my code:

main.php
<html>
<body>
<form action='county.php' method='post' target="county">
<select name="state">
<?php

      #Fetch database connection
      require_once('Connections/myconn.php');
      mysql_select_db($database_myconn,$myconn);
      require_once('myfunctions.php');

$q=mysql_query("SELECT Distinct State FROM zipcodes GROUP BY State");
while($r=mysql_fetch_array($q)){
echo "<option>$r[State]</option>";
}
?>
</select>
<input type="submit" value="Get State">
</form>
<iframe src="county.php" name="county" width="700" height="300" frameborder="NO"></iframe>
<br>
<iframe src="city.php" name="city" width="700" height="300" frameborder="NO"></iframe>
</body>
</html>

county.php
<html>
<body>
<?php
#Fetch database connection
      require_once('Connections/myconn.php');
      mysql_select_db($database_myconn,$myconn);
      require_once('myfunctions.php');

echo $_POST[state]; ///it passes the value OK
?>

<form action='city.php' method='post' target="city">
<select name="county">
<?php
$q=mysql_query("SELECT DISTINCT County FROM zipcodes WHERE State=$_POST[state] GROUP BY County");
while($r=mysql_fetch_array($q)){
echo "<option>$r[County]</option>";
}
?>
</select>
<input type="submit" value="Get cityes">
<input type="hidden" value="<?=$_POST['state'] ?>" name="state">
</form>
</body>
</html>

city.php
<html>
<body>
<form action='result.php' method='post' target="_top">
<select name="city">
<?php

      require_once('Connections/myconn.php');
      mysql_select_db($database_myconn,$myconn);
      require_once('myfunctions.php');

$q=mysql_query("SELECT City FROM zipcodes WHERE State=$_POST[state] AND County=$_post[county]  GROUP BY City");
while($r=mysql_fetch_array($q)){
echo "<option>$r[City]</option>";
}
?>
</select>
<input type="submit" value="Get result">
<input type="hidden" value="<?=$_POST['state'] ?>" name="state">
<input type="hidden" value="<?=$_POST['city'] ?>" name="city">
</form>
</body>
</html>

=============================

Could you please, take a look what is wrong?

Thank you

Andy
0
 

Author Comment

by:andy7789
ID: 16656564
Kosta,

Thank you, I have fixed a few bugs and it is working now. The only problem is that it is not good to click "sumbit" buttin every time to refresh the next menu list. I am playing with onChange options in the forms, but it is not working. Could it be because of iframes? The code looks like this:

<html>
<body>
<form name="main" action="county.php" method="post" target="county" >
<select name="state" size=1 onChange="main.sumbit();">
<?php

      #Fetch database connection
      require_once('Connections/myconn.php');
      mysql_select_db($database_myconn,$myconn);
      require_once('myfunctions.php');

$q=mysql_query("SELECT Distinct State FROM zipcodes GROUP BY State");
while($r=mysql_fetch_array($q)){
echo "<option>$r[State]</option>";
}
?>
</select>
<input type="submit" value="Get State">
</form>
<iframe src="county.php" name="county" width="700" height="300" frameborder="NO"></iframe>
<br>
<iframe src="city.php" name="city" width="700" height="300" frameborder="NO"></iframe>
</body>
</html>

Do you have any other ideas how to avoid submit clicking?

Thanks

Andy
0
 

Author Comment

by:andy7789
ID: 16656674
I do not know why, but "onChange" does not work directly from the SELECT tag. I have added a simple script like
<script language=javascript>
  function go()
    {
      document.form1.submit();
     }
</script>
and it works perfectly. Thank you again Kosta
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

807 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