Thread

Hi,

I’m working on a real estate site, and I’m trying to return a list of states where there are properties in.

The code immdiately below does work; “field_id_88” is the column name for the field that stores the state in the exp_channel_data table.

{exp:query sql="SELECT DISTINCT field_id_88 AS state FROM exp_channel_data"}
          <state>{state}</state>
{/exp:query}

However, I would like to try to NOT hard code the field name, but rather work with the name of the field, in case things get changed around for some reason. The field name is “property_state”. I tried the below code, using a nested sql query:

{exp:query sql="SELECT DISTINCT CONCAT('field_id_',(SELECT field_id FROM exp_channel_fields WHERE field_name = 'property_state')) AS state FROM exp_channel_data"}
          <state>{state}</state>
{/exp:query}

However this is not working. The field_id column stores an integer index, so I’m trying to concatenate “field_id_” with this index to return the name of the column where ‘state’ is stored. Instead of evaulating the inner sql query as a column to search on, it is returning as a string, so what appears within the <state> node in the output is just “field_id_88”, as opposed to returning a list of states.

Any clues on how to get the inner sql query to evaluate as a column to search on?

Hi mycloc,

I think that the only you can do something like this is either with some more advanced functions in MySQL like procedures and functions or by doing this in PHP and using two separate queries. Shall I move this to the CodeShare Corner forum so that you can get some community input?

Cheers

Greg