Link to home
Start Free TrialLog in
Avatar of anandhisankar
anandhisankar

asked on

Retrieving query output for dynamic column

I need to write a code to retrieve data from a table but the column names for the tables are dynamic and they are stored in a table. To solve this I created an array that has column names thru CFQUERY. I created another CFQUERY to fetch data row. To CFOUT the datarow I need to use values in first array as column identifier and join it to second query resultset and must CFOUT the result. This is the code I tried but it doesnot work It display that invalid column name. Please help.


<cfquery name = "Customer_details" datasource="cust_manager">
Select column_name, data_type from information_schema.columns where table_name = 'customer_settings'  
</cfquery>
<cfset record_count = customer_details.recordcount>
<cfset array_column_name = ArrayNew(1)>
<cfset array_data_type = ArrayNew(1)>
<cfoutput query = "customer_details">
<cfset array_column_name[currentrow] = column_name>
<cfset array_data_type[currentrow] = data_type>
#array_column_name[currentrow]#
#array_data_type[currentrow]#<br>
</cfoutput>

<cfquery name = "Get_Customer_Settings" datasource="cust_manager">
select * from customer_settings where company_info_id = '#url.rid#'
</cfquery>
<cfset array_column_value = ArrayNew(1)>
<br><br>

<cfoutput>
<cfloop index = "loopcount" from = "1" to = "#record_count#">
dgfjfdh<br>
#get_customer_settings.array_column_name[loopcount]# <!--- Problem reported --->
</cfloop>

</cfoutput>


<!---<cfset array_column_value[currentrow] = >
<cfset array_data_type[currentrow] = data_type>
#array_column_name[currentrow]#
#array_data_type[currentrow]#<br>--->






</body>
</html>
Avatar of HamdyHassan
HamdyHassan

you need coldfusion to evaluate this
#get_customer_settings.array_column_name[loopcount]#
before running it

I will find syntax of eval ot val for you
I hope this is working

<cfoutput>
<cfloop index = "loopcount" from = "1" to = "#record_count#">
dgfjfdh<br>

#Evaluate(get_customer_settings.array_column_name[loopcount])# </cfloop>

</cfoutput>



About the whole idea of reading table with dynamic column name, I assume there is a better way without array, need another search at the web
Avatar of anandhisankar

ASKER

Hi,
Thanks for posting the reply. But it doesnt help me. Is there any way to get values from cfquery without specifying column names. All I need to do is to create a form for a table for which I dont know the columns names since they are dynamic.
ASKER CERTIFIED SOLUTION
Avatar of TallerMike
TallerMike

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial