Remove County And State Information from users and replace with ID from standardized lookup tables

[wshawn]

wshawn created Redmine issue ID 2105

After the forth website (in as many months) needing these look ups, I have found public domain / gnu released cvs files and converted them to mysql containing: countries (released by http://www.iso.org/iso ) – containing 2 letter designation, 3 letter designation, iso numbers, country names (all caps), country name Camel Cased – released annually and can be updated by core developers by running a perl script I also have, which will create the database for you on the fly and pull new list from them all in 30 seconds or less – usage – replace web sign up form asking for county with a call to a snippet in the appropriate section, which creates the option select combination pulled from the standardized ISO list and / or provide a look up table for developers to cater content based on county – provides a snippet for developers to use in their own projects – a simple Perl script I have will pull the ISO list from the iso site and create the table on the fly in a few seconds thereby removing the need for developers to update the list or manage the sign up or other respected code – currently modx has 239 counties listed in it and there are 244 zipcodes – containing two letter state abbreviation – counties – this would allow us to only have to obtain the users zip code while allowing us access to their city, state, county, without having to store it in the database – a simple left join in a query can combine the data as needed based on modx.zipcode = lookupZipCodes.zip – usage – lower storage overhead, standardize user input by either removing the need for typing or simply verify a 5 digit number against the database (easy enough with a simple Ajax or JavaScript call) states – Simple cross reference two letter abbreviation to state names (removes end users typing : fl, FL, FLORIDA, Florida, or even Floorida or Floriduh) – mostly display continuity by removing direct user input (drop down instead) If developers think this is worth pursuing I can create the mysql tables: modx_lookup_countries, modx_lookup_zipcodes, modx_lookup_states and the snippets to pull the look ups for states and countries. A possible snippet for zip code to verify what is typed is actually in the postal system may also be produced. Overhead: All three tables bzip into 545k of sql export file containing data and structure. Estimate is based on importing the tables (5 secs) Writing the snippets Changing the sign up in websignup.inc.php (will have to find the equivalent in revolution dumping a local server to install it locally)

[wshawn]

wshawn submitted:

Url above is a redirect and hits a bad page. Go here instead http://www.iso.org/iso/country_codes/iso_3166_code_lists.htm The lists they offer on this page (AFGHANISTAN;AF) are not the same as the one created with the perl script: INSERT INTO modx_lookup_countries VALUES ('AF','AFGHANISTAN','Afghanistan','AFG','004'); The Lists they offer have the following statement attached: The short country names from ISO 3166-1 and the alpha-2 codes are made available by ISO at no charge for internal use and non-commercial purposes. ==== Update==== I created a snippet which will perform the country looks up and create the same format currently used, with actually two letter country designations instead of auto incremented (meaningless) numbers. I could switch it to use the actual ISO country numeric value if desired. This script also outputs to a templated chunk with snippet parameters. Whole code is less than 55 lines with around 90 lines of documentation. I thought an unattended, default run version for modx combined with the template for designers would serve best for this. If necessary I could classify this, but with code this small is that not just wasting memory? I have also added the functionality of returning a given country name upon snippet parameter

[wshawn]

wshawn submitted:

Proposed snippet to replace the current handling of countries by modx Affected snippets and chunks included (by not limited to): [ [WebloginPE] ] [ [UserPlaceholders] ] FormSignup These are all using redundant code

[modxbot]

tracker_integration submitted:

A Pivotal Tracker story has been created for this Issue: http://www.pivotaltracker.com/story/show/3030068

[netProphET]

netprophet submitted:

I am all for the general idea you're proposing Shawn. I just want to raise a couple of things: First, it would be great if all MODx users weren't forced to see English country names. So I would love to see any solution include the facility for containing, importing, overlaying, downloading (whatever) country names in various languages as they are made available and packaged for the purpose. As far as states goes, I don't think it's advisable to change the default text box to a select list of U.S. State names. It's just too limiting for MODx users all over the world in other countries and speaking other languages. I have given some thought, but not enough yet, to adding these kinds of facilities as an Add-On, to make it easier for coders to access and utilize Country and State/Province/Region names and ISO codes etc. (for lookups and for templating out option lists etc). That would be useful both for general snippet development and bespoke development activities. It would also be very cool if a developer could use such an Add On to replace the country and state fields in the user profile with data/options of their choice. I'm just not up to speed enough yet on extending the core to figure that out. So I have a vague feeling that it might be best to start with some kind of abstraction layer or interface, separate from the data itself, because not every developer or webmaster will be working with the same data, but it would be great if they could work with it in a standard way. Thoughts?