There is already a similar post but 5 years old and as I needed to make sure to have the countries names always written the same way (for filter, list, search purpose) I made a country drop-down field and want to share the process.
So if you want to create a drop-down type CB field with a list of countries (or other lists), but don't want to input each country name, 1 by 1, through the CB backend, you can do this by using phpMyAdmin to make some direct additions to the related comprofiler tables.
Before starting, make sure to backup your database! Never take unnecessary risks!
1 - Create a new 'Drop Down Single Select' CB field in the Field Management of CB backend and name it countrylistXX (where XX correspond to the language, ex: countrylistEN for english).
2 - Manually type in one fake allowable field value, otherwise the CB backend won't let you save that new field. It doesn't matter what the value will be, because at the end you will delete it (ex.:Fakecountry).
NOTE: doing so you have modify 2 CB tables of your Joomla database: 'jos_comprofiler_fields' and 'jos_comprofiler_field_values'. Opening phpMyAdmin you can see these modifications, but just concentrate yourself on the second one, the 'jos_comprofiler_field_values' used by the CB to store the values of list fields.
3 - Look at the last entry in the 'jos_comprofiler_field_values' table, which should contain five table field values looking like this:
fieldvalueid 49
fieldid 56
fieldtitle Fakecountry
ordering 1
sys 0
4 - Now you need to prepare a country list text file
4.1 - Depending on the language that you need, open one of the Excel files already prepared for you in the
Attachment country_lists_en_es_fr.zip not found
- country_list_CB_123_template.xlsx / lista_paises_CB_123_plantilla.xlsx / liste_pays_CB_123_gabarit.xlsx
4.2 - Fill all rows of column A with an incremental serie of numbers starting at the value of your 'fieldvalueid' +1 ( in this example, see point 3, it's 49, so you would start at 50, incrementing 1 by 1 up to the end of the list )
4.3 - Fill all rows of column B with the same value as your 'fieldid'. In this exemple it's 56
NOTE: your file should look like this:
50 56 Afghanistan 1
51 56 Aland Islands 2
52 56 Albania 3
53 56 Algeria 4
... ... .............
4.4 - Save it as CSV file ( save as > other formats and, in the extensions drop-down option, select 'CSV (Comma delimeted) (*.csv)' and not an other CSV. ( ex.: countrylistEN.csv )
Note: You will receive a Warning saying that your file may contain features that are not compatible with CSV,.... just click YES
4.5 - Open your 'countrylistEN.csv' file with notepad and resave it under, for example 'countrylistEN_ready.txt', selecting an encoding UTF-8. This encoding is not necessary for all languages but it's to make it simple.
5 - It's time to populate your table with all the countries.
5.1 - In phpMyAdmin, click on the 'jos_comprofiler_field_values' table name in the left column.
5.2 - On the top of the screen, select 'Import'
5.3 - Browse to load your 'countrylistEN_ready.txt', select as Character set of the file: UTF-8 and as Format for imported file: CSV using LOAD DATA
5.4 - Confirm clicking the GO button.
6 - Now you will delete the 'Fakecountry' table row, by selecting only his row, clicking on the red X, and confirming via the pop-up confirmation window.
WARNING: for security purpose, the 'countrylistEN.csv' and 'countrylistEN_ready.txt' files must be deleted afterwards has they contain values(fieldvalueid)that cannot be used twice.
NOTE: this procedure can be applied to any kind of population of CB fields with long list of values (countries, states, dates, activities, currencies, etc).
Post edited by: nicophp, at: 2010/07/13 18:05
Post edited by: nicophp, at: 2010/08/13 16:50
