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

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”

  1. 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!

    • 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

  2. 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’;

    • Yep that’ll work too.
      You’ll see I’m using PEARs lastInsertId() method in the PHP code sample..

  3. 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.

    • it’s because of the cache. Wait for a while.

  4. 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

    • 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..

  5. it will great if there is a model for it.