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?
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);