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 India, we need to insert a row in the table directory_country_region
INSERT INTO `magento1.6`.`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. Attached is an sql code through which you can easliy add all the indian states in magento.