Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Retrieving query output for dynamic column

Posted on 2003-03-08
4
Medium Priority
?
482 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
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month11 days, 20 hours left to enroll

564 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