SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Aug 2002
    Location
    Colorado Springs, CO
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Triple dependent drop down using MySQL and PHP

    I am new to all of this and I'm totally lost.


    I have the following 'Customer' table in a MySQL database:

    CustomerID
    CustomerName
    Address
    City
    StateProv
    PostalCode
    Country

    There are numerous customers and I need three drop down
    lists that allow the user to select a Country then based
    on that country, the next drop down list will list the
    states for that country. After a state is selected the
    third drop down box will contain a list of the cities
    for that state. There will be a button at the bottom of
    the lists (Display List) this sends these choices to a
    new page that displays only the customers for the country,
    state and city chosen.

    I have been trying to avoid using JavaScript, so I have
    come up with the following code. The problem is I get the
    list of countries, but the information is not passed to the
    selectCountry variable, so nothing works.

    Can anyone tell me what I'm missing?



    Code:
    <?php 
    
    include("db.php"); 
    mysql_select_db($dbname);
    
    if (isset($HTTP_POST_VARS['clear'])) { 
    unset($HTTP_POST_VARS['selectCity']); 
    unset($HTTP_POST_VARS['selectState']); 
    unset($HTTP_POST_VARS['selectCountry']); 
    } 
    
    if (!isset($HTTP_POST_VARS['selectCountry'])) { 
    $selectCountry = ""; 
    } 
    if (!isset($HTTP_POST_VARS['selectState'])) { 
    $selectState = ""; 
    } 
    if (!isset($HTTP_POST_VARS['selectCity'])) { 
    $selectCity = ""; 
    } 
    
    $cityList = array();  
    $stateList = array();  
    $countryList = array();  
    
    $get_countries = mysql_query("SELECT DISTINCT (Country) FROM `Customer` ORDER BY Country ASC ");
    if (!$get_countries) {  
    print "get_countries<br>"; 
    print mysql_error() . "<br><br>";  
    }  
    
    if (isset($HTTP_POST_VARS['selectCountry'])) 
    { 
        $selectCountry = $HTTP_POST_VARS['selectCountry'];  
        $get_states = mysql_query("SELECT DISTINCT (StateProv) FROM Customer WHERE Customer.Country = '$selectCountry' ORDER BY StateProv ASC");
        if (!$get_states) {  
        print "get_state<br>"; 
        print mysql_error() . "<br><br>";  
        }  
    }  
    
    if (isset($HTTP_POST_VARS['selectCity'])) {  
        $selectState = $HTTP_POST_VARS['selectState'];  
        $selectCity = $HTTP_POST_VARS['selectCity'];  
        $get_cities = mysql_query("  
        SELECT DISTINCT (City) FROM Customer WHERE Customer.StateProv = '$selectState' AND Customer.Country = '$selectCountry'");  
        if (!$get_cities) {  
        print "get_cities<br>"; 
        print mysql_error() . "<br><br>";  
        } 
    } 
    
    if (@mysql_num_rows($get_countries)!=0) { 
    while ($item = mysql_fetch_array($get_countries)) {  
    if ($item["selectCountry"] == $selectCountry) {$sel = " selected";} else {$sel = "";}  
    array_push ($countryList,"<option value=\"" . $item["selectCountry"] . "\" " . $sel . ">" . $item["Country"] . "</option>");  
        } 
    } 
    else { 
    array_push ($countryList,"<option>---------</option>"); 
    } 
    
    if (@mysql_num_rows($get_states)!=0) { 
    while ($item = mysql_fetch_array($get_states)) {  
    if ($item["State"] == $selectState) {$sel = " selected";} else {$sel = "";}  
    array_push ($cityList,"<option value=\"" . $item["selectState"] . "\" " . $sel . ">" . $item["State"] . "</option>");  
        } 
    } 
    else { 
    array_push ($cityList,"<option>---------</option>"); 
    } 
    
    if (@mysql_num_rows($get_cities)!=0) { 
    while ($item = mysql_fetch_array($get_cities)) {  
    if ($item["City"] == $selectCity) {$sel = " selected";} else {$sel = "";}  
    array_push ($stateList,"<option value=\"" . $item["selectCity"] . "\" " . $sel . ">" . $item["City"] . "</option>");  
        } 
    } 
    else { 
    array_push ($stateList,"<option>---------</option>"); 
    } 
    
    ?> 
    
    <html>
    <head>
    <title>Directory</title><meta name="Microsoft Border" content="b, default"
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    
    </head>
    
    <body>
    <table border="1" width="950" cellpadding="5">
    
      <tr>
      	<td width="150" valign="top" bgcolor="#33CCFF"> 
    	<?
            include('mainMenu.inc');
        ?> 
    	</td>
        <td valign="top" > 
    		<?
    	      include('chooseHeader.inc');
    		?>
    		<table width="800" cellpadding="5">
    		<p align="left"><font size="3" face="Verdana, Arial, Helvetica, sans-serif">
    		Select the name of the country, press 'SELECT'. A list of states will appear,
    		Select the name of the state, press 'SELECT'. A list of cities will appear,
    		Select the name of the city, press 'SELECT'. 
    		Use the 'DISPLAY LIST' button to see the cities listed the chosen state.</font><font size="3" face="Verdana, Arial, Helvetica, sans-serif"> 
    		</font></p>
    		
    		<?php 
    		if (isset($_SERVER)) $PHP_SELF = $_SERVER['PHP_SELF'];  
    		?>  
    		
    		<FORM ACTION="<?php print $PHP_SELF; ?>" METHOD="POST">
    		<input type="hidden" name="selectCountry" value="<?php print @$selectCountry; ?>"> 
    		<input type="hidden" name="selectState" value="<?php print @$selectState; ?>"> 
    		<input type="hidden" name="selectCity" value="<?php print @$selectCity; ?>"> 
    		
    		<tr><td> 
    		<font face="Verdana, Arial, Helvetica, sans-serif" size="4"> <font size="4">Choose a Country: 
    		<select name="selectCountry"> 
    		<?php  
    		foreach ($countryList as $row) { 
    		print $row; 
    		} 
    		?> 
    		</select> </font>
    		</tr></td>
    		
    		<tr><td>
    		<font face="Verdana, Arial, Helvetica, sans-serif" size="4">Choose a State: 
    		<select name="selectState"> 
    		<?php  
    		foreach ($cityList as $row) { 
    		print $row; 
    		} 
    		?> 
    		</select> </font>
    		</td></tr>
    		
    		<tr><td> 
    		<font face="Verdana, Arial, Helvetica, sans-serif" size="4">Choose a City:
    		<select name="selectCity"> 
    		<?php  
    		foreach ($stateList as $row) { 
    		print $row; 
    		} 
    		?> 
    		</select> </font>
    		</td></tr>
    		
    		<tr><td>
    		<input type="submit" value="NEXT" name="next"> 
    		<input type="submit" value="CLEAR" name="clear"> 
    		</td></tr>
    		
    	</form> 
    	<tr><td>
    	<FORM name="passData" method=post action="displayList.php">
    		<div align="center">
    			<center>
    				<p><span style="font-family: Verdana, Arial"> 
    					<input type="submit" value="Display List" >
    				</span></p>
    			</center>
    		</div>
    	</form>
    	</td></tr>	
    	<br> 
    	</td>
    	<tr>
    		<td>
    			<?php
    				 include('mainFooter.inc');
    			?>
    		</td>
    	</tr>
    	</table>
    	
    </body>

  2. #2
    PHP Developer W1LL's Avatar
    Join Date
    Apr 2001
    Location
    Leicester, UK
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've solved it! I'm not a Javascript person so I've just used form buttons, but you could probably change that so that when someone just clicks on the drop-down menu item it submits the form. But here is what I've come up with. It's at http://www.cbuk.org/adbookv20/triple_dropdown.php by the way:
    PHP Code:
    <?php
      
    if ($stage == "1") {
        
    session_start();
        
    session_register('Country');
      } elseif (
    $stage == "2") {
        
    session_start();
        
    session_register('Country');
        
    session_register('State');
      } elseif (
    $stage == "3") {
        
    session_start();
        
    session_register('Country');
        
    session_register('State');
        
    session_register('City');
      }

      include(
    'includes/dbConnect.php');
    ?>

    <html>

    <body>

    <form name="step1" action="triple_dropdown.php?stage=1" method="post">
    <select name="Country">
        <?php
          $country 
    = @mysql_query("SELECT * FROM Locations WHERE Country = '' AND State = '' ORDER BY PlaceName") or die ("<p>ERROR!</p>");
          while (
    $row = @mysql_fetch_array($country)) {
            echo(
    "<option value=\"" $row["PlaceName"] . "\">" $row["PlaceName"]);
          }
        
    ?>
    </select> <input type="submit" value="-->">
    </form>

    <?php
      
    if ($stage >= 1) {
        echo(
    "<form name=\"stage2\" action=\"triple_dropdown.php?stage=2\" method=\"post\"><select name=\"State\">");
        
    $state = @mysql_query("SELECT * FROM Locations WHERE Country = '$Country' AND State = '' ORDER BY PlaceName") or die ("<p>ERROR!</p>");
        while (
    $row = @mysql_fetch_array($state)) {
          echo(
    "<option value=\"" $row["PlaceName"] . "\">" $row["PlaceName"]);
        }
        echo(
    "</select> <input type=\"submit\" value=\"-->\"></form>");
      }
    ?>

    <?php
      
    if ($stage >= 2) {
        echo(
    "<form name=\"stage3\" action=\"triple_dropdown.php?stage=3\" method=\"post\"><select name=\"City\">");
        
    $city = @mysql_query("SELECT * FROM Locations WHERE Country = '$Country' AND State = '$State' ORDER BY PlaceName") or die ("<p>ERROR!</p>");
        while (
    $row = @mysql_fetch_array($city)) {
          echo(
    "<option value=\"" $row["PlaceName"] . "\">" $row["PlaceName"]);
        }
        echo(
    "</select> <input type=\"submit\" value=\"-->\"></form>");
      }
    ?>

    <p><b>
    <?php
      
    if ($stage == "3") {
        echo(
    $City ", " $State ", " $Country);
      }
    ?>
    </b></p>

    </body>

    </html>
    Hope this is what you wanted?

    - Will

  3. #3
    PHP Developer W1LL's Avatar
    Join Date
    Apr 2001
    Location
    Leicester, UK
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, and I used the following table as an example:
    Code:
    ------------------------------------------------------------
    |  ID  |     PlaceName     |   Country   |      State      |
    ------------------------------------------------------------
    |   1  | England           |             |                 |
    |   2  | USA               |             |                 |
    |   3  | Northamptonshire  | England     |                 |
    |   4  | Oxfordshire       | England     |                 |
    |   5  | Buckinghamshire   | England     |                 |
    |   6  | Northampton       | England     | Northamptonshire|
    |   7  | Roade             | England     | Northamptonshire|
    |   8  | Towcester         | England     | Northamptonshire|
    |   9  | Florida           | USA         |                 |
    |  10  | Ohio              | USA         |                 |
    |  11  | Fort Lauderdale   | USA         | Florida         |
    |  12  | Miami             | USA         | Florida         |
    ------------------------------------------------------------
    For example: Roade is a City in the State of Northamptonshire, which is in the Country of England!

    Get in contact with me if this is or isn't what you were looking for.

    - Will

  4. #4
    SitePoint Member
    Join Date
    Aug 2002
    Location
    Colorado Springs, CO
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Not quite right...

    Will,

    I tried your example but it's not working quite right.
    If I choose "England" and do a select, I only get states
    for the US, and it relists England as the chosen country.
    Even if I choose USA and Florida, I get no cities, and the
    display goes back to the original list.

    What I'm trying to do is Select a Distinct Country,
    then keep that country in the display and only list the
    states in the selected country. Then follow the same
    pattern with the cities. When the screen is redisplayed, I
    want it to display the original country selected, the
    original state selected and a list of the cities for that
    state only, so that when I change pages and do the display
    list I am only passing the specified city, state and
    country name.

    The code that I started, finds the Distinct countries but
    when I hit the Next button, it puts the country back to the
    last one in the list and doesn't give me a list of states.


    Thanks for trying,

    CC

  5. #5
    SitePoint Member
    Join Date
    Aug 2002
    Location
    Colorado Springs, CO
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I finally got this working so I thought I'd put it
    in here. This is using PHP version 4.0.6, so I can't
    use the new globals ($_POST, etc.)
    Code:
    <?php
      if ($stage == "0"){
         session_start();
    	 unset($HTTP_SESSION_VARS['Country']);
    	 unset($HTTP_SESSION_VARS['State']);
    	 unset($HTTP_SESSION_VARS['City']);
    	 session_destroy();
    	 }
      if ($stage == "1") {
        session_start();
        session_register('Country');
      } elseif ($stage == "2") {
        session_start();
        session_register('Country');
        session_register('State');
      } elseif ($stage == "3") {
        session_start();
        session_register('Country');
        session_register('State');
        session_register('City');
      }
    
       include('db.php');
       mysql_select_db($dbname);
    ?>
    
    <html>
    <head>
    <title>Find Country</title><meta name="Microsoft Border" content="b, default"
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    
    <body>
    <table border="1" width="950" cellpadding="5">
    
      <tr>
      	<td width="150" valign="top" bgcolor="#33CCFF"> 
    	<?
            include('mainMenu.inc');
        ?> 
    	</td>
        <td valign="top" background="images/blue.jpg"> 
    		<?
    	      include('findHeader.inc');
    		?>
    		<table width="800" cellpadding="5">
    		<p align="left"><font size="2" face="Verdana, Arial, Helvetica, sans-serif">
    		Select the name of the country, press '--->'. A list of states will appear.
    		<br>
    		Select the name of the state, press '--->'. A list of cities will appear.
    		<br>
    		Select the name of the city, press '--->'.
    		</font></p>
    		<form name="stage1" action="findCountry.php?stage=1" method="post">
    		<font face="Verdana, Arial, Helvetica, sans-serif" size="2"> <font size="4">Choose a Country: 
    		<select name="Country">
    		<?php
    		    $country = @mysql_query ("SELECT DISTINCT (Country) FROM `Customer` ORDER BY Country ASC");
    			      while ($row = @mysql_fetch_array($country)) 
    				  {
    				        echo("<option value=\"" . $row["Country"] . "\">" . $row["Country"]);
    				  }
    		?>
    		</select> 
    		<input type="submit" value="--->">
    		</font>
    		</form>
    		
    		<?php
    		    if ($stage >= 1) 
    			{?>
    			<form name="stage2" action="findCountry.php?stage=2" method="post">
    			<font face="Verdana, Arial, Helvetica, sans-serif" size="4">Choose a State: 
    			<select name="State">
    			<?php
    				$state = @mysql_query("SELECT DISTINCT (StateProv) FROM Customer WHERE Country = '$Country' ORDER BY StateProv ASC");
    				    while ($row = @mysql_fetch_array($state)) 
    					{
    						echo("<option value=\"" . $row["StateProv"] . "\">" . $row["StateProv"]);
    					}?>
    			</select> 
    			<input type="submit" value="--->"></font></form>
    			<?php
    			}?>
    			
    			<?php
    			    if ($stage >= 2) 
    				{?>
    				<form name="stage3" action="findCountry.php?stage=3" method="post">
    				<font face="Verdana, Arial, Helvetica, sans-serif" size="4">Choose a City:
    				<select name="City">
    				<?php
    				    $city = @mysql_query("SELECT DISTINCT (City) FROM Customer WHERE StateProv = '$State' AND Country = '$Country'");
    					    while ($row = @mysql_fetch_array($city)) 
    						{
    						      echo("<option value=\"" . $row["City"] . "\">" . $row["City"]);
    						}?>
    				</select> 
    				<input type="submit" value="--->"></font></form>
    				<?php
    				}?>
    				<p>
    				<?php
    					if ($stage == "3") 
    					{?>
    						<font face="Verdana, Arial, Helvetica, sans-serif" size="3">You have chosen:</font>
    						<b>
    						<font color="#FF0000">
    						<?php
    						echo($City . ", " . $State . ", " . $Country);
    						?>
    						</font>
    						</b>
    						<font face="Verdana, Arial, Helvetica, sans-serif" size="3">
    						<br>
    						<br>If this is correct press the <b>"Display List"</b> button to continue.
    						<br>
    						<br>If this in not correct press the <b>"Clear"</b> button to start again.
    						<br>
    						</font>
                            <FORM name="passData" method="post" action="displayList.php?<?php $HTTP_POST_VARS['City'];$HTTP_SESSION_VARS['State']; $HTTP_SESSION_VARS['Country'];?>">
    						<div align="center">
    							<center>
    								<p><span style="font-family: Verdana, Arial"> 
    									<input type="submit" value="Display List">
    								</span></p>
    							</center>
    						</div>
    						</form>
    						<?php
    					}?>
    				</td></tr>
    				</p>
    				<br> 
    				<tr><td>
    				<FORM name="clearData" action="findCountry.php?stage=0" method="post">
    				<div align="center">
    					<center>
    						<p><span style="font-family: Verdana, Arial"> 
    						<input type="submit" value="CLEAR" name="clear">
    						</span></p>
    					</center>
    				</div>
    				</form>
    				</td></tr>
    		</td>
    		<td>
    			<?php
    				 include('mainFooter.inc');
    			?>
    		</td>
    	</tr>
    	</table>	
    </body>
    Hope this is helpful to someone else.

    CC

  6. #6
    SitePoint Member
    Join Date
    Dec 2001
    Location
    oslo
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is there php a way to do this without the --> buttons, i'm pretty sure this can't be done with php but i would really love to do this without javascript, buttons or reloading

    :water

  7. #7
    SitePoint Enthusiast 27groo27's Avatar
    Join Date
    Apr 2003
    Location
    US
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Trying to follow this thread; in the last code, what would the findCountry.php code look like?

    Thanks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •