Author Topic: Complete List of States / Provinces / Regions  (Read 417 times)

bryanearl

  • Beginner
  • *
  • Posts: 25
  • Web Design and SEO / SMO
    • Bryan Earl Web Design
Complete List of States / Provinces / Regions
« on: May 15, 2018, 23:31:12 pm »
I am working on a store that sells to countries all over the world. I have noticed that the "state / province / region" drop-down is empty for most countries.

It's critical but there is no way that I can manually find and enter all of these myself. Is there a plugin or list or some way I can import these?

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 2732
  • VirtueMart Version: 3.2.14
Re: Complete List of States / Provinces / Regions ?
« Reply #1 on: May 16, 2018, 09:21:23 am »
Quote
It's critical but there is no way that I can manually find and enter all of these myself.

http://forum.virtuemart.net/index.php?topic=79799.0
regards
A

Joomla 3.8.7
php 5.6 + php 7

jenkinhill

  • UK Web Developer & Consultant
  • Global Moderator
  • Super Hero
  • *
  • Posts: 26809
  • Always on vacation
    • Jenkin Hill Internet
Kelvyn

Jenkin Hill Internet,
Lowestoft, Suffolk, UK

Unsolicited PMs/emails will be ignored.

Please mention your VirtueMart, Joomla and PHP versions when asking a question in this forum

Currently using VM3.2.14.9808 on Joomla 3.8.8 PHP 7.0.30

Testing VM3.2.15.9840 on J3.8.8

bryanearl

  • Beginner
  • *
  • Posts: 25
  • Web Design and SEO / SMO
    • Bryan Earl Web Design
Re: Complete List of States / Provinces / Regions ?
« Reply #3 on: May 16, 2018, 15:42:25 pm »
Quote
It's critical but there is no way that I can manually find and enter all of these myself.

http://forum.virtuemart.net/index.php?topic=79799.0

Joomla 3.8.7 and VirtueMart 3.2.14

bryanearl

  • Beginner
  • *
  • Posts: 25
  • Web Design and SEO / SMO
    • Bryan Earl Web Design
Re: Complete List of States / Provinces / Regions ?
« Reply #4 on: May 18, 2018, 18:09:28 pm »
I still find it hard to believe that nobody has created an SQL import with a complete countries and states list! I realize that the official "standpoint" from VM developers is to do it manually, but that would take absolutely forever. Anyone at all?

jenkinhill

  • UK Web Developer & Consultant
  • Global Moderator
  • Super Hero
  • *
  • Posts: 26809
  • Always on vacation
    • Jenkin Hill Internet
Re: Complete List of States / Provinces / Regions ?
« Reply #5 on: May 18, 2018, 18:22:11 pm »
I realize that the official "standpoint" from VM developers is to do it manually, but that would take absolutely forever. Anyone at all?

So it would take a long time (although maybe not for ever) to enter the states of all countries in the world - and even to write sql to do it. Maybe that's why nobody has voluntered to do it.  And who would maintain the list? 

People who sell to more than one country can add the states where required - as far as I can remember files with some of the states were submitted to the project by users such as for Armenis, and may well be already added.
Kelvyn

Jenkin Hill Internet,
Lowestoft, Suffolk, UK

Unsolicited PMs/emails will be ignored.

Please mention your VirtueMart, Joomla and PHP versions when asking a question in this forum

Currently using VM3.2.14.9808 on Joomla 3.8.8 PHP 7.0.30

Testing VM3.2.15.9840 on J3.8.8

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 2732
  • VirtueMart Version: 3.2.14
Re: Complete List of States / Provinces / Regions ?
« Reply #6 on: May 18, 2018, 19:06:03 pm »
Quote
I still find it hard to believe that nobody has created an SQL import with a complete countries and states list!

You could be the one to do it and give it back to the community to use for free  :)
regards
A

Joomla 3.8.7
php 5.6 + php 7

jflash

  • 3rd party VirtueMart Developer
  • Beginner
  • *
  • Posts: 27
Re: Complete List of States / Provinces / Regions ?
« Reply #7 on: May 18, 2018, 21:06:00 pm »
Hello!
My friend wrote me script for Estonian states, in attachment it is txt file, just rename it to php, put to root of Joomla and execute with browser :)

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 2974
  • Joomla & Virtuemart addon developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3.0.x.y
Re: Complete List of States / Provinces / Regions ?
« Reply #8 on: May 18, 2018, 21:13:18 pm »
I still find it hard to believe that nobody has created an SQL import with a complete countries and states list!
First you have to find a full list of state from all the world.
Then you have to maintain it.
I have for eg do for a customer an autofill list of cities from france using zipcode.
The database is more then 2Go!
So you want state and a day someone want all cities in the world or phone numbers....
For eg here a list, but i mean it's not complete (many cities are missing) and not maintained  https://github.com/hiiamrohit/Countries-States-Cities-database

bryanearl

  • Beginner
  • *
  • Posts: 25
  • Web Design and SEO / SMO
    • Bryan Earl Web Design
Re: Complete List of States / Provinces / Regions ?
« Reply #9 on: May 19, 2018, 04:43:17 am »
I went ahead and created a complete SQL import for all states for all countries in the VM database that aren't loaded by default in the install. It took me a while, but I located and used the latest country and state data from the United Nations.

I tested it and it works using phpmyadmin to import! All states from every country are now loaded into my VM installation  ;D

Maybe you guys can take a look and provide me with some feedback?

(ATTACHMENT AS TXT FILE)

* Also, note that I did not use "real" 2-code or 3-code values. I just put in random data to fill those fields.


Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 2974
  • Joomla & Virtuemart addon developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3.0.x.y
Re: Complete List of States / Provinces / Regions ?
« Reply #10 on: May 19, 2018, 09:19:59 am »
bryanearl, have you compared your states with the link i sended before?
The number of state in the list is 4120

bryanearl

  • Beginner
  • *
  • Posts: 25
  • Web Design and SEO / SMO
    • Bryan Earl Web Design
Re: Complete List of States / Provinces / Regions ?
« Reply #11 on: May 19, 2018, 15:06:26 pm »
bryanearl, have you compared your states with the link i sended before?
The number of state in the list is 4120

It should be very close. My list does not include all of the states that are already included with the default VM install. Also, for countries that don't necessarily have multiple states, I added the country name itself as a state so that the field can be required (something can always be chosen regardless of the country selected).

bryanearl

  • Beginner
  • *
  • Posts: 25
  • Web Design and SEO / SMO
    • Bryan Earl Web Design
Re: Complete List of States / Provinces / Regions ?
« Reply #12 on: May 19, 2018, 20:32:43 pm »
I successfully finished and tested my complete world-states import today and it works great! Note that I do not offer ANY guarantees, but I am providing this for others for free.

I updated my post above with the newest, working TXT file. Here are a couple notes:

   1. This is meant for those that have NOT entered in any additional states, other than what is automatically brought in by the latest VM install.

   2. I did not use actual 2-code or 3-code values for each state. It was not needed for what I am doing, so the 2-code and 3-code data is just generic.

   3. Before importing, you will need to do a "find and replace" in the text file to add your own database prefix to the "virtuemart_states" table identifier.

jjk

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3284
  • using Matomo instead of Google Analytics
Re: Complete List of States / Provinces / Regions
« Reply #13 on: May 19, 2018, 23:20:08 pm »
Just a note - for worldwide shipping the 'state/province/region' is useful only if those location names are required in the postal address. In many countries this is not the case.
Non-English Shops: Are your language files up to date?
http://virtuemart.net/community/translations

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 2974
  • Joomla & Virtuemart addon developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3.0.x.y
Re: Complete List of States / Provinces / Regions
« Reply #14 on: May 19, 2018, 23:44:37 pm »
I checked the file.
You should not add a state_3_code, state_2_code
For eg in france 67 = bas-rhin for postal code
And in your database Bas-Rhin','68','68'
So the code can have right values in some case, but not in other cases.
An empty value is better then a generic one.
Here you can see the ZIP codes per states in france https://fr.wikipedia.org/wiki/Num%C3%A9rotation_des_d%C3%A9partements_fran%C3%A7ais, so you understand the problem.