Countries and cities

Hi,
I have a query drop down field for countries (sql table has country_name and the country_alpha3_code).
Now I need the same for cities (the sql table has city_name and also the country_alpha3_code).

My needs in the profile edit: After choosing the country in the drop down field, the city drop down field may only shown the cities of the choosen country (profile edit still not saved).

I searched here in the forum for example, that I could take as a base for developing the right query and setting in the cb field, but I think, I have only a part at the moment, because it don't works.

My idea is:

The city field (query drop down field) has to fetch the choosen country in the integration tab "CB Core Fields Ajax" with choosen the country field (cb_country) in "Additional Fields". In cb_country the content "alpha_3_code" is stored.

My first step for the query at the moment is

SELECT city
FROM #__cities
WHERE country_alpha_3_code = `[cb_country]`

So how can I filter the drop down list with the cities of the cb_country? Do I have to add code for the user_id?

May be you can give me a link to a sample or doc?

Thanks a lot, best regards
Eric

The city field (query drop down field) has to fetch the choosen country in the integration tab "CB Core Fields Ajax" with choosen the country field (cb_country) in "Additional Fields". In cb_country the content "alpha_3_code" is stored.

My first step for the query at the moment is

SELECT city
FROM #__cities
WHERE country_alpha_3_code = `[cb_country]`

You've the right idea, but your query will fail since you're using back quotes instead of single quotes around your substitution. So you'll want to make the below change.


Aside from that it should work perfectly fine as you've everything setup correctly already.

Hi,
Thanks! Good, now it works.

I have a further question hereto: if I change the cb_country into a Query Drop Down (Multi-select) field, the cb_country has several alpha 3 codes stored. How can I filter this with the WHERE in the query? (all cities of the choosen countries shall be shown)

I tried something like this, but that doesn't work.

SELECT city
FROM #__cities
WHERE '[cb_country]' like '%country_alpha_3_code%'

or

SELECT city
FROM #__cities
WHERE country_alpha_3_code like '%[cb_country]%'
(this one works for one country, but if I choose a second not)

Do you have a hint here?

Thanks, best regards
Eric

I have a further question hereto: if I change the cb_country into a Query Drop Down (Multi-select) field, the cb_country has several alpha 3 codes stored. How can I filter this with the WHERE in the query? (all cities of the choosen countries shall be shown)

You're going to have to split them in your query the use FIND_IN_SET against it converted to a comma list. Easiest way to do that is with the following usage.


Suggest reading though MYSQL function documentation below and a quick search on stackoverflow if you've future SQL structure questions as we don't provide custom coding assistance here.

dev.mysql.com/doc/refman/5.7/en/functions.html

Hi,
Thank you very much for your code help, so now it is published here and can help the forum members. Such code snippets help me much. And yes, I always make a search in stackoverflow or here in the forum. But sometimes there a cb specific things needed and I cannot know whether this is due in my case or not. So a short question and a short answer helps really very much.

So back to my sample. It works well, if both fields (country and city) are not part of group field. But if I take them into a group field for editing all together with ajax, all works also - but the choosen cities are not stored.

Do you have a hint what I have to add / change to can save the cities also if the city field is part of a group field?

Thanks, best regards
Eric

The cities should be storing fine while inside of a field group. I've this exact setup with Country > State as a test case and it works fine. Please ensure CB Field Groups, CB Query Field, and CB Core Fields Ajax are all fully up to date.