0

I have an application in CakePHP that lists businesses. I have a business model/controller, as well as a state_list model/controller. However I want to be more detailed so when a user clicks on a State page, it lists all the cities in that particular State that businesses are listed in.
Then when they click a particular city it then shows a page listing all the businesses in that particular city.

How would I be able to do this without a database table of a listing of all cities?

tereško
  • 58,060
  • 25
  • 98
  • 150
xtine
  • 2,599
  • 2
  • 18
  • 15
  • What does your database structure currently look like? How are you determining what city/state a business is in? – deceze Aug 18 '10 at 07:46
  • Business table: ID, name, address, city, state_id, zip, country, url; State table: ID, name, abbr. When I list a State page, I can just grab all businesses that have that State ID. I just don't know how to list by City without doing the same thing. – xtine Aug 18 '10 at 07:49

1 Answers1

2

For this purpose a better database structure would be something along the lines of this:

Table: Location
id
parent_id
name
type

Table: Business
id
location_id
...

Your cities and states should form a tree:

America
   California
      San Francisco
   New York
      ...
Japan
   Tokyo
      ...

For example:

Business ( ..., location_id => 5, ... )
Location ( id => 5, parent_id => 2, name => San Francisco, type => city )
Location ( id => 2, parent_id => ..., name => California, type => state )

That way each business belongs to a city and implicitly to a state and a country as well and everything has a nice id. You also can't make the mistake of having a business that's in New York, California, Japan (which is currently possible).

Given what you have you can only filter cities via a name search:

$listOfBusinessesInState = $this->Business->find('all', array(
    'conditions' => array('Business.state_id' => $state_id),
    'fields'     => array('Business.city'),
    'group'      => array('Business.city')
));
$listOfCitiesInState = Set::extract('/Business/city', $listOfBusinessesInState);
deceze
  • 510,633
  • 85
  • 743
  • 889
  • Thanks, this is exactly the advice I needed. I'll definitely have to re-haul how I handle locations because I'll have to extend to other countries as well. I assume when I add a business it will check if that city in that state/country exists, and if it doesn't I will add it in the table. – xtine Aug 18 '10 at 08:06
  • @xtine Right. You need to be careful about generating duplicates for spelling variants though. So, interface-wise you may want to employ dropdown lists (1. Choose Country, 2. Choose State, ...) or do auto-completion or some such. – deceze Aug 18 '10 at 08:14
  • How would you suggest I go about a database migration script from the current way I have locations to the newer tree based version? And I the thought of having autocomplete dropdowns once I get tree based version popped into my mind as well. :) – xtine Aug 19 '10 at 05:45
  • @xtine Shouldn't be too hard. For each record, take the country name, check if a country of this name already exists. If it doesn't, create it. Use the id. Rinse and repeat for state and city. If you need more help on that, best make a new question about it. – deceze Aug 19 '10 at 07:05