I want to build an application where users can select a datasource/table and query it. They will be able to select what fields they want to be displayed, and also what criteria they are looking for (ie. dynamically build a query). So, for each table I have a list of fields, and whether users should enter criteria in a text box or choose from a select box for that field. If they choose from a select box I want to build one (obviously) and populate it with data from that field.
Firstly I have a query called GetFields which returns the FieldName, DisplayName, and what I've called ConstructType (Select, Text, and hopefully later Date, Between, GtrThan etc) for the table they have selected.
Typical values might be 'FirstName', 'First Name', 'Select' or 'City', 'City', 'Text' etc.
I also have a variable called DataTable which holds the table name.
After this I cfloop over the GetFields query, and have a CFSWITCH/CFCASE to deal with each 'construct'. The CFCASE statement for the select box is as follows:-
<CFCASE value="Select">
<BR><BR>
<CFOUTPUT>
<CFQUERY name="qSelect" datasource="MyDB">
SELECT #FieldName# FROM #DataTable#
GROUP BY #FieldName#
ORDER BY #FieldName#
</cfquery>
</cfoutput>
<CFOUTPUT>#DisplayName#:
<SELECT name="#FieldName#"></cfoutput>
<CFLOOP query="qSelect">
<CFOUTPUT>
<OPTION value="#FieldName#">#FieldName#
</cfoutput>
</cfloop>
</SELECT>
</cfcase>
The problem is with the OPTION tag. I don't want it to be the value of #FieldName# but rather the values from the field in the query 'qSelect', which is being held in #FieldName# !!
For instance if I select 'FirstName' then the first query would be:
<CFQUERY name="qSelect" datasource="MyDB">
SELECT FirstName FROM MyDB
GROUP BY FirstName
ORDER BY FirstName
</cfquery>
But the select bit would just show 'FirstName' (literally)for every option, instead of the actual FirstNames derived from the database.
So, I guess I want CF to work out what #FieldName# is first, and then use that value as a fieldname rather than an absolute value.
Got there in the end but did anyone understand me??
Firstly I have a query called GetFields which returns the FieldName, DisplayName, and what I've called ConstructType (Select, Text, and hopefully later Date, Between, GtrThan etc) for the table they have selected.
Typical values might be 'FirstName', 'First Name', 'Select' or 'City', 'City', 'Text' etc.
I also have a variable called DataTable which holds the table name.
After this I cfloop over the GetFields query, and have a CFSWITCH/CFCASE to deal with each 'construct'. The CFCASE statement for the select box is as follows:-
<CFCASE value="Select">
<BR><BR>
<CFOUTPUT>
<CFQUERY name="qSelect" datasource="MyDB">
SELECT #FieldName# FROM #DataTable#
GROUP BY #FieldName#
ORDER BY #FieldName#
</cfquery>
</cfoutput>
<CFOUTPUT>#DisplayName#:
<SELECT name="#FieldName#"></cfoutput>
<CFLOOP query="qSelect">
<CFOUTPUT>
<OPTION value="#FieldName#">#FieldName#
</cfoutput>
</cfloop>
</SELECT>
</cfcase>
The problem is with the OPTION tag. I don't want it to be the value of #FieldName# but rather the values from the field in the query 'qSelect', which is being held in #FieldName# !!
For instance if I select 'FirstName' then the first query would be:
<CFQUERY name="qSelect" datasource="MyDB">
SELECT FirstName FROM MyDB
GROUP BY FirstName
ORDER BY FirstName
</cfquery>
But the select bit would just show 'FirstName' (literally)for every option, instead of the actual FirstNames derived from the database.
So, I guess I want CF to work out what #FieldName# is first, and then use that value as a fieldname rather than an absolute value.
Got there in the end but did anyone understand me??