Configuring dynamic USA state, city, zip fields with CB Query field plugin

Print

The CB Query field plugin (version 5.0.2 or better) supports 5 additional field types that get their select values from a database query.

These new field types are:

You can create as many CB Fields as you want of each field type.

The fields work just like their core counterparts but instead of having to provide a static list of values for each field, you can give an SQL query that generates the list of values. These queries can also be configured to dynamically refresh themselves when another CB field is updated using CB Core Fields Ajax.

Our use case is to create 3 CB Query Single Select drop down field based on data stored in a database table:

To demonstrate this use case implementation we have prepared an sql statement file with relevant usa state, city and zip code data:

You can download this file and use phpmyadmin to import it to your Joomla database.

Once successfully imported you should have a new table (and populated data) in your database named: usa_zips

With the latest CB Query field plugin (version 5.0.2 or better) installed and published you will be able to implement the cb_usastate field as follows:

  1. Create a new CB Field from CB Field Management area
  2. Select the 'Query Drop Down (Single Select)' type for your new field
  3. Give the field a name (e.g., usastate) and a title (e.g., USA State)
  4. Populate the Query parameter area of the field with:
    SELECT DISTINCT `state` FROM `usa_zips` ORDER BY `state`
  5. Set the 'Value Column' parameter to state (this way the 2 character iso code will be stored in the CB database)
  6. Set the 'Label Coumn' parameter to state (this way the drop down field will display the country names for selection)
  7. Keep the 'Group Column' parameter empty

You should then configure the cb_usacity field as follows:

  1. Create a new CB Field from CB Field Management area
  2. Select the 'Query Drop Down (Single Select)' type for your new field
  3. Give the field a name (e.g., usacity) and a title (e.g., USA City)
  4. Populate the Query parameter area of the field with:
    SELECT DISTINCT `city` FROM `usa_zips` WHERE `state` = '[cb_usastate]' ORDER BY `city`
  5. Set the 'Value Column' parameter to city
  6. Set the 'Label Coumn' parameter to city
  7. Keep the 'Group Column' parameter empty
  8. Configure the Update On parameter in Integrations > CB Core Fields Ajax to your previously created cb_usastate field

Finally, you should configure the cb_usazip field as follows:

  1. Create a new CB Field from CB Field Management area
  2. Select the 'Query Drop Down (Single Select)' type for your new field
  3. Give the field a name (e.g., usazip) and a title (e.g., USA Zip code)
  4. Populate the Query parameter area of the field with:
    SELECT `zip` FROM `usa_zips` WHERE `city`='[cb_usacity]' AND `state`='[cb_usastate]' ORDER BY `zip`
  5. Set the 'Value Column' parameter to zip
  6. Set the 'Label Coumn' parameter to zip
  7. Keep the 'Group Column' parameter empty
  8. Configure the Update On parameter in Integrations > CB Core Fields Ajax to your previously created cb_usastate and cb_usazip fields

 

On completion your three fields will display on our CB registration page like the following screenshot:

 

usastatecityzip