populate list field based on another list field's selection

14 posts by 3 authors in: Forums > CMS Builder
Last Post: May 24, 2009   (RSS)

By atomicrabbit - May 4, 2009

Ok 'll try to keep this as simple as possible:

I created a section called Locations which will hold the company's locations around North America, so I've created a Country list field and a Province/State list field.

When inputting data, what I want to do is have the Province/State list field automatically updated with appropriate values when the Country list field is changed.

So if I'm adding an entry in the locations section, and I select Canada from the country field, I want the Provinces/States field to automatically be populated with Canada's provinces & territories only. And if I select US, I want it to be automatically populated with USA's states.

One way I thought of doing this is by creating a separate section that holds provinces and states and have three main fields:

Country, PS_Abbreviation, Province/State

So I would add all provinces and states from Canada and the US in the separate section and give each the appropriate country, so for example:

Canada, ON, Ontario
Canada, BC, British Columbia
United States, AL, Alabama
United States, AK, Alaska
etc etc

And MAYBE set the Province/State list field to populate using a MySQL statement using the values from the separate section, but I don't know how I'd reference the Country field that is selected.

Note: I want to do this all within the CMS backend, not on a html viewer page I create.

I hope this makes sense.

Re: [atomicrabbit] populate list field based on another list field's selection

By ross - May 5, 2009

Hi there.

Thanks for posting!

I see what you are trying to setup and right now the program isn't setup to do that in its interface. This might end up being something we can do a custom addon for you though. Send me an email through consulting@interactivetools.com with a link to this post and and an idea of the budget you had for something like this.

Thanks!
-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

Re: [atomicrabbit] populate list field based on another list field's selection

By Dave - May 5, 2009

Hi atomicrabbit,

Let me give this one a try. It's possible, but it is a little bit of work and requires some MySQL. One way is to create two sections: countries and states

For countries, just have a single text field called "country".

For states, start with a single text field called 'state' and then add a list (pulldown) field called "country" and in the field editor select these options:
List Options: [ Get options from database (advanced) ]
Section Tablename: countries
Use this field for option values: country
Use this field for option labels: country

Then, for the section you want these to appear in, add a field for "country" as described above and then a list (pulldown) field for "state" with these options:
List Options: [ Get options from MySQL query (advanced) ]
Query: SELECT state FROM cms_states WHERE country = '<?php echo $ESCAPED_FILTER_VALUE ?>'
Advanced Filter: Refresh list when this field changes: [ Country ]

Hope that makes sense. Let me know if you have any questions about that or need clarification about anything.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] populate list field based on another list field's selection

By atomicrabbit - May 5, 2009 - edited: May 5, 2009

thanks for the replies Ross and Dave.

Dave, I think your idea makes sense, although I don't think I need to create a Country section, I can just create a Country list field in the state section.

But regardless, I will try your solution and post the results!

Edit: ok it seems to return the proper values from the MySQL select statement, but it gives me the following error when I change the Country list pulldown
Error:
Notice: Undefined offset: 1 in /home/.sites/4/site38/web/cmsAdmin/lib/database_functions.php on line 507
<option value=''>&lt;select&gt;</option>
<option value="Alberta" ></option>
<option value="British Columbia" ></option>
<option value="Manitoba" ></option>
<option value="New Brunswick" ></option>
<option value="Newfoundland and Labrador" ></option>
<option value="Nova Scotia" ></option>
<option value="Northwest Territories" ></option>
<option value="Nunavut" ></option>
<option value="Ontario" ></option>
<option value="Prince Edward Island" ></option>
<option value="Quebec" ></option>
<option value="Saskatchewan" ></option>
<option value="Yukon" ></option>

So I'm not sure whats going on here, but right off the bat, it seems like only the values are being populated, not the visible text between the <option></option> tags

- Adam

Re: [atomicrabbit] populate list field based on another list field's selection

By Dave - May 6, 2009

Adam,

Are you using the latest version? The query may require two values (one for "value" and one for "label" in the pulldown). Try this:

Query: SELECT state, state FROM cms_states WHERE country = '<?php echo $ESCAPED_FILTER_VALUE ?>'

Let me know if that fixes it.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] populate list field based on another list field's selection

By atomicrabbit - May 6, 2009

The query may require two values (one for "value" and one for "label" in the pulldown)
yeah thats what I was thinking

Try this:

Query: SELECT state, state FROM cms_states WHERE country = '<?php echo $ESCAPED_FILTER_VALUE ?>'

that worked like a charm, dave. Thanks!!!! [:)]

Re: [Dave] populate list field based on another list field's selection

By atomicrabbit - May 8, 2009 - edited: May 8, 2009

is there an easy way to input the province/state entries into the database without having to do each one manually?

Can I just use a MySQL insert statement in a php page??

Re: [atomicrabbit] populate list field based on another list field's selection

By Dave - May 8, 2009

Yea, absolutely. You can insert multiple records with one query in MySQL like this:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
See: http://dev.mysql.com/doc/refman/5.1/en/insert.html

So in a PHP page (or phpMyAdmin, etc) that would be something like this:

$query = "INSERT INTO cms_states (states) VALUES
('British Columbia'),
('Alberta'),
('etc')";

mysql_query($query) or die("MySQL Error: " .mysql_error(). " with query $query\n");

Note that the last entry has no trailing comma.

Hope that helps!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] populate list field based on another list field's selection

By atomicrabbit - May 10, 2009 - edited: May 10, 2009

Hey Dave, I'm having a bit of trouble. I'm using a normal MySQL state as opposed to the getRecords() function to retrieve the values from the database to display on the page because I needed multiple WHERE statements. This is the line of code:

$locRecords = mysql_query_fetch_all_assoc("SELECT * FROM cms_locations WHERE country='" . $_REQUEST['c']
. "' AND province_state='" . $_REQUEST['ps'] . "' AND city='" . $_REQUEST['ci'] . "' ORDER BY country, province_state, city");

The problem is I can't use the same code to loop through an image field because the normal MySQL statement doesn't add all the extra variables like 'thumbUrlPath' or 'isImage' or 'hasThumbnail', etc etc. Is there a way I can get those variables still using a normal MySQL statement, or is there a way to use the getRecords() function and add multiple WHERE statements?