Countries and cities

3 months 3 days ago - 3 months 3 days ago #323737 by ericmuc
Countries and cities was created by ericmuc
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

Please Log in to join the conversation.

3 months 3 days ago #323739 by krileon
Replied by krileon on topic Countries and cities

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.

WHERE country_alpha_3_code = '[cb_country]'

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


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

3 months 3 days ago #323743 by ericmuc
Replied by ericmuc on topic Countries and cities
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

Please Log in to join the conversation.

3 months 3 days ago #323746 by krileon
Replied by krileon on topic Countries and cities

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.

WHERE FIND_IN_SET( `country_alpha_3_code`, REPLACE( '[cb_country]', '|*|', ',' ) ) > 0

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


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

3 months 3 days ago #323749 by ericmuc
Replied by ericmuc on topic Countries and cities
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

Please Log in to join the conversation.

3 months 3 days ago #323750 by krileon
Replied by krileon on topic Countries and cities
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.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.433 seconds

Facebook Twitter LinkedIn