Need some theory

[forumnz]

I'm creating a site that includes a search. Now, I am familiar with searches etc.

 

The site has places people can visit in different areas.

There are three dropdown boxes. The first is Regions. The user clicks on say region A, which loads all the cities in dropdown box 2 within that region. User selects city A (example) which loads all suburbs in that city.

I have all that fine and worked out, but how would I select the right stuff from the database depending on the area?

 

Each area has a value (like 233). How would I get the script to select All Suburbs within a city?

 

I dont need code, just some theory.

 

Thanks a lot,

Sam.

[psychowolvesbane]

Well first off you would need a relational database with at least 3 different tables, one for each different Regions, Cities, and Suburbs. Then you would need a table between each (Say between Regions and Cities) to determine which City goes with what Region, and do the same with the Suburbs for each City. That will get you a decent normalized Database to play with. 

[forumnz]

Is there an easier way? I have thousands of areas! ??? ??? ???

 

Thanks,

Sam.

[Barand]

They aren't many-to-many relationships, so you don't need those intermediate tables that psychowolvesbane suggested

 

Data schema

[pre]

region        city          suburb                place

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

regionID --+  cityID  --+  suburbID  ----+      placeID

region    |  city      |  suburb        |      placename

          +- regionID  +-  cityID        +---  suburbID

[/pre]         

To find all places in $city

SELECT c.city, s.suburb, p.placename
FROM place p
    INNER JOIN suburb s ON s.suburbID = p.suburbID
    INNER JOIN city c ON c.cityID = s.cityID
WHERE cityID = '$city'