By default Magento stores the region/state information only for some countries – Austria, Canada, Estonia, Finland, France, Germany, Latvia, Lithuania, Romania, Spain, Switzerland, United States. So on the checkout and registration pages Magento will display the state/region dropdown for only these countries and for other counties region/state will be shown as a textbox.

If you developing an e-commerce website with target audience as your country only then you will definitely want to show a nice region/state dropdown on the frontend of Magento for your customers.

If region/state data is not available for your country in Magento then the solution is to add this information to the tables in your database. Magento will pick up this data on its own and populate a drop down list.

The tables are `directory_country_region` and `directory_country_region_name`.  The table `directory_country_region` holds the region – country mapping and `directory_country_region_name` holds the translation for region name based on locale.

We were developing an e-commerce website for Indian market, so we prepared the following queries to populate the states data in tables.

INSERT INTO `directory_country_region` (`country_id`, `code`, `default_name`) VALUES
('IN', 'IN-AN', 'Andaman & Nicobar Islands'),
('IN', 'IN-AP', 'Andhra Pradesh'),
('IN', 'IN-AR', 'Arunachal Pradesh'),
('IN', 'IN-AS', 'Assam'),
('IN', 'IN-BR', 'Bihar'),
('IN', 'IN-CH', 'Chandigarh'),
('IN', 'IN-CT', 'Chattisgarh'),
('IN', 'IN-DN', 'Dadra & Nagar Haveli'),
('IN', 'IN-DD', 'Daman & Diu'),
('IN', 'IN-DL', 'Delhi'),
('IN', 'IN-GA', 'Goa'),
('IN', 'IN-GJ', 'Gujrat'),
('IN', 'IN-HR', 'Haryana'),
('IN', 'IN-HP', 'Himachal Pradesh'),
('IN', 'IN-JK', 'Jammu & Kashmir'),
('IN', 'IN-JH', 'Jharkhand'),
('IN', 'IN-KA', 'Karnataka'),
('IN', 'IN-KL', 'Kerala'),
('IN', 'IN-LD', 'Lakshwdeep'),
('IN', 'IN-MP', 'Madhya Pradesh'),
('IN', 'IN-MH', 'Maharashtra'),
('IN', 'IN-MN', 'Manipur'),
('IN', 'IN-ML', 'Meghalaya'),
('IN', 'IN-MZ', 'Mizoram'),
('IN', 'IN-NL', 'Nagaland'),
('IN', 'IN-OR', 'Orissa'),
('IN', 'IN-PY', 'Pondicherry'),
('IN', 'IN-PB', 'Punjab'),
('IN', 'IN-RJ', 'Rajasthan'),
('IN', 'IN-SK', 'Sikkim'),
('IN', 'IN-TN', 'Tamilnadu'),
('IN', 'IN-TR', 'Tripura'),
('IN', 'IN-UP', 'Uttar Pradesh'),
('IN', 'IN-UT', 'Uttarakhand'),
('IN', 'IN-WB', 'West Bengal');

INSERT INTO `directory_country_region_name` (`locale` ,`region_id` ,`name` )
SELECT 'en_US', tmp.region_id, tmp.default_name FROM `directory_country_region`
AS tmp WHERE tmp.country_id='IN';

After running these queries in database, Magento automatically displayed the states option dropdown on the address and checkout pages when the country was selected as “INDIA”.