Country/State/City picker in Symfony 1.1

October 26th, 2008 by Carlos Barros

Have you ever seen those forms where you select a country, then it populates a list of states, then you pick a state and it populate a list of cities? I’ve seem many of these and I really like them. In one of my past projects I was asked to add a similar form to the system, using symfony, and I’ll show how I accomplished this, making use of the excellent new symfony 1.1 form system. For those who want to see what it looks like before keep on reading, I put very simple sample online in this link.

Let’s start with the database model (config/schema.yml):

propel:
  _attributes:   { noXsd: false, defaultIdMethod: none, package: lib.model }
 
  cp_users:
    _attributes:     { phpName: User, idMethod: native }
    id:              ~
    name:            { type: varchar(100), required: true }
    country_id:      { type: integer, required: true, foreignTable: cp_countries, foreignReference: id }
    state_id:        { type: integer, required: true, foreignTable: cp_states, foreignReference: id }
    city_id:         { type: integer, required: true, foreignTable: cp_cities, foreignReference: id }
    created_at:      ~
    updated_at:      ~
 
  cp_countries:
    _attributes:     { phpName: Country, idMethod: native }
    id:              ~
    code:            { type: varchar(2), required: true }
    name:            { type: varchar(100), required: true }
 
  cp_states:
    _attributes:     { phpName: State, idMethod: native }
    id:              ~
    country_id:      { type: integer, required: true, foreignTable: cp_countries, foreignReference: id, onDelete: "CASCADE" }
    name:            { type: varchar(100), required: true }
 
  cp_cities:
    _attributes:     { phpName: City, idMethod: native }
    id:              ~
    state_id:        { type: integer, required: true, foreignTable: cp_states, foreignReference: id, onDelete: "CASCADE" }
    name:            { type: varchar(100), required: true }

This is a very simple database model, with user, country, state and city. In this schema, each city is related to a single state and each state is related to a single country. You may ask why I did add all these three elements in the user model, as we can get these info from the city. Honestly, there’s no reason for this, but I left if there just for the sake of simplicity. We can remove state and country and create a getState() and getCountry() methods on User class to get these informations, but I’ll leave it as an exercise for the readers. So, first step is to build this model and then generate SQL script:

$ symfony propel:build-model
$ symfony propel:build-sql
$ symfony propel:insert-sql

Also, for this example I created a module called citypicker, inside frontend application:

$ symfony generate:module frontend citypicker

First step in this sample is to create the list view, I’ll not explain in details about it because it’s just a simple list view with nothing special, I’ll just put the action and template code here:

actions.class.php:

  public function executeIndex($request)
  {
    $this->users = UserPeer::doSelect(new Criteria());
  }
 
  public function executeDel($request)
  {
  	$user = UserPeer::retrieveByPK($request->getParameter("id"));
  	$this->forward404Unless($user);
 
  	$user->delete();
  	return $this->redirect('citypicker/index');
  }

indexSuccess.php:

<table width="50%" align="center" border="1">
	<tr>
		<th>ID</th>
		<th align="left">Name</th>
		<th align="left">Country</th>
		<th align="left">State</th>
		<th align="left">City</th>
	</tr>
	<?php foreach ($users as $user): ?>
		<tr>
			<td align="center"><?=link_to($user->getId(),'citypicker/edit?id='.$user->getId()) ?> - <?=link_to('del','citypicker/del?id='.$user->getId()) ?></td>
			<td><?=esc_entities($user->getName()) ?></td>
			<td><?=$user->getCountry() ?></td>
			<td><?=$user->getState() ?></td>
			<td><?=$user->getCity() ?></td>
		</tr>
	<?php endforeach; ?>
</table>
<p align="center"><?=button_to('New user','citypicker/edit') ?></p>

Now we need to create the edit view, where the citipicker will be shown. For this, we need to create the form that will handle user input. In this example I didn’t use propel:build-forms command, instead I created the form from scratch (that’s one point to improve). The resulting code is as follows:

lib/form/CPUserForm.class.php:

class CPUserForm extends sfFormPropel
{
  public function configure()
  {
  	// build state criteria
  	$stateC = new Criteria();
  	$stateC->add(StatePeer::COUNTRY_ID,$this->getObject()->getCountryId());
 
  	// build city criteria
   	$cityC = new Criteria();
  	$cityC->add(CityPeer::STATE_ID,$this->getObject()->getStateId());
 
    $this->setWidgets(array(
      'name'   			=> new sfWidgetFormInput(),
      'country_id'		=> new sfWidgetFormPropelSelect(array('model'=>'Country','add_empty'=>'-- Country --','order_by'=>array('Name','asc'))),
      'state_id'		=> new sfWidgetFormPropelSelect(array('model'=>'State','add_empty'=>'-- State/Province --','order_by'=>array('Name','asc'),'criteria'=>$stateC)),
      'city_id'			=> new sfWidgetFormPropelSelect(array('model'=>'City','add_empty'=>'-- City --','order_by'=>array('Name','asc'),'criteria'=>$cityC)),
      'id'				=> new sfWidgetFormInputHidden(),
    ));
 
    $this->setValidators(array(
    	'name'			=> new sfValidatorString(
    							array(
    								'trim'		=> true,
    								'required'	=> true,
    							),array(
    								'required'	=> '- Please enter name',
    							)
    					),
    	'country_id'	=> new sfValidatorPropelChoice(
    							array(
    								'model'		=> 'Country',
    								'column'	=> 'id',
    							),array(
    								'required'	=> '- Please choose country',
    								'invalid'	=> '- Invalid country',
    							)
    					),
    	'state_id'		=> new sfValidatorPropelChoice(
    							array(
    								'model'		=> 'State',
    								'column'	=> 'id',
    								'criteria'	=> clone $stateC,
    							),array(
    								'required'	=> '- Please choose state',
    								'invalid'	=> '- Invalid state',
    							)
    					),
    	'city_id'		=> new sfValidatorPropelChoice(
    							array(
    								'model'		=> 'City',
    								'column'	=> 'id',
    								'criteria'	=> clone $cityC,
    							),array(
    								'required'	=> '- Please choose city',
    								'invalid'	=> '- Invalid city',
    							)
    					),
    	'id'			=> new sfValidatorNumber(array('required'=>false)),
    ));
 
	$this->widgetSchema->setLabels(array(
		'name'	=> 'Name',
	));
 
	$this->widgetSchema->setNameFormat('user[%s]');
    $this->errorSchema = new sfValidatorErrorSchema($this->validatorSchema);
  }
 
  public function getModelName()
  {
  	return 'User';
  }
}

This form is not much different from a common form, using sfWidgetFormPropelSelect to show a list of available objects, except by the new option I added, criteria. As can be seen at the top of the code, I build two criterias that are used to filter state and city, based on current selection. This will make the state select only display states belonging to selected country, and cities belonging to selected state. Also, if no country is selected, state and city list will be empty.
Another important step that must be done in order to use sfWidgetFormPropelSelect is to create the magic method __toString to Country, State and City objects. Just add the following code to ALL three classes:

lib/model/Country.class.php, lib/model/State.class.php, lib/model/City.class.php:

	public function __toString()
	{
		return $this->getName();
	}

The template needs a special attention as well. As will we use AJAX to update the select boxes without reloading the page, we need to split the edit template in two files, the main file and a partial file that will contain the select boxes and the ajax calls:

editSuccess.php:

<form method="post" action="<?=url_for('citypicker/edit?id='.$form->getObject()->getId()) ?>">
  <?=$form["id"]->render() ?>
  <table>
  	<?php if ($errors): ?>
    <tr>
    	<td colspan="2">
    		<ul>
    			<?php foreach ($errors as $error): ?>
					<li><?=$error ?></li>
				<?php endforeach; ?>
    		</ul>
    	</td>
    </tr>
	<?php endif; ?>
    <tr>
      <th><?=$form["name"]->renderLabel() ?></th>
      <td><?=$form["name"]->render() ?></td>
    </tr>
    <tr>
      <th>Address</th>
      <td>
        <div id="address">
        	<?=include_partial('address_edit',array('form'=>$form)) ?>
        </div>
      </td>
    </tr>
    <tr>
      <td colspan="2">
        <input type="submit" name="submit" value="save"/>
        <?=button_to('cancel','citypicker/index') ?>
      </td>
    </tr>
  </table>
</form>

_address_edit.php:

<?php use_helper('Javascript') ?>
<?=$form["country_id"]->render(array(
	'onchange'	=> '$(this.form).request({
						parameters: {refresh: "Y" },
						onComplete: function (r) { $("address").update(r.responseText) }
					});'
)) ?>
<?=$form["state_id"]->render(array(
	'onchange'	=> '$(this.form).request({
						parameters: {refresh: "Y" },
						onComplete: function (r) { $("address").update(r.responseText) }
					});'
)) ?>
<?=$form["city_id"]->render()?>

This code submits the form every time the user changes either country or state boxes. Note that it will append an extra parameter to the form, called refresh, in order to tell the action that it’s only updating the boxes, and not actually submitting the form. Also, when the call is finished, it will update the #address (div container defined in editSuccess.php template file) element with the response received.

Now, the only piece missing to make this work is the actual edit action, that will handle both form submission and boxes update.

actions.class.php:

  public function executeEdit($request)
  {
  	if($request->getParameter('id')) $user = UserPeer::retrieveByPK($request->getParameter("id"));
  	else $user = new User();
  	$this->forward404Unless($user);
 
  	$this->errors = array();
  	if($request->isMethod('post'))
  	{
  		// pre-populate country, state and city in order to filter select boxes
  		$data = $request->getParameter('user');
  		$user->setCountryId(@$data['country_id']);
  		$user->setStateId(@$data['state_id']);
  		$user->setCityId(@$data['city_id']);
 
  		// check validity
  		if(!$user->getCity() || ($user->getCity()->getStateId() != $user->getStateId())) $user->setCityId(0);
  		if(!$user->getState() || ($user->getState()->getCountryId() != $user->getCountryId())) $user->setStateId(0);
  		if(!$user->getCountry()) $user->setCountryId(0);
 
  		$this->form = new CPUserForm($user);
  		$this->form->bind($request->getParameter('user'));
  		if($request->getParameter('refresh') != "Y")
  		{
  			if($this->form->isValid())
  			{
  				$this->form->save();
  				return $this->redirect('citypicker/index');
  			}
 
		  	// get errors into array
		  	foreach($this->form->getFormFieldSchema()->getError() as $e)
		  		$this->errors[] = $e;
  		}
  		else
  		{
  			// render city/state/country
  			return $this->renderPartial('address_edit',array('form'=>$this->form));
  		}
  	}
  	else 
  	{
  		$this->form = new CPUserForm($user);
  	}
  }

Again, this action is not much different from a common edit action, but there are some extra code to handle the box update. At first, we extract selected country, state and city from the parameters array and populate the user object with these values. This step is needed in order to make the criterias (defined in the form) to work, as it fetches data from the object. Also, we validate these data in order to ensure that selected state belongs to selected country, and that selected city belongs to selected state (well, I guess this can be removed as the criterias will filter it, but I’ll leave it as I didnt test – another point to improve). Another change is the check for the refresh parameter. If it’s set to Y, we skip form validation and just render the _address_edit partial. This partial will be received by the ajax call and will update the form with new select boxes values. Another small detail on this sample is the error handling. After validation, if there’s any error, I get the list of error messages and create an array with these values, then send it to the template, that in turn will render a unordered list with the messages. I did this because this form has a special design and putting the error messages together with the fields would mess the box design.

Well, that’s it, now you have the form working. There are some things that can be done in order to improve user experience. Actually experience told me that it MUST be done. One thing is to disable ALL boxes as soon as the user changes it’s value and only re-enabling after update is finished. Also, you can add an ajax indicator somewhere to tell the user that it’s still working. If you don’t do this, it’s most likely that users will get confused with the boxes (and they WILL, trust me, hehe).

This small tutorial maybe looks a bit confuse, with lots of code with not much explanation, but I hope this can be useful for you. It would be nice to hear feedback from the readers, specially comments about how to improve the form (I’m not really an symfony expert).

Share this post...
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • Ma.gnolia
  • MySpace
  • Rec6
  • Reddit
  • StumbleUpon
  • Technorati

In symfony | 38 Comments

Comments

      FabienNo Gravatar in October 27th, 2008 at 5:36

    • Thanks a lot. Very useful for me. I don’t see a lot of tutorial about using javascript and new form system.
      Thank you again.

      christianNo Gravatar in October 27th, 2008 at 5:48

    • nice piece of code, thank you very much!

      i’m just curious where you got all the data from (country->province->city) to populate your database?

      christianNo Gravatar in October 27th, 2008 at 10:51

    • thanks, been searching for such a database recently, but maxmind did not come to my mind..

      thanks, for the DB and the tutorial :)

      FredlabNo Gravatar in November 6th, 2008 at 17:08

    • Very usefull tutoral. Please continue to help us discover and use symfony the right way.

      Frédéric

      halferNo Gravatar in November 7th, 2008 at 12:28

    • Hi, thanks for the info about the cities database. I’ll be needing that for my current project!

      dudeNo Gravatar in November 8th, 2008 at 16:23

    • You shouldn’t use <?= in your code. Always use <?php echo. Yeah, it sucks, but it’s the only valid way…

      SergioNo Gravatar in December 5th, 2008 at 1:06

    • My friend:

      I am trying to implement a drop-down selection for the country, state and city, using the database you informed and php with mysql.
      Presently, I made a small program in php to import the data to one database. The problem is that this database got very large, and it’s a little bit slow to access.
      Can you help me just telling what was the format of the database you are using for your example (how mani=y databases and the structure of each of them)?
      Thank you ery much.

      Sergio

      Carlos BarrosNo Gravatar in December 5th, 2008 at 9:07

    • Hi Sergio.

      About my database structure, I’m use the structure described at the top of article. I have three tables: countries, states and cities.

      mysql> describe cp_countries;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment | 
| code  | varchar(2)   | NO   |     | NULL    |                | 
| name  | varchar(100) | NO   |     | NULL    |                | 
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
 
mysql> describe cp_states;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment | 
| country_id | int(11)      | NO   | MUL | NULL    |                | 
| name       | varchar(100) | NO   |     | NULL    |                | 
+------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
 
mysql> describe cp_cities;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment | 
| state_id | int(11)      | NO   | MUL | NULL    |                | 
| name     | varchar(100) | NO   |     | NULL    |                | 
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

      Country and state tables are not that big, but city table is really HUGE! That’s the main reason for implementing this using ajax, so we just fetch cities on demand.

      Carlos

      SergioNo Gravatar in December 5th, 2008 at 10:05

    • Thank you very much for your help.
      I will try to implement it.
      I already made a small program in php to export to a db query, just the fields I need to use from MaxMind’s database: the country code, the city name and the region code.
      Again, thank you very much.

      Sergio

      SergioNo Gravatar in December 6th, 2008 at 23:22

    • My friend:
      I downlaoded the database file from Maxmind (the file at http://www.maxmind.com/download/worldcities/worldcitiespop.txt.gz).
      I think that this database has some problems.
      By example, The city where I live is called "Florianópolis", but, in the file downloaded from Maxmind, there are 3 Florianópolis: one at the state code 01, one at 04 and one at state 26 – this is the correct.
      There are also several others errors 9cities that doesn’t exist as the city: Fôlego do Sérgio

      In you example, they are correct.
      Is this the file you used for your aplication (worldcitiespop.txt)?

      Thank you again.

      Sérgio

      br,florianopolis,Florianópolis,01,,-7.7,-72.65
      br,florianopolis,Florianópolis,04,,-0.35,-63.8833333
      br,florianopolis,Florianópolis,26,412731,-27.5833333,-48.5666667
      .
      .
      br,folego do sergio,Fôlego do Sérgio,05,,-14.1,-44.4

      SergioNo Gravatar in December 7th, 2008 at 19:17

    • Hi Carlos:

      I implemented the search app using the database.
      You can see it at http://www.vivaemfloripa.com.br (click on the link "Pesquisa Avançada").
      The only problem I have now, is the accents that are missing (the database’s tables are created using "TYPE=MyISAM" I don’t know if this is the cause).
      I made 3 programs in php to generate the 3 tables from the files of maxmind.
      Now, I just have to integrate this in the backend of the hot property (Real Estate aplication for Joomla) and make it work with the search.
      Here, at Santa Catarina, people is trying to retart their lifes (in the cities that were very badly affected by the flooding).
      Fortunately, where I live (São José – Florianópolis), we are ok.

      Sérgio

      Carlos BarrosNo Gravatar in December 7th, 2008 at 19:49

    • Hi Sergio.. I saw the search, very nice… About accents, it’s probably an encoding problem, but I think the problem is in your PHP to import the data into DB.. You site is entirely in UTF-8, so I tried to switch to ISO-8859-1 but the accents were still messed. So, I guess your importer are messing it up. As far as I remember, maxmind fils are all in UTF-8… check your script and your database settings if they are not trying to convert from UTF-8 to something else… Btw, did u try to browse your database using something like phpmyadmin, or mysql cli?? If not, try to inspect the data and see if accents are messed in DB.. If you want (and can), show me your importer script so I can try to find something….

      About SC, that was terrible.. really sad.. :( .. I just prey for this not happen again, and for affected people to get their lives back…

      SergioNo Gravatar in December 7th, 2008 at 20:04

    • My friend:

      In the phpmyadmin, all accents are ok.
      The code of the importer for the cities (in the home-page) is:
      [code]
      <?php
      include "auth.php";
      $countryId = $_GET['country'];
      $stateId = $_GET['state'];

      $conn = mysql_pconnect($hostname, $username, $password) or die("DBase Error!");
      mysql_select_db($database, $conn);

      $query = "SELECT * from cp_cities where Country_Code = '$countryId' AND State_Code = $stateId";
      $result = mysql_query($query);

      $content .= '';
      $content .= 'Selecione a Cidade';
      while($row1 = mysql_fetch_assoc($result))
      {
      $content .= '' . $row1['City_Name'] . '';
      }
      $content .= '';

      echo $content;

      ?>
      [/code]
      This file (and all the others) are saved in utf-8 format.
      The code for the exporter from the maximd’s files is (for the cities):
      [code]
      1 )
      {
      $content = "INSERT INTO `cp_cities` (`Country_Code`, `State_Code`, `City_Name`, `City_Lat`, `City_Lon`) VALUES ('$C_code', '$S_Region', '$City', '$Lat', '$Long');\r\n";
      fwrite($handlew, $content) or die("1 Could not write to file: " . $filename . "\n");
      }

      $row++;
      //if ($row > 100000) exit();
      }
      fclose($handle);
      fclose($handlew);

      ?>
      [/code]

      Sergio

      Carlos BarrosNo Gravatar in December 7th, 2008 at 20:25

    • ok, i think I found where’s the problem… Open this page:

      http://www.vivaemfloripa.com.br/includes/js/findCity.php?country=BR&state=26

      You will see that accents are OK.. Also, if you open it in FF, and go to View->Character Encoding you will see that encoding is set to ISO-8859-1.. So, this mean that your database is all in ISO-8859-1…. One way to solve this problem is changing

      echo $content;

      to

      echo utf8_encode($content);

      Other way is to use utf8_encode in your importer, but as you already have it working, maybe you can just try adding this to findCity.php and findState.php..

      Dont worry about the [code] thing :) .. If you want to use again, just use:

      <pre>

      code here

      </pre>

      Calros

      SergioNo Gravatar in December 7th, 2008 at 20:32

    • My friend:

      Thank you very much for your help.
      I’ll make the mods you indicated.

      If you want, I can send the codes I used to you so you can publish in your blog (the codes are free).
      As soon as I have them working, I’ll publish also in my blog.
      Just send me an e-mail with an e-mail address I can use to send them to you, or, if prefer, I can ut them here, but there are 6 files.

      Sergio

      Carlos BarrosNo Gravatar in December 7th, 2008 at 20:45

    • Hi Sergio, u’re welcome….

      About the files, send the link of your blog post (when u have it), so I can put a reference to it in this article…

      Carlos

      KevinNo Gravatar in January 24th, 2009 at 12:16

    • This is great. I have been trying to get the database working based on the data from the maxmind site without success. Would you be willing to make your sql file available?

      Kevin

      Organic Search GuyNo Gravatar in February 26th, 2009 at 9:07

    • thanks for that list, it really saved me hours of google searhcing time. thanks for that.

      JohnNo Gravatar in April 17th, 2009 at 23:45

    • Hi, Great piece of code! Had a play with it and it all works wonderfully on it’s own. I’m wondering though: any ideas how I can I adapt this to get this working with the admin generator in symfony 1.2?
      Hope someone can point me in the right direction!
      John

      WillNo Gravatar in May 4th, 2009 at 6:23

    • Hi

      I always get a required error. Any idea why this is or what I should do to check more. All the fields are filled and I have been checking all the logs for hours. Any ideas would be greatly appreciated. Perhaps someone has had a similar problem. Thankyou

      Will

      Carlos BarrosNo Gravatar in August 11th, 2009 at 9:29

    • Hi Rafael.. Actually it’s not a Firefox problem (I always develop using firefox, so it’s most likely to not work on IE :)
      The problem was that I moved the domain and forgot to fix the sf/ directory link, so it was not finding the prototype.js library. Thanks for pointing this out!

      Carlos

      huu2uanNo Gravatar in August 16th, 2009 at 14:04

    • i have some tables:
      bsc: id, name
      cell: id, bsc_id, name
      group: id, name
      cell_group: cell_id, group_id

      when i create a new group, i use sfWidgetFormDoctrineChoiceMany with option "renderer_class"="sfWidgetFormSelectDoubleList" to have a double list.
      i want to add a bsc select tag and when i change value of bsc tag, cell double list value change too.
      i don’t know how to do it.

      hope you (and someone) can help :)
      thanks

      Gary ArcticNo Gravatar in February 25th, 2010 at 3:02

    • This page of your site ranks really well in google.au, just thought i’de post and say greetings from down under

      Cheap DVD BoxsetsNo Gravatar in February 25th, 2010 at 20:34

    • You know, I’ve REALLY found your posts really rather interesting. I can’t wait to read your next one, this has really struck a cord with me.

      William I.No Gravatar in March 7th, 2010 at 22:34

    • Good work! I would like to commend you for your very good build up this site. I hope you keep on coming up with advantageous posts as great as this one.

Leave a Reply

Powered by WP Hashcash