Setting up MySql country, state city table

[bzzrd2]

I'm working on an application where country city etc. is the key. I am able to set them up as individual table in MySql, however I'm sure there is a specific way this must be done to work properly. Here's whet I need to do.

Country (USA / Canada)
State / Province
Region in State or province
City

The customer will be ale to post to thier city but I want them to be able to see data by:

Country (which has many cities)
State (which has many cities)
Region (which has many cities)
City (and perhaps even a link to bring up cities close to them.

Can anyone steer me in the right direction with an idea of how the framework should look? Thanks. I'm sure you can tell that I'm fairly new to MySql.

[Dijkgraaf]

Usually you would do something like

Country table
CountrCode
CountrName

Province table
ProvinceCode
ProvinceName
CountryCode

Region table
RegionCode
RegionName
ProvinceCode

City table
CityCode
CityName
RegionCode

This is based on the assumption that a City only belongs to a single Region and Province.
In each case the last field in the above example shows to which parent group that particular instance belongs.
This way you can easily join the tables together in a query to find the Region/Province (or state) and Country to to which a city belongs.

[bzzrd2]

Thnak you Dijkgraaf. That is very helpful. I'll put it together, build some quieries and see what happens. Thanks again!

[bzzrd2]

Well, I got the tables made (thank you) and have been playing with querys. On the province page I have a list of the provinces and text that says "select". What I want to do is, when someone selects a province I want them to then see a list of the regions for that province. I have tried a few methods but I can't seem to get it to work. Found lot's of information on dynamic dropdowns...not what I need.

So all I'm trying to accomplish is:

Province ---- Click
Query region table
Bring back and display list of regions to user.

Can anyone get me going in the right direction or link me to a site that will help a newb?

Thanks, and this is kinda fun stuff to learn after doing mainly html for years!

[arran]

Hi bzzrd2,

Here's the SQL:

select RegionCode, RegionName from Region where ProvinceCode = $provice_code

Try searching google for "Introduction to SQL" [google.com]

Also, crashmon recommends a book that might help you in this thread [webmasterworld.com]

[bzzrd2]

Thanks Arron, I'll do more reading over the weekend. One more question. Can I not query against an alpha instead of a number?

I tried a query in MySql like so:
SELECT * FROM `region` WHERE province_id = AB
and I get:
#1054 - Unknown column 'AB' in 'where clause'

I get a similar error when I try your string.

I have the columns set like so:

region_id region_name province_id

-- ABE -- Alberta East -- AB

and so on for each province.

All are set to varchar(255)

Thanks again folks! As a newb this is the most helful forum I have found. Just smack me if I become a pain in the you know what!

[arran]

Sorry, i assumed your id columns (province_id and region_id) were numeric.

When you're dealing with varchars, you have to quote them e.g.

SELECT * FROM `region` WHERE province_id = 'AB'

[bzzrd2]

Well, I found some code which I was able to modify for 3 dropdowns. I need to add a fourth and I'm not sure if I set up my sql table wrong or if I'm messing up on the code. I named the table subcategory3 with colums subcat_id and subcat3. I tried copying down the table 2 PHP statements and pointing them to the table but with no sucess. Any help is much appreciated!

<?

///////// First List Box//////////
$quer2=mysql_query("SELECT DISTINCT category,cat_id FROM category order by category");
///////////// End ////////////

/////// Second List Box/////
$cat=$HTTP_GET_VARS['cat'];
if(isset($cat) and strlen($cat) > 0){
$quer=mysql_query("SELECT DISTINCT subcategory,subcat_id FROM subcategory where cat_id=$cat order by subcategory");}

////////// End ///////////////////////////

///////////////////////Third List Box/////////////////////
$cat3=$HTTP_GET_VARS['cat3']; // This line is added to take care if your global variable is off
if(isset($cat3) and strlen($cat3) > 0){
$quer3=mysql_query("SELECT DISTINCT subcat2 FROM subcategory2 where subcat_id=$cat3 order by subcat2");}
///////////////////////////////End////////////////////////

echo "<form method=post name=f1 action=''>";

////////// First Drop List ////////
echo "<select name='cat' onchange=\"reload(this.form)\"><option value=''>Select one</option>";
while($noticia2 = mysql_fetch_array($quer2)) {
if($noticia2['cat_id']==@$cat){echo "<option selected value='$noticia2[cat_id]'>$noticia2[category]</option>"."<BR>";}
else{echo "<option value='$noticia2[cat_id]'>$noticia2[category]</option>";}
}
echo "</select>";
////////////////// End ///////////

////////// Second Drop List /////////
echo "<select name='subcat' onchange=\"reload3(this.form)\"><option value=''>Select one</option>";
while($noticia = mysql_fetch_array($quer)) {
if($noticia['subcat_id']==@$cat3){echo "<option selected value='$noticia[subcat_id]'>$noticia[subcategory]</option>"."<BR>";}
else{echo "<option value='$noticia[subcat_id]'>$noticia[subcategory]</option>";}
}
echo "</select>";
////////////////// End ///////////

////////// Third Drop List /////////
echo "<select name='subcat3' ><option value=''>Select one</option>";
while($noticia = mysql_fetch_array($quer3)) {
echo "<option value='$noticia[subcat2]'>$noticia[subcat2]</option>";
}
echo "</select>";
////////////////// End ///////////

echo "</form>";
?>

3rd mysql table:
-- Table structure for table `subcategory3`
--

CREATE TABLE `subcategory3` (
`subcat_id` int(3) NOT NULL default '0',
`subcat3` varchar(15) collate latin1_general_ci NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `subcategory3`
--

INSERT INTO `subcategory3` VALUES (1, 'Calgary');
INSERT INTO `subcategory3` VALUES (1, 'Wheat');
INSERT INTO `subcategory3` VALUES (1, 'Prairie');
INSERT INTO `subcategory3` VALUES (1, 'Rock Head');
INSERT INTO `subcategory3` VALUES (3, 'Orange sweet');
INSERT INTO `subcategory3` VALUES (3, 'Orange Sour');
INSERT INTO `subcategory3` VALUES (66, 'Apple sweet');