Magento: Add a custom State / Province / Region to a Country

January 30th, 2010 Charlie Evans 9 Comments

If you need to add a custom State / Province / Region to a Country in Magento, you’ll need to modify the database manually as there currently isn’t a means to do this from within the Magento admin. Fortunately it’s pretty simple – there are 2 tables involved: directory_country_region and directory_country_region_name.

Adding a record to directory_country_region for a specified country_id will make that new entry show up in the State/Province drop box on the address forms. It will also make that a required field, so you need to make sure you add all the possible options.

You then need to add a corresponding record to directory_country_region_name, using the region_id generated when you inserted into directory_country_region. This entry will make the new region show up when a whole address is displayed on the screen or email, e.g. in an order summary.

Example: Add North and South Island to New Zealand

So let’s say that like me, you live in New Zealand and want to add 2 regions: North Island and South Island. The country id for New Zealand is NZ, the region code is a unique identifier so I’m going with NORTH and at the moment I’m only interested in the en_US locale.

First I will insert North Island into directory_country_region as follows:

INSERT INTO `directory_country_region` 
(`region_id`,`country_id`,`code`,`default_name`) 
VALUES (NULL,'NZ','NORTH','North Island');

Note the NULL entry for the region_id field which will auto_increment. I need to find out this new region_id for my next insert so I can either browse the table manually, or this query will do the trick:

SELECT * FROM `directory_country_region` 
WHERE `country_id`='NZ' AND`code`='NORTH' AND `default_name`='North Island';

In my case, the new region_id is 320, so with that I’ll now insert into directory_country_region_name as follows:

INSERT INTO `directory_country_region_name` 
(`locale`,`region_id`,`name`) 
VALUES ('en_US','320','North Island');

Now I just repeat those steps for South Island and I’m all set.

I’ve created some code below which automates this process, you just need to make sure Magento is properly initialised in your script and modify the variables at the top as necessary.. Enjoy!

// set up array containing regions to be added (region_code => region_name)
$new_regions = array(
	'NORTH' => "North Island",
	'SOUTH' => "South Island",
);
 
// specify country code for new regions
$country_code = 'NZ';
 
// specify locale
$locale = 'en_US';
 
// create our core_write conection object
$connection = Mage::getSingleton('core/resource')->getConnection('core_write');
 
// iterate our new regions
foreach ($new_regions as $region_code => $region_name) {
 
	// insert region 
	$sql = "INSERT INTO `directory_country_region` (`region_id`,`country_id`,`code`,`default_name`) VALUES (NULL,?,?,?)";
	$connection->query($sql,array($country_code,$region_code,$region_name));
 
	// get new region id for next query
	$region_id = $connection->lastInsertId();
 
	// insert region name
	$sql = "INSERT INTO `directory_country_region_name` (`locale`,`region_id`,`name`) VALUES (?,?,?)";
	$connection->query($sql,array($locale,$region_id,$region_name));
}

9 Responses to “Magento: Add a custom State / Province / Region to a Country”

By Gary May 12th, 2010 at 4:16am

Hi Charlies,
Thanks for your post and this is very help!

So if says I want to change the name of New Zealand to “The BIG New Zealand” – can I do that directly in that database? If so, can you please kindly teach me how to do that exactly???
Am very stuck on this at the moment…

Thanks!
- By Charlie Evans Jun 4th, 2010 at 12:50pm
  
  Hey Gary, you’re welcome 🙂
  Country names are not stored in the database, they are part of the Zend_Locale package which has all the various transalations dependent on which locale you are using.. I wouldn’t recommend it, but if you really need to change a country name you’ll need to edit the appropriate files in the directory /lib/Zend/Locale/Data
By Cyberneticist Jun 4th, 2010 at 6:56am

There is a last_insert_id() function in mysql for get last generated auto_increment value

So you can simply use select last_insert_id() instead SELECT * FROM `directory_country_region`
WHERE `country_id`=’NZ’ AND`code`=’NORTH’ AND `default_name`=’North Island’;
- By Charlie Evans Jun 4th, 2010 at 12:17pm
  
  Yep that’ll work too.
  You’ll see I’m using PEARs lastInsertId() method in the PHP code sample..
By Shawn Oct 27th, 2010 at 11:39am

I tried to modified country names: /lib/Zend/Locale/Data
But the names won’t change. The country dropdown list always stays the same, no matter what I did to /lib/Zend/Locale/Data

Please help. Thanks.
- By Lan Oct 27th, 2010 at 12:05pm
  
  it’s because of the cache. Wait for a while.
By Norm Nov 12th, 2010 at 1:00pm

Would anyone happen to know how to set a specific country code as the default country code from Magento Admin Sales Order Form?

For example, when creating an order from the Sales Order Form in the Magento Admin, I would like the country code to default to United States…

Any thoughts are much apprecitated
- By Charlie Evans Nov 13th, 2010 at 8:36am
  
  Hi Norm, you can set the default country for when your customers are entering their addresses at:
  System > Configuration > General > Countries Options > Default Country
  
  Unfortunately this setting only applies to the front end though, so doesn’t help you much..
By sjolzy Jan 11th, 2011 at 2:43am

it will great if there is a model for it.