?
Solved

Retrieving query output for dynamic column

Posted on 2003-03-08
4
Medium Priority
?
457 Views
Last Modified: 2013-12-24
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>
0
Comment
Question by:anandhisankar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8097603
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
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8097626
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
0
 

Author Comment

by:anandhisankar
ID: 8097742
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.
0
 
LVL 8

Accepted Solution

by:
TallerMike earned 300 total points
ID: 8097817
You can get the field names from the query like so, and output them dynamically:

<cfoutput query="qName">
  <cfloop list="#qName.columnList#" index="columnName">
    #Evaluate("qName.#columnName#")#
  </cfloop>
</cfoutput>

qName.columnList will return the column names for the query you specified in qName. What we're doing is looping through this list and outputting the values. You should be able to easily adapt this to your coding needs.
0

Featured Post

Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question