Link to home
Start Free TrialLog in
Avatar of global_expert_advice
global_expert_adviceFlag for India

asked on

populating combo box using mysql and php

hi all
i'm using the below link
http://roshanbh.com.np/2008/01/populate-triple-drop-down-list-change-options-value-from-database-using-ajax-and-php.html/comment-page-3
to populate 3 combo box country - state - city...
as per the instructions i did and got success..
however my requirement is one more combo box that is locality which should populate based on the city selection.

for this i created findLocality.php with code...
<?
include ("dbconnect.php");
?>

<? $countryId=intval($_GET['country']);
$stateId=intval($_GET['state']);
$cityId=intval($_GET['city']);
$query="SELECT id,locality FROM locality WHERE countryid='$countryId' AND stateid='$stateId' AND cityid='$cityId'";
$result=mysql_query($query);

?>
<select name="locality">
<option>Select Locality</option>
<? while($row=mysql_fetch_array($result)) { ?>
<option value=<?=$row['id']?>><?=$row['locality']?></option>
<? } ?>
</select>

To make this work... i added a new function too like this on the form page
function getLocality(countryId,stateId,cityId) {            
            var strURL="findLocality.php?country="+countryId+"&state="+stateId+"&city="+cityId;
            var req = getXMLHTTP();
            
            if (req) {
                  
                  req.onreadystatechange = function() {
                        if (req.readyState == 4) {
                              // only if "OK"
                              if (req.status == 200) {                                    
                                    document.getElementById('localitydiv').innerHTML=req.responseText;                                    
                              } else {
                                    alert("There was a problem while using XMLHTTP:\n" + req.statusText);
                              }
                        }                        
                  }                  
                  req.open("GET", strURL, true);
                  req.send(null);
            }
                        
      }

however to my luck... i get success til city but after that localityis not coming...

Could any one just let me know where i'm wrong, or instead let me know the fresh code to add extra box...

Need urgent help
Avatar of Mark Brady
Mark Brady
Flag of United States of America image

The way I would code that page is I would have each box auto submit on change so I would add
<select name="state" onchange="this.form.submit()">
// then get all your options from the DB like you are doing to populate it. Once it has been submitted I would do another query to get the cities based on the state that was selected. The once the city box has been selected, have that also submit the form (onchange="this.form.submit()") then you can do a final query to populate the localities.

If you have trouble understanding, please give me the column heading names and I will code up an example for you.
I have made an example for you. When you setup the tables in the attached sql (at the bottom of my script) and save the script as 'locations.php' - run it and you will see how it works. Select a state first, then watch how it works.

The only improvement I would make to this code is to write a function that works backwards so if you have already chosen a state and a county, then when you select a town, the state should only show the correct state and the county should only show the correct county. A simple function should do that but for now, it works forwards. IE:

1: Select a state and the next box appears with counties of that state
2: Select a county and the final selectbox appears with towns of the selected county.

Hope this helps.
<?php
//*******************// locations.php - coded by Mark Brady //*********************//
//                                                                                 //
//     The code below uses sessions to store selected values from dropdown select  //
//     boxes and populate other select boxes based on the selection of the first   //
//     one. To use, replace the line "Include("DB-connect.php");" with your        //
//     connection script or code. Create a table in a mysql database and name it   //
//     'locations'. Create 4 columns [ID - name - county - state].ID is "Auto Inc" //
//     and the others are set to "varchar(40)". Populate with some different areas //
//     of different counties of different states. Or use the sql dump at the end   //
//     of this script. Save your DB connection script (if external) to the same    //
//     folder as this file (locations.php). Any questions - please send to         //
//     elvin66 at extra dot co dot nz. ------------------ Enjoy                    //
/////////////////////////////////////////////////////////////////////////////////////
 
session_start();
if(is_array($_REQUEST)) {
              foreach($_REQUEST as $key => $value) {
                        $_SESSION[$key]      = $value;
              }
      }

if($_SESSION['reset'] == 1){            // The reset button has been pressed
session_destroy();                      // This is for the reset button and destroys the session and session variables
header("location: locations.php");      // This file

}else{
Include("DB-connect.php");  // Your connection script
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//******// Setup session variables ///////////////////////////////////////////////////////////////////////////////////////

$_state  = $_SESSION['state'];
$_county = $_SESSION['county'];
$_town   = $_SESSION['town'];   // This is the location or town
$_ID     = $_SESSION['ID'];     // Not used in this script but it's a good idea to have an ID for each entry for later use

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// Setup a basic form with one selectbox containing "states" only - at this stage
//
$form  = "<form action=\"locations.php\" method=\"POST\">";  // You could also just have "$form = "<form>";  It will do the same thing!
$select_state  = "<select name=\"state\" style=\"width:130px\" onchange=\"this.form.submit()\">";
$select_state .= "<option value=\"\">Select a State</option>";

// Create a query to populate the states selectbox (remember there are only 3 states in this example)
//
$sql_state = "SELECT DISTINCT `state` FROM `locations` ORDER BY `state` ASC";  // This will return all 3 states and sort them in alphabetical order
$result_state = mysql_query($sql_state)or die("Error with DB search");
$nums = mysql_num_rows($result_state);
//
// Populate the selectbox
while($row = mysql_fetch_array($result_state, MYSQL_ASSOC))
{
$name = "{$row['state']}";
if($name == $_state)
{$select_state .= "<option selected value=\"".$name."\">".$name."</option>";} // This will make sure once selected, it stays on the selection after refresh
else
{$select_state .= "<option value=\"".$name."\">".$name."</option>";}
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Now we display the form inside a basic table /////////////////////////////////////////////////////////////
//
echo "<center><table width=400 cellpadding=2 cellspacing=2 bgcolor=\"#234478\" style=\"font-family:arial;color:white\"><tr><td width=185>";
//
echo $form.$select_state."</select></td><td><font color=yellow>";

// See if a state has been selected
//
if($_state != ""){ echo "<strong>You selected <font color=red>".$_state."</td></tr>";
//
// Create a query to search the locations table for counties in the chosen state
$sql_search_counties = "SELECT DISTINCT `county` FROM `locations` WHERE `state`='$_state' ORDER BY `county` ASC";
$result_counties = mysql_query($sql_search_counties)or die("Error with DB search");
$select_county   = "<select name=\"county\" style=\"width:130px\" onchange=\"this.form.submit()\">";
$select_county  .= "<option value=\"\">Select a County</option>";
//
// Populate the selectbox
while($row = mysql_fetch_array($result_counties, MYSQL_ASSOC))
{
$name = "{$row['county']}";
if($name == $_county)
{$select_county .= "<option selected value=\"".$name."\">".$name."</option>";} // This will make sure once selected, it stays on the selection after refresh
else
{$select_county .= "<option value=\"".$name."\">".$name."</option>";}
}
// Display the County selectbox
//
echo "</tr><td>".$select_county."</select></td><td><font color=yellow>";

                 }else{  // No state has been selected
echo "</td></tr>";
}

/////////////////////////////////// Finally, the location or town selectbox ////////////////////////////////////////////////////////
//
// See if a county has been selected
//
if($_county != ""){ echo "<strong>You selected <font color=red>".$_county."</td></tr>";
//
// Create a query to search the locations table for towns in the chosen county
$sql_search_towns = "SELECT  `name` FROM `locations` WHERE `county`='$_county' ORDER BY `name` ASC";
$result_towns  = mysql_query($sql_search_towns)or die("Error with DB search");
$select_towns  = "<select name=\"town\" style=\"width:130px\" onchange=\"this.form.submit()\">";
$select_towns .= "<option value=\"\">Select a Town</option>";
//
// Populate the selectbox
while($row = mysql_fetch_array($result_towns, MYSQL_ASSOC))
{
$name = "{$row['name']}";
if($name == $_town)
{$select_towns .= "<option selected value=\"".$name."\">".$name."</option>";} // This will make sure once selected, it stays on the selection after refresh
else
{$select_towns .= "<option value=\"".$name."\">".$name."</option>";}
}
// Display the towns selectbox
//
echo "</tr><td>".$select_towns."</select></td><td><font color=yellow>";

                 }else{  // No county has been selected
echo "</td></tr>";
}
//
// See if a Town has been selected
//
if($_town!= ""){ echo "<strong>You selected <font color=red>".$_town."</td></tr>";}
//
//////////////////////////////////  End of Select Boxes ////////////////////////////////////////////////////////////////////////////
//
echo "</table></form>";
}
?>
<!-- The Reset Button <!-->
<br><br>
<SCRIPT LANGUAGE="JavaScript">
function Reset()
{
session_destroy();
}
</script>

<form method="POST" action="locations.php">
<input type="hidden" name="reset" value="1">
<input type="submit" value="Reset">
</form>
<!--   End of locations.php script <!-->

<!--   The following code is a mysql dump for the sql data. Save everything below into a file and name it data.sql-Import into mysql <!--><!--

-- phpMyAdmin SQL Dump
-- version 3.2.2.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 31, 2009 at 01:12 PM
-- Server version: 5.1.37
-- PHP Version: 5.2.10-2ubuntu6.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `locations`
--

CREATE TABLE IF NOT EXISTS `locations` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `county` varchar(40) NOT NULL,
  `state` varchar(40) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;

--
-- Dumping data for table `locations`
--

INSERT INTO `locations` (`ID`, `name`, `county`, `state`) VALUES
(1, 'winter park', 'orange', 'florida'),
(2, 'edgewood', 'orange', 'florida'),
(3, 'apopka', 'orange', 'florida'),
(4, 'orlando', 'orange', 'florida'),
(5, 'daytona beach', 'volusia', 'florida'),
(6, 'deltona', 'volusia', 'florida'),
(7, 'oak hill', 'volusia', 'florida'),
(8, 'edgewater', 'volusia', 'florida'),
(9, 'boulder creek', 'santa cruz', 'california'),
(10, 'ben lomond', 'santa cruz', 'california'),
(11, 'felton', 'santa cruz', 'california'),
(12, 'corralitos', 'santa cruz', 'california'),
(13, 'rosemont', 'san diego', 'california'),
(14, 'coronado', 'san diego', 'california'),
(15, 'san marcos', 'san diego', 'california'),
(16, 'fallbrook', 'san diego', 'california'),
(17, 'ellerslie', 'harris', 'georgia'),
(18, 'hamilton', 'harris', 'georgia'),
(19, 'kingboro', 'harris', 'georgia'),
(20, 'whitesville', 'harris', 'georgia'),
(21, 'alexander', 'burke', 'georgia'),
(22, 'shell bluff', 'burke', 'georgia'),
(23, 'murray hill', 'burke', 'georgia'),
(24, 'midville', 'burke', 'georgia');

<!-->
<!-- End of dump. Copy only to "24" above  <!-->

Open in new window

This code :

<SCRIPT LANGUAGE="JavaScript">
function Reset()
{
session_destroy();
}
</script>

Just ignore that, it does nothing..... There is a reset button below that works
Avatar of global_expert_advice

ASKER

thanks for the details..
i will go thru it and let u know if any problem
thanks again
hi
tried ur code.. but in different manner...
more over, i do need to work on field values.. which is to be used for further query....
first i wanted to get country list
state list
city list
locality list

so what i'm doing is fetching the country list using sql query.. and using onchange this.value submit...
to get city list...
but once page refreshes,... i'm loosing the country value id and its name.... i know it has to be saved in session... i tried...
likewise rest of the things has to be done...

i tried ur way... like when countryid=$_country
use of select option...
or else default country select...

my code is below

<?php

session_start();
if(is_array($_REQUEST)) {
              foreach($_REQUEST as $key => $value) {
                        $_SESSION[$key]      = $value;
              }
      }
 
if($_SESSION['reset'] == 1){            // The reset button has been pressed
session_destroy();                      // This is for the reset button and destroys the session and session variables
header("location: locations_iws.php");      // This file
 
}else{
include("dbconnect.php");  // Your connection script
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
 
//******// Setup session variables ///////////////////////////////////////////////////////////////////////////////////////
 
$_country = $_SESSION['country'];
$_countyid = $_SESSION['countryid'];
$_state  = $_SESSION['state'];
$_stateid  = $_SESSION['stateid'];
$_city   = $_SESSION['city'];   // This is the location or town
$_cityid   = $_SESSION['cityid'];
$_locality   = $_SESSION['locatlity'];
$_localityid   = $_SESSION['locatlityid'];
}
 
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
?>
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>

<div align="center">
      <table border="0" width="100%" cellspacing="0" cellpadding="0">
            <tr>
                  <td>
                  <form method="POST" action="#">
                        <table border="0" width="100%" cellspacing="0" cellpadding="0">
                              <tr>
                                    <td>Country</td>
                                    <td><?php
      $query="SELECT id,country FROM country";
      $result=mysql_query($query);
            ?>
<select name="country" onchange="this.form.submit()">
<option>Select Country</option>
<? while($row=mysql_fetch_array($result)) {
?>
<option value="<?=$row['id']?>"><?=$row['country']?></option>
<? } ?>
</select>
</td>
                              </tr>
                              <tr>
                                    <td>&nbsp;State</td>
                                    <td><?php
      $query="SELECT id,statename FROM state WHERE countryid=$_country";
      $result=mysql_query($query);
            ?>
<select name="state" onchange="this.form.submit()">
<option>Select State</option>
<? while($row=mysql_fetch_array($result)) { ?>
<option value="<?=$row['id']?>"><?=$row['statename']?></option>
<? } ?></td>
                              </tr>
                              <tr>
                                    <td>city</td>
                                    <td><?php
      $query="SELECT id,city FROM city WHERE countryid='$_country' AND stateid='$_state'";
      $result=mysql_query($query);
            ?>
<select name="city" onchange="this.form.submit()">
<option>Select City</option>
<? while($row=mysql_fetch_array($result)) { ?>
<option value="<?=$row['id']?>"><?=$row['city']?></option>
<? } ?></td>
                              </tr>
                              <tr>
                                    <td>Locality</td>
                                    <td><?php
      $query="SELECT id,locality FROM locality WHERE countryid='$_country' AND stateid='$_state' AND cityid='$_city'";
      $result=mysql_query($query);
            ?>
<select name="locality" onchange="this.form.submit()">
<option>Select Locality</option>
<? while($row=mysql_fetch_array($result)) { ?>
<option value="<?=$row['id']?>"><?=$row['locality']?></option>
<? } ?></td>
                              </tr>
                              <tr>
                                    <td>&nbsp;</td>
                                    <td>&nbsp;</td>
                              </tr>
                        </table>
                        <p><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
                  </form>
                  <p>&nbsp;</td>
            </tr>
      </table>
</div>

</body>

</html>

I want things in this manner... as i'm not comfortable with echo system...
please modify it...
regards
zia
I gave you a working code that I coded from scratch. I tested this code and it works to answer your question. The code you have posted is totally rewritten so I can't go through your code and fix up the errors. You will need to do that. When you ask a question on EE, we try to give you guidence by posting code that works. The idea is that you will look at what we have done and ask questions about how to modify it to suit your needs if required. Unfortunately, I have not got the time to go through your latest code and sort it out for you. If you were to use my code and tell me exactly what you want it to do that it does not do already, then I would be able to help you.

I'm just trying to help you with your learning here.
ASKER CERTIFIED SOLUTION
Avatar of Mark Brady
Mark Brady
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
hi
thanks for the wonderful advice... and i'm sure going to take the tips..
i'll start working on ur method...and i will try to modify your code in your way...
so that first i should get country --- than state --- than city --- than locality... my intention is this only..
also after fetching all these details... the value of each field should be the id's...
In my case i already created 4 tables... like country, state, city and locality..

so what i'm going to try now... is using your code... first to fetch country using query to country table.. and taking the country id and country name.. and taking the same in session... so that when i make query to state... i need where countryid = ...

actually first i tried ur code... but got something messes... i will re try.. things and let u know..
once again thanks... and happy new year... my new year will start after 3 hrs.. hope to solve this issue before new year... i don't want to take incomplete coding :-)
thanks
Ok if you were to explain how your tables are set up (column names etc) then it would be simple to modify my code to suit. In my code, you would modify the mysql query (each one) to include the ID. Something like

$sql = "SELECT DISTINCT `state`, `ID` FROM `locations WHERE......blah blah... you get the idea.

As for your separate tables, you can do it this way but you can also do it in one table like I have done. I suppose if you have a lot of information then separate tables would be better but if it is just "Country" - "State" - "City" - "County" - "Town" then you could do all this with one table.

If you want to use separate tables then you can use the "join" method to connect values from each table by linking on of the fields which would be a common field like "ID".

Anyway, good luck. I'll be awake for a short time before I pass out with alcohol induced coma !
okay it has been solved now... i used my first method only using jquery... and without refreshing page state city locality populates...
my code was right.. just a mistake in $country....
your idea too is good... but it makes the page refresh again....
and chances of loosing session variables...
thanks
not to what i asked
cOOL THANKS ANYWAY.