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)

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.

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

By (Deleted User) - April 4, 2012

Hi Gary,

I see what you mean - I'll see if we can improve the example for future releases to make it clearer.

Tom