Problem with City/State Integration

[rubenmajor]

I'm building an SQL Query with Dreamweaver and I can't seem to get what I want. I am trying to find a way to search by state and then by city. I can get the records to pull up by DISTINCT states, but I need it to search via DISTINCT state and then by DISTINCT city. How do I do this?

I am definitely a beginner in this aspect, but I would be more than grateful for any help I can get.

The code is pretty basic so I'm not posting it yet. There is not a problem with the code, it's just that I don't know what it is....

:eek:

[rubenmajor]

What I meant, I think was something relative to the parent/child aspect where the page refreshes after you select a state - i.e. two list/menus.

[Seiya]

You will need to learn some ajax to accomplish this with an instant refresh... otherwise just have them select one first and then the other... actually you will have to do it this way even with AJAX,, since ajax just gets the new page and makes it refesh without refreshing, if you know what i mean. :confused:

Well the thing is you need 1 query for the parent category, then another one for the child category.

[falcondriver]

you need 3 tables:

tbl_city:
--------
city_id: int
city_name: varchar
state_id: int (your connection to related state)

tbl_state:
--------
state_id: int
state_name: varchar
state_short: char(2) (in case you need the "FL" for florida etc.)
country_id: int (your connection to related country)

sql to create a country table, enjoy or set a link t my page if you find it usefull :)
you can find some flag icons at countomat.com, just use <img src="'.country['flag'].'.gif"> if you need it. you may delete the last few entrys, because they are no exactly countrys.


--
-- Table structure for table `country`
--

CREATE TABLE `country` (
`country_id` int(11) NOT NULL auto_increment,
`name` varchar(30) collate latin1_general_ci NOT NULL default '',
`type` smallint(1) NOT NULL default '1',
`flag` char(2) collate latin1_general_ci NOT NULL default 'xx',
PRIMARY KEY (`country_id`),
KEY `countrytype` (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=210 ;

--
-- Dumping data for table `country`
--

INSERT INTO `country` VALUES (1, 'Afghanistan', 1, 'af');
INSERT INTO `country` VALUES (2, 'Albania', 1, 'xx');
INSERT INTO `country` VALUES (3, 'Algeria', 1, 'xx');
INSERT INTO `country` VALUES (4, 'Andorra', 1, 'xx');
INSERT INTO `country` VALUES (5, 'Angola', 1, 'xx');
INSERT INTO `country` VALUES (6, 'Antigua and Barbuda', 1, 'xx');
INSERT INTO `country` VALUES (7, 'Argentina', 1, 'ar');
INSERT INTO `country` VALUES (8, 'Armenia', 1, 'xx');
INSERT INTO `country` VALUES (9, 'Australia', 1, 'au');
INSERT INTO `country` VALUES (10, 'Austria', 1, 'at');
INSERT INTO `country` VALUES (11, 'Azerbaijan', 1, 'xx');
INSERT INTO `country` VALUES (12, 'Bahamas', 1, 'xx');
INSERT INTO `country` VALUES (13, 'Bahrain', 1, 'xx');
INSERT INTO `country` VALUES (14, 'Bangladesh', 1, 'xx');
INSERT INTO `country` VALUES (15, 'Barbados', 1, 'xx');
INSERT INTO `country` VALUES (16, 'Belarus', 1, 'xx');
INSERT INTO `country` VALUES (17, 'Belgium', 1, 'be');
INSERT INTO `country` VALUES (18, 'Belize', 1, 'xx');
INSERT INTO `country` VALUES (19, 'Benin', 1, 'xx');
INSERT INTO `country` VALUES (20, 'Bhutan', 1, 'xx');
INSERT INTO `country` VALUES (21, 'Bolivia', 1, 'xx');
INSERT INTO `country` VALUES (22, 'Bosnia', 1, 'xx');
INSERT INTO `country` VALUES (23, 'Botswana', 1, 'xx');
INSERT INTO `country` VALUES (24, 'Brazil', 1, 'br');
INSERT INTO `country` VALUES (25, 'Brunei', 1, 'xx');
INSERT INTO `country` VALUES (26, 'Bulgaria', 1, 'bg');
INSERT INTO `country` VALUES (27, 'Burkina Faso', 1, 'xx');
INSERT INTO `country` VALUES (28, 'Burundi', 1, 'xx');
INSERT INTO `country` VALUES (29, 'Cambodia', 1, 'xx');
INSERT INTO `country` VALUES (30, 'Cameroon', 1, 'xx');
INSERT INTO `country` VALUES (31, 'Canada', 1, 'ca');
INSERT INTO `country` VALUES (32, 'Cape Verde', 1, 'xx');
INSERT INTO `country` VALUES (33, 'Central African Rep.', 1, 'xx');
INSERT INTO `country` VALUES (34, 'Chad', 1, 'xx');
INSERT INTO `country` VALUES (35, 'Chile', 1, 'cl');
INSERT INTO `country` VALUES (36, 'China', 1, 'cn');
INSERT INTO `country` VALUES (37, 'Colombia', 1, 'co');
INSERT INTO `country` VALUES (38, 'Comoros', 1, 'xx');
INSERT INTO `country` VALUES (39, 'Congo (Brazzaville)', 1, 'xx');
INSERT INTO `country` VALUES (40, 'Congo', 1, 'xx');
INSERT INTO `country` VALUES (41, 'Costa Rica', 1, 'xx');
INSERT INTO `country` VALUES (42, 'Croatia', 1, 'hr');
INSERT INTO `country` VALUES (43, 'Cuba', 1, 'xx');
INSERT INTO `country` VALUES (44, 'Cyprus', 1, 'xx');
INSERT INTO `country` VALUES (45, 'Czech Republic', 1, 'cz');
INSERT INTO `country` VALUES (46, 'Côte d Ivoire', 1, 'xx');
INSERT INTO `country` VALUES (47, 'Denmark', 1, 'dk');
INSERT INTO `country` VALUES (48, 'Djibouti', 1, 'xx');
INSERT INTO `country` VALUES (49, 'Dominica', 1, 'xx');
INSERT INTO `country` VALUES (50, 'Dominican Republic', 1, 'xx');
INSERT INTO `country` VALUES (51, 'East Timor', 1, 'xx');
INSERT INTO `country` VALUES (52, 'Ecuador', 1, 'xx');
INSERT INTO `country` VALUES (53, 'Egypt', 1, 'eg');
INSERT INTO `country` VALUES (54, 'El Salvador', 1, 'xx');
INSERT INTO `country` VALUES (55, 'Equatorial Guinea', 1, 'xx');
INSERT INTO `country` VALUES (56, 'Eritrea', 1, 'xx');
INSERT INTO `country` VALUES (57, 'Estonia', 1, 'ee');
INSERT INTO `country` VALUES (58, 'Ethiopia', 1, 'xx');
INSERT INTO `country` VALUES (59, 'Fiji', 1, 'xx');
INSERT INTO `country` VALUES (60, 'Finland', 1, 'fi');
INSERT INTO `country` VALUES (61, 'France', 1, 'fr');
INSERT INTO `country` VALUES (62, 'Gabon', 1, 'xx');
INSERT INTO `country` VALUES (63, 'Gambia, The', 1, 'xx');
INSERT INTO `country` VALUES (64, 'Georgia', 1, 'xx');
INSERT INTO `country` VALUES (65, 'Germany', 1, 'de');
INSERT INTO `country` VALUES (66, 'Ghana', 1, 'xx');
INSERT INTO `country` VALUES (67, 'Greece', 1, 'gr');
INSERT INTO `country` VALUES (68, 'Grenada', 1, 'xx');
INSERT INTO `country` VALUES (69, 'Guatemala', 1, 'xx');
INSERT INTO `country` VALUES (70, 'Guinea', 1, 'xx');
INSERT INTO `country` VALUES (71, 'Guinea-Bissau', 1, 'xx');
INSERT INTO `country` VALUES (72, 'Guyana', 1, 'xx');
INSERT INTO `country` VALUES (73, 'Haiti', 1, 'xx');
INSERT INTO `country` VALUES (74, 'Honduras', 1, 'xx');
INSERT INTO `country` VALUES (75, 'Hungary', 1, 'hu');
INSERT INTO `country` VALUES (76, 'Iceland', 1, 'xx');
INSERT INTO `country` VALUES (77, 'India', 1, 'in');
INSERT INTO `country` VALUES (78, 'Indonesia', 1, 'xx');
INSERT INTO `country` VALUES (79, 'Iran', 1, 'xx');
INSERT INTO `country` VALUES (80, 'Iraq', 1, 'ir');
INSERT INTO `country` VALUES (81, 'Ireland', 1, 'ie');
INSERT INTO `country` VALUES (82, 'Israel', 1, 'il');
INSERT INTO `country` VALUES (83, 'Italy', 1, 'it');
INSERT INTO `country` VALUES (84, 'Jamaica', 1, 'xx');
INSERT INTO `country` VALUES (85, 'Japan', 1, 'jp');
INSERT INTO `country` VALUES (86, 'Jordan', 1, 'xx');
INSERT INTO `country` VALUES (87, 'Kazakhstan', 1, 'xx');
INSERT INTO `country` VALUES (88, 'Kenya', 1, 'xx');
INSERT INTO `country` VALUES (89, 'Kiribati', 1, 'xx');
INSERT INTO `country` VALUES (90, 'Korea, North', 1, 'xx');
INSERT INTO `country` VALUES (91, 'Korea, South', 1, 'xx');
INSERT INTO `country` VALUES (92, 'Kuwait', 1, 'xx');
INSERT INTO `country` VALUES (93, 'Kyrgyzstan', 1, 'xx');
INSERT INTO `country` VALUES (94, 'Laos', 1, 'xx');
INSERT INTO `country` VALUES (95, 'Latvia', 1, 'xx');
INSERT INTO `country` VALUES (96, 'Lebanon', 1, 'xx');
INSERT INTO `country` VALUES (97, 'Lesotho', 1, 'xx');
INSERT INTO `country` VALUES (98, 'Liberia', 1, 'xx');
INSERT INTO `country` VALUES (196, 'Herzegovina', 1, 'xx');
INSERT INTO `country` VALUES (100, 'Libya', 1, 'xx');
INSERT INTO `country` VALUES (101, 'Liechtenstein', 1, 'xx');
INSERT INTO `country` VALUES (102, 'Lithuania', 1, 'xx');
INSERT INTO `country` VALUES (103, 'Luxembourg', 1, 'xx');
INSERT INTO `country` VALUES (104, 'Macedonia', 1, 'xx');
INSERT INTO `country` VALUES (105, 'Madagascar', 1, 'xx');
INSERT INTO `country` VALUES (106, 'Malawi', 1, 'xx');
INSERT INTO `country` VALUES (107, 'Malaysia', 1, 'xx');
INSERT INTO `country` VALUES (108, 'Maldives', 1, 'xx');
INSERT INTO `country` VALUES (109, 'Mali', 1, 'xx');
INSERT INTO `country` VALUES (110, 'Malta', 1, 'xx');
INSERT INTO `country` VALUES (111, 'Marshall Islands', 1, 'xx');
INSERT INTO `country` VALUES (112, 'Mauritania', 1, 'xx');
INSERT INTO `country` VALUES (113, 'Mauritius', 1, 'xx');
INSERT INTO `country` VALUES (114, 'Mexico', 1, 'mx');
INSERT INTO `country` VALUES (115, 'Micronesia', 1, 'xx');
INSERT INTO `country` VALUES (116, 'Moldova', 1, 'md');
INSERT INTO `country` VALUES (117, 'Monaco', 1, 'xx');
INSERT INTO `country` VALUES (118, 'Mongolia', 1, 'xx');
INSERT INTO `country` VALUES (119, 'Morocco', 1, 'ma');
INSERT INTO `country` VALUES (120, 'Mozambique', 1, 'xx');
INSERT INTO `country` VALUES (121, 'Myanmar', 1, 'xx');
INSERT INTO `country` VALUES (122, 'Namibia', 1, 'xx');
INSERT INTO `country` VALUES (123, 'Nauru', 1, 'xx');
INSERT INTO `country` VALUES (124, 'Nepal', 1, 'xx');
INSERT INTO `country` VALUES (125, 'Netherlands', 1, 'ne');
INSERT INTO `country` VALUES (126, 'New Zealand', 1, 'nz');
INSERT INTO `country` VALUES (127, 'Nicaragua', 1, 'xx');
INSERT INTO `country` VALUES (128, 'Niger', 1, 'xx');
INSERT INTO `country` VALUES (129, 'Nigeria', 1, 'xx');
INSERT INTO `country` VALUES (130, 'Norway', 1, 'no');
INSERT INTO `country` VALUES (131, 'Oman', 1, 'xx');
INSERT INTO `country` VALUES (132, 'Pakistan', 1, 'pk');
INSERT INTO `country` VALUES (133, 'Palau', 1, 'xx');
INSERT INTO `country` VALUES (134, 'Panama', 1, 'xx');
INSERT INTO `country` VALUES (135, 'Papua New Guinea', 1, 'xx');
INSERT INTO `country` VALUES (136, 'Paraguay', 1, 'xx');
INSERT INTO `country` VALUES (137, 'Peru', 1, 'pe');
INSERT INTO `country` VALUES (138, 'Philippines', 1, 'xx');
INSERT INTO `country` VALUES (139, 'Poland', 1, 'pl');
INSERT INTO `country` VALUES (140, 'Portugal', 1, 'pt');
INSERT INTO `country` VALUES (141, 'Qatar', 1, 'xx');
INSERT INTO `country` VALUES (142, 'Romania', 1, 'ro');
INSERT INTO `country` VALUES (143, 'Russia', 1, 'ru');
INSERT INTO `country` VALUES (144, 'Rwanda', 1, 'xx');
INSERT INTO `country` VALUES (145, 'Saint Kitts and Nevis', 1, 'xx');
INSERT INTO `country` VALUES (146, 'Saint Lucia', 1, 'xx');
INSERT INTO `country` VALUES (147, 'Saint Vincent', 1, 'xx');
INSERT INTO `country` VALUES (148, 'Samoa', 1, 'xx');
INSERT INTO `country` VALUES (149, 'San Marino', 1, 'xx');
INSERT INTO `country` VALUES (151, 'Saudi Arabia', 1, 'xx');
INSERT INTO `country` VALUES (152, 'Senegal', 1, 'xx');
INSERT INTO `country` VALUES (153, 'Serbia', 1, 'xx');
INSERT INTO `country` VALUES (154, 'Seychelles', 1, 'xx');
INSERT INTO `country` VALUES (155, 'Sierra Leone', 1, 'xx');
INSERT INTO `country` VALUES (156, 'Singapore', 1, 'xx');
INSERT INTO `country` VALUES (157, 'Slovakia', 1, 'sk');
INSERT INTO `country` VALUES (158, 'Slovenia', 1, 'xx');
INSERT INTO `country` VALUES (159, 'Solomon Islands', 1, 'xx');
INSERT INTO `country` VALUES (160, 'Somalia', 1, 'xx');
INSERT INTO `country` VALUES (161, 'South Africa', 1, 'xx');
INSERT INTO `country` VALUES (162, 'Spain', 1, 'xx');
INSERT INTO `country` VALUES (163, 'Sri Lanka', 1, 'xx');
INSERT INTO `country` VALUES (164, 'Sudan', 1, 'xx');
INSERT INTO `country` VALUES (165, 'Suriname', 1, 'xx');
INSERT INTO `country` VALUES (166, 'Swaziland', 1, 'xx');
INSERT INTO `country` VALUES (167, 'Sweden', 1, 'se');
INSERT INTO `country` VALUES (168, 'Switzerland', 1, 'ch');
INSERT INTO `country` VALUES (169, 'Syria', 1, 'xx');
INSERT INTO `country` VALUES (170, 'Taiwan', 1, 'xx');
INSERT INTO `country` VALUES (171, 'Tajikistan', 1, 'xx');
INSERT INTO `country` VALUES (172, 'Tanzania', 1, 'xx');
INSERT INTO `country` VALUES (173, 'Thailand', 1, 'th');
INSERT INTO `country` VALUES (174, 'Togo', 1, 'xx');
INSERT INTO `country` VALUES (175, 'Tonga', 1, 'xx');
INSERT INTO `country` VALUES (197, 'Montenegro', 1, 'xx');
INSERT INTO `country` VALUES (177, 'Tunisia', 1, 'xx');
INSERT INTO `country` VALUES (178, 'Turkey', 1, 'tr');
INSERT INTO `country` VALUES (179, 'Turkmenistan', 1, 'xx');
INSERT INTO `country` VALUES (180, 'Tuvalu', 1, 'xx');
INSERT INTO `country` VALUES (181, 'Uganda', 1, 'xx');
INSERT INTO `country` VALUES (182, 'Ukraine', 1, 'xx');
INSERT INTO `country` VALUES (183, 'United Arab Emirates', 1, 'xx');
INSERT INTO `country` VALUES (184, 'United Kingdom', 1, 'uk');
INSERT INTO `country` VALUES (185, 'United States', 1, 'us');
INSERT INTO `country` VALUES (186, 'Uruguay', 1, 'xx');
INSERT INTO `country` VALUES (187, 'Uzbekistan', 1, 'xx');
INSERT INTO `country` VALUES (188, 'Vanuatu', 1, 'xx');
INSERT INTO `country` VALUES (189, 'Vatican City', 1, 'xx');
INSERT INTO `country` VALUES (190, 'Venezuela', 1, 've');
INSERT INTO `country` VALUES (191, 'Vietnam', 1, 'vn');
INSERT INTO `country` VALUES (192, 'Western Sahara', 1, 'xx');
INSERT INTO `country` VALUES (193, 'Yemen', 1, 'xx');
INSERT INTO `country` VALUES (194, 'Zambia', 1, 'xx');
INSERT INTO `country` VALUES (195, 'Zimbabwe', 1, 'xx');
INSERT INTO `country` VALUES (0, 'World', 3, 'xx');
INSERT INTO `country` VALUES (204, 'Europe', 2, 'xx');
INSERT INTO `country` VALUES (205, 'Asia', 2, 'xx');
INSERT INTO `country` VALUES (206, 'Australia & Nz', 2, 'xx');
INSERT INTO `country` VALUES (207, 'Africa', 2, 'xx');
INSERT INTO `country` VALUES (208, 'North America', 2, 'xx');
INSERT INTO `country` VALUES (209, 'South America', 2, 'xx');

[rubenmajor]

You will need to learn some ajax

I've never heard of this program before what is it?

[rubenmajor]

Thanks falcondriver for the country list and the code. I was looking for a solution in dreamweaver or perhaps a simple sql statement. I am not a pro by any means, but I will examine what you sent and see if I can make it work somehow.