creating a database

[Benjamin_4]

Hello guys i want create a table which willl consist of Regions and Location
therefore when i take a region it should give me a list of locations under that region. i hope my question is clear enough THank you

[Reverend Jim]

A few problems here

1. you haven't said what DBMS you are using
2. you haven't said what programming language you are using
3. you apparently haven't put any effort into this yourself
4. you expect us to do this for you with almost zero input

[Benjamin_4]

Am using mysql database
the language am using is Java

Region                     Locations
-------                  -------------
Greater Accra            Labone,East Legon,Airport
Ashanti Region           Kumasi,Amakom,Bantama

i want when i select greater accra, only the locations under greater accra should show. And when Ashanti region is chosen only the locations under Ashanti region should show. i just need help on how to go about it please.

[RomelynCastillo]

If I were you I would create three tables such as REGIONS, LOCATIONS, and PLACES. The REGIONS table contains 1 column named RegList where in all the regions are listed. In the LOCATIONS table there would be two columns named LocList and RegList wherein the LocList contains all the list of locations or cities and acts as the primary key while in the RegList you can find the region where that location is in. In the Places, There will be two three columns such as PlacesList, LocList, RegIn. PlacesList is the list of all the locations while the LocIn and RegIn are the location and region where a specific place is in. It is in your program where you can connect and redirect one table to another (an input will produce an output that will serve as an input to another matter).

Create database Tour;
Use Tour;

Create table REGIONS(
RegList varchar,
PRIMARY KEY (RegList)
)

Create table LOCATIONS(
LocList varchar,
RegList varchar,
PRIMARY KEY (LocList)
INDEX (RegList)
)

Create table LOCATIONS(
PlacesList varchar,
LocList varchar,
RegIn2 varchar,
PRIMARY KEY (PlaceList)
INDEX (LocList)
)

[balaji9124]

If your Mysql platform is Linux
then first of all you may need to get into your host with its respective username and pass as follows:
open the terminal by pressing ctrl+alt+t and type
mysql -h localhost -u username -p then press on enter
now it will ask for the password and enter the password
Eg: mysql -h localhost -u root -p

Now you could create a database using "create database Tour;" query
and "use Tour" query.

Create table REGIONS(RegList varchar(30),PRIMARY KEY (RegList));

Create table LOCATIONS(LocList varchar(30),RegList varchar(30),PRIMARY KEY (LocList) INDEX (RegList));

Create table LOCATIONS(PlacesList varchar(30),LocList varchar(30),RegIn2 varchar(30),PRIMARY KEY (PlaceList)INDEX (LocList));

[chocolatte.lavista_1]

example :-

CREATE DATABASE Tour;
USE Tour;

CREATE TABLE Tour2
(Region VARCHAR(50) NOT NULL,
Locations VARCHAR(100),
PRIMARY KEY (Region));

INSERT INTO Tour2
(Region, Locations)
VALUES('Greater Accra', 'Labone,East Legon,Airport');

INSERT INTO Tour2
VALUES('Ashanti Region', 'Kumasi,Amakom,Bantama');

[diafol]

I recently covered something like this in a code snippet:

http://www.daniweb.com/web-development/php/code/475238/linked-dropdowns-select-fields-for-static-ish-data

with a suggested table schema

Have a look at GROUP_CONCAT for creating a list by group, as in

SELECT states.state, GROUP_CONCAT(cities.city) FROM states INNER JOIN cities ON cities.state_id = states.state_id GROUP BY states.state_id