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 - 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 Aland 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

Hi...
I have a problem with this isseu. I followd step by step. First I've a question about to save the exel file to CSV.I use exel 2007 and have three options to save as CSV 1e CSV (sepreted and somthing, sorry can't translate in english) 2e CSV (macintosh) 3e CSV (MS-DOS)
I used the first one but I get a warning about some function wont be compotible with ( sepreted by list somthing. I just choose yes.

I did open by notepad and follow the instructions.
Now I want to upload in my phpadmin and get this error :"

SQL-query:

LOAD DATA INFILE '/tmp/phphgsnNB' INTO TABLE `jos_comprofiler_field_values` FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

MySQL retourneerde:

#1045 - Access denied for user 'vries25'@'localhost' (using password: YES)

What went wrong?

I will put the exel file as how i saved

Thanks

Post edited by: underdog, at: 2010/08/13 14:33

Your text file seems correct as far as your fieldvalueid is set following my tuto. But your issue is a MySQL problem.

You should first look at your "privileges" to grant your user "vries25" all privileges.
If not solved, look at your /phpmyadmin/config.inc.php file.

The best is to search on Google for the following

#1045 - Access denied for user 'root'@'localhost' (using password: YES)

Good luck

I asked my server if the privaleges are all open. I told them the problem. I get a mail bach and there they say that it wont work on this way because it's about diffrent users. They say the easy way to do it is to open the file countrylistEN.txt' and copy>past in phmyadmin/tab sql.

If I do that I get this error:

SQL-query:

18;

MySQL retourneerde:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '18' at line 1

I just want that it work...I hope you can help me

OK, it's not a CB issue and it seems that you will not have full privilege in managing your database online.

But you still have the possibility to use a nice plugin, even with much more features, not free but cheep: 5.95 $

Look at www.angekdesign.com.au/community-builder/cb-country-and-state-dropdown-fields-plugin

and and give me your feedback.

Good luck

Nico

Thanks!