Magento: how to add states to countries in magento.

By default in magento only few countries have a state drop down when a country is selected like United States, in this tutorial we will see how to add state drop down for other countries as well.

The main table where are all the state values of a country are stored is directory_country_region and directory_country_region_name.
The fields of the table ‘directory_country_region’ are
1. region_id: auto-incremented id
2. country_id: the iso code of country (this code is taken from directory_country table)
3. code: unique code to be used for a state
4. default_name: name of the state

The second table is directory_country_region_name with fields
1. locale: website locale or language of the website
2. region_id: it’s a forign key from the previous table
3. name: name of the state for a specific language or locale.

The first table is required to add states to a country, the second table is only required if you want to state displayed in multiple languages.

So to add state to country Australia, we need to insert a row in the table directory_country_region

INSERT INTO  `directory_country_region` (
`region_id` ,
`country_id` ,
`code` ,
`default_name`
)
VALUES (
'0',  'AU',  'ACT',  'Australian Capital Territory');
Here ‘AU’ is the country code for Australia, ‘ACT’ is a user defined unique key and ‘Australian Capital Territory’ is the name of the state. Similarly we can add other states as well as

//start sql //

INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('AU', 'ACT', 'Australian Capital Territory');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('AU', 'NSW', 'New South Wales');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('AU', 'VIC', 'Victoria');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('AU', 'QLD', 'Queensland');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('AU', 'SA', 'South Australia');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('AU', 'WA', 'Western Australia');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('AU', 'TAS', 'Tasmania');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('AU', 'NT', 'Northern Territory');

Add also to add state to country India, we need to insert a row in the table directory_country_region

INSERT INTO  `directory_country_region` (
`region_id` ,
`country_id` ,
`code` ,
`default_name`
)
VALUES (
'0',  'IN',  'IN-DL',  'New Delhi'
);
Here ‘IN’ is the country code for India, ‘IN-DL’ is a user defined unique key and ‘New Delhi’ is the name of the state. Similarly we can add other states as well as

//start sql //

INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-AN', 'Andaman and Nicobar');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Andaman and Nicobar');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-AP', 'Andhra Pradesh');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Andhra Pradesh');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-AR', 'Arunachal Pradesh');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Arunachal Pradesh');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-AS', 'Assam');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Assam');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-BR', 'Bihar');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Bihar');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-CH', 'Chandigarh');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Chandigarh');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-CG', 'Chhattisgarh');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Chhattisgarh');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-DN', 'Dadra and Nagar Haveli');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Dadra and Nagar Haveli');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-DD', 'Daman and Diu');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Daman and Diu');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-DL', 'Delhi');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Delhi');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-GA', 'Goa');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Goa');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-GJ', 'Gujarat');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Gujarat');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-HR', 'Haryana');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Haryana');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-HP', 'Himachal Pradesh');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Himachal Pradesh');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-JK', 'Jammu and Kashmir');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Jammu and Kashmir');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-JH', 'Jharkhand');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Jharkhand');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-KA', 'Karnataka');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Karnataka');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-KL', 'Kerala');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Kerala');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-LD', 'Lakshadweep');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Lakshadweep');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-MP', 'Madhya Pradesh');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Madhya Pradesh');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-MH', 'Maharashtra');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Maharashtra');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-MN', 'Manipur');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Manipur');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-ML', 'Meghalaya');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Meghalaya');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-MZ', 'Mizoram');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Mizoram');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-NL', 'Nagaland');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Nagaland');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-OR', 'Orissa');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Orissa');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-PY', 'Puducherry');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Puducherry');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-PB', 'Punjab');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Punjab');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-RJ', 'Rajasthan');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Rajasthan');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-SK', 'Sikkim');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Sikkim');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-TN', 'Tamil Nadu');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Tamil Nadu');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-TR', 'Tripura');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Tripura');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-UK', 'Uttarakhand');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Uttarakhand');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-UP', 'Uttar Pradesh');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'Uttar Pradesh');
INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES ('IN', 'IN-WB', 'West Bengal');
INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES ('en_US', LAST_INSERT_ID(), 'West Bengal');

//end sql //