SQL to create a list selection based on another value in record

6 posts by 2 authors in: Forums > CMS Builder
Last Post: April 4, 2012   (RSS)

By garyhoffmann - April 4, 2012

I'm trying to figure out how to create the SQL to get a list of values based on the value in another field in this same record.

For example, I want to select a county for a state, so I have a field called state where the user selects a state from my state table. Let's assume they pick WI.

Now, I want to populate the county selection list based on the value in this record's state field. I know I have to set the state field as the field to use to refresh the list, I'm just not sure how to reference a field within this record to use as part of my where clause.

Thanks in advance.
Gary.

Re: [garyhoffmann] SQL to create a list selection based on another value in record

By (Deleted User) - April 4, 2012

Hi Gary,

If you want to select all records with "state ='WI'" (assuming that the value is the same as the string selected and the state is stored in a field called 'state'), then you can add:
'where' => "state='WI'",
to the getRecords call (if you are using getRecords to retrieve the information) or, if you are hand coding the query, use:
$sql = "SELECT * FROM {$TABLE_PREFIX}your_table WHERE state='WI'";

Let me know if this helps,

Tom

Re: [Tom P] SQL to create a list selection based on another value in record

By garyhoffmann - April 4, 2012

Thank you for the response - I'm actually talking about within the CMS itself while creating a list field using the "list options" "Get options from MySQL query (advanced).

I've attached a screen shot.
Attachments:

4-4-2012-5-18-28-pm.png 46K

Re: [garyhoffmann] SQL to create a list selection based on another value in record

By (Deleted User) - April 4, 2012

Hi Gary,

I think you're after something like:
SELECT num, label FROM `<?php echo $TABLE_PREFIX ?>tableName` WHERE `state` = '<?php echo $ESCAPED_FILTER_VALUE ?>'
and then set the 'Advanced Filter' to the state list (when the state is changed, the list of options will be updated)

Let me know if that helps,

Tom

Re: [Tom P] SQL to create a list selection based on another value in record

By garyhoffmann - April 4, 2012

Oh, that's what was meant by $ESCAPED_FILTER_VALUE - it meant to use this literally ... oops. I thought it was an example, not a literal.

It seems to work perfectly now.

Thanks much.
Gary.