7 replies to this topic

[cancer10]

Hi

Does anyone here have the DDL + the data for country list along with state for those countries?

If yes, can you please share the same with me?



Thanks

(Database = mySQL)

[WingedPanther]

The DDL will depend a LOT on how you want to store the information, the level of details you want to store, etc. The DDL for Country name, Country code, Primary Language, and Capital would be very different from the DDL for Countries, all their cities, regions, languages, capitals at various levels, etc, etc, etc. Additionally, there are multiple ways to link various tables together, character sets (do you really want all the information about China in Chinese characters?), etc.

Also, some countries do NOT have states, they have provinces, regions, etc.

[cancer10]

While registering on various sites I see that they ask for the user's country, once the user selects the country, the state dropdown populates with the states of that country. Where do you think they bring that information, do they add each states manually for all countries? There must be these kind of data available in .sql or .csv format, no?

[WingedPanther]

That doesn't mean they store it in a standard DDL.

list of countries and provinces - Google Search

[cancer10]

I googled this thing several times before I came here and posted. So I dont think google can help me with this :(

[WingedPanther]

If you get a list of all countries and their provinces, you can convert that into an insert statement for whatever DDL you create.

[cancer10]

But the states shold be mapped with their respective countries.

[WingedPanther]

There are several distinct issues you need to address:
1) getting a list of all countries and each province/state/region/whatever within those countries.
2) storing that information in one or more tables in your database.
3) getting a list of the countries to appear or your site, application, etc
4) when a country is selected, updating the list of states/provinces/whatever (and possibly the label) that are available (using AJAX, JavaScript, or whatever code is appropriate for your website/application).

These amount to
1) getting data
2) structuring data
3) displaying data
4) processing and displaying data

With all that said, I could give you the DDL, and it would do you no good, because it will not produce functionality, or may make your desired functionality inconvenient. If this is part of homework, I could give you a badly non-normalized DDL that is actually properly normalized as part of a larger database that you don't have. I could give you way too much data, resulting in a badly bloated database and poor performing queries. In addition, all of this will be only part of the data you are likely to need. For US states, you are likely to want a list of zip codes or area codes for each state, if you want to do shipping information or contact information. But for other purposes, you wouldn't want that information.