Menu
Hi,
i was unsure about where to post this question since its php, mysql and ajax related. I want to build a dropdown with countries, states and cities. States box gets populated upon country selection and city box gets populated upon state selection as well. I already have a database with all the data but i really dont know whats the best way to load that data from my php page.
All the data is in on single table split into the following fields:
country_code, country_name, region_code, region_name, city, zipcode latitude, longitude and metrocode. Im not interested in zipcode and the last 3 so i stick with the date of the remaining fields. This is an example of the data in the fields:
DK Denmark 18 Midtjyllen Tranbjerg
I have several code examples but none of them seem to be suitable for using a database. Could you please help me?
Thank you.
[code=php]
echo "<select name="selectedid" size='1'>";
echo "<OPTION value="0" label="New Article">New Article</OPTION>"
$query = "SELECT ID, Title FROM content WHERE ID >=1 ORDER BY ID DESC LIMIT ". $numArticles2Sel." ";
//echo $query."<br>";
$result = mysql_query ($query,$link);
//echo "SELECTED ? = ".$articleID."<BR>";
while ($a_row = mysql_fetch_array ($result, MYSQL_BOTH)){
if ($articleID == $a_row[0]){$isSel = " SELECTED ";}else {$isSel="";}
echo "<OPTION ".$isSel." value='".$a_row[ID]."' label='".$a_row[Title]."' >".$a_row[Title]."</OPTION>n";
}
echo "</SELECT>";[/code][CODE]<? $currentpage= basename($_SERVER['SCRIPT_FILENAME']);?>
<SCRIPT language=JavaScript>
function reload(form)
{
// Setting the variable with the value of selected country's ID
var val=populate.countryList.options[populate.countryList.options.selectedIndex].value;
// Sending the country id in the query string to retrieve the state list
self.location='<? print $currentpage;?>?countryId=' + val ;
}
function reload2(form)
{
// Setting the variable with the value of selected state's ID
var val2=populate.stateList.options[populate.stateList.options.selectedIndex].value;
// Sending the state id in the query string to retrieve the city list
self.location='<? print $currentpage."?".$_SERVER['QUERY_STRING'];?>?stateId=' + val2 ;
}
</script>
<? print $currentpage ;?>
<?php
/*
- Function to return the Country list as an array
- The array can be generated from a database resultset
*/
function getCountryList()
{
// Country List array
$countryList = array (
'1' => 'Bangladesh',
'2' => 'USA',
'3' => 'UK'
);
return $countryList;
}
/*
- Function to return the state list as an array
- Country ID is used to generate the state list
*/
function getStateList($countryId)
{
// State list array
// First key of the array is the Country ID, which holds an array of State list
$stateList = array (
'1' => array ('Dhaka'=> 'Dhaka','Chittagong', 'What else'),
'3' => array ('London'=> 'London', 'Cannot Remember'),
'2' => array ('Washington', 'N.Y.', 'etc')
);
return $stateList[$countryId];
}
function getCityList($stateId)
{
// City list array
// First key of the array is the State ID, which holds an array of City list
$cityList = array (
'Dhaka' => array ('Dhaka', 'Chittagong', 'What else'),
'London' => array ('London', 'Cannot Remember'),
'2' => array ('Washington', 'N.Y.', 'etc')
);
return $cityList[$stateId];
}
?>
<form action="populate.php" name="populate">
<?
// Retrieving the country list
$countryList = getCountryList();
// Setting the variable if the country is selected for its state list
@$countryId = $_GET['countryId'];
// Retrieving the state list if a country is selected
$stateList = ($countryId) ? getStateList($countryId) : null;
// Retrieving the state list
$stateList = getstateList($countryId);
// Setting the variable if the state is selected for its city list
@$stateId = $_GET['stateId'];
// Retrieving the city list if a state is selected
$cityList = ($stateId) ? getCityList($stateId) : null;
if (!empty($countryList))
{
// Generating the country drop down menu
echo "<select onChange='reload(this.form)' name='countryList'>";
foreach ($countryList as $key => $value)
{
echo "<option value='$key'";
if ($countryId == $key)
echo "selected";
echo ">$value</option>";
}
echo "</select>";
}
if (!empty($stateList))
{
// Generating the state drop down menu if a country is selected
echo "<select onChange='reload2(this.form)' name='stateList'>";
foreach ($stateList as $key => $value)
{
echo "<option value='$key'";
if ($stateId == $key)
echo "selected";
echo ">$value</option>";
}
echo "</select>";
}
if (!empty($cityList))
{
// Generating the city drop down menu if a state is selected
echo "<select name='cityList'>";
foreach ($cityList as $key => $value)
{
echo "<option value='$key'>$value</option>";
}
echo "</select>";
}
?>
</form>[/CODE][code=html]
<script type="text/javascrip">
var states = new Array();
states['Canada'] = new Array('Alberta','British Columbia','Ontario');
states['Mexico'] = new Array('Baja California','Chihuahua','Jalisco');
states['United States'] = new Array('California','Florida','New York');
// City lists
var cities = new Array();
cities['Canada'] = new Array();
cities['Canada']['Alberta'] = new Array('Edmonton','Calgary');
cities['Canada']['British Columbia'] = new Array('Victoria','Vancouver');
cities['Canada']['Ontario'] = new Array('Toronto','Hamilton');
cities['Mexico'] = new Array();
cities['Mexico']['Baja California'] = new Array('Tijauna','Mexicali');
cities['Mexico']['Chihuahua'] = new Array('Ciudad Juárez','Chihuahua');
cities['Mexico']['Jalisco'] = new Array('Guadalajara','Chapala');
cities['United States'] = new Array();
cities['United States']['California'] = new Array('Los Angeles','San Francisco');
cities['United States']['Florida'] = new Array('Miami','Orlando');
cities['United States']['New York'] = new Array('Buffalo','new York');
</script>[/code] [code=html]
// The cities are in alphabetic order
var cities=['Calgary',1,'Edmonton',1,'Hamilton',3,'Toronto',3,'Victoria',2,'Vancouver',2];
// 1 2 et 3 are the states (or regions) codes
var states={'1':'Alberta','2':'British Columbia',3:'Ontario'};
// To select the cities from British Columbia (code 2) :
for (i=0;i<cities.length;i+=2) if (cities[i+1]==2) {...}
[/code][code=php]$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {die('Could not connect: ' . mysql_error());}
echo 'Connected successfully';
$query = "SELECT * FROM content WHERE ID... ORDER BY cities";
//echo $query."<br>"; to see what you do
$result = mysql_query ($query,$link);
$chnCts='';
while ($a_row = mysql_fetch_array ($result, MYSQL_BOTH)){
// foreach ($a_row as $k=>$v) echo "$k=>$v<br>"; to see what you get
$chnCts.=',"'.$a_row["cities"].'",'.$a_row["codeStates"];
}
// the first comma is to delete
$chnCts='var cities=['.substr($chnCts,1).'];';
$fci=fopen("canada.js","w+");
fwrite($fci,$chnCts);
fclose($fci);[/code][code=html]var cities="Calgary|1|Edmonton|1|Hamilton|3|Toronto|3|Victoria|2|Vancouver|2";
// Than a simple split give the tab
var tabCts=cities.split(/|/g);[/code][CODE] function onCountryChange() {
var component = $(COUNTRIES_LIST_ID);
var callback;
var countryId;
var regionsList = $(REGIONS_LIST_ID);
var isUsa;
if (component.selectedIndex < 0) {
countryId = 0;
} else {
countryId = component.options[component.selectedIndex].value;
}
doUsaSelectionLogic();
isUsa = isUsaSelected();
if (!isUsa) {
onUsaNotSelected();
regionsList = fastClearOptions(REGIONS_LIST_ID);
regionsList.disabled = true;
callback = function (count) {
if (count > 1) {
insertFirstOption(regionsList, "Choose region", 0, false, true);
regionsList.selectedIndex = 0;
}
regionsList.disabled = false;
onRegionChange(regionsList);
};
getRegionsListByCountryId(countryId, regionsList, callback);
}
}
// Do all logic on region change.
function onRegionChange() {
var component = $(REGIONS_LIST_ID);
var callback;
var regionId;
var citiesList = $(CITIES_LIST_ID);
if (component.selectedIndex < 0) {
regionId = 0;
} else {
regionId = component.options[component.selectedIndex].value;
}
citiesList = fastClearOptions(CITIES_LIST_ID);
citiesList.disabled = true;
callback = function (count) {
if (count > 1) {
insertFirstOption(citiesList, "Choose city", 0, false, true);
citiesList.selectedIndex = 0;
}
citiesList.disabled = false;
};
getCitiesListByRegionIdWithGeoInfo(regionId, citiesList, callback);
} [/CODE][code=html]function getCitiesListByRegionIdWithGeoInfo(idRegion, listBox, callback){}
function getCitiesListByCountryId(idCountry, listBox, callback){}
function getRegionsListByCountryId(idCountry, listBox, callback){}[/code]All this functions call this service [code=html]askSoapService('/ws/GeoService', 'GeoService', xml, internalCallback);[/code]0.1.9 — BETA 5.16