jl_forum
asked on
Query in ColdFusion
Hey guys,
I'm fairly new to Dreamweaver/Coldfusion and was wondering how to do the following:
I have a database that I want to pull almost all the data from. For example, I have TableA with 26 fields, named A-Z in order respectively. I want to place this data into a HTML table (2x13), in a specific order that is not the order of the columns in my table. (e.g. C, Z, Y, Q…). How do I do that in one query (select * from TableA), but select the actual values from the specific fields to enter into my HTML table? That way, for each of the cells within the HTML table, I can insert the proper value from the query. Thanks.
Sincerely,
JL
I'm fairly new to Dreamweaver/Coldfusion and was wondering how to do the following:
I have a database that I want to pull almost all the data from. For example, I have TableA with 26 fields, named A-Z in order respectively. I want to place this data into a HTML table (2x13), in a specific order that is not the order of the columns in my table. (e.g. C, Z, Y, Q…). How do I do that in one query (select * from TableA), but select the actual values from the specific fields to enter into my HTML table? That way, for each of the cells within the HTML table, I can insert the proper value from the query. Thanks.
Sincerely,
JL
ASKER
Actually, thats what I wanted to know. How exactly do you access the values? You mentioned by field names, but what does that mean? E.g. C.Value (?) I'm not sure. Thanks.
This provides a somewhat dynamic solution, allowing you to specify in a single list the number of fields and the order you want the fields to be displayed. All you have to do is put in a comma delimited list the field order you want, from left to right. Example:
All you have to is the order, like this:
<cfset fieldOrder = "B,C,A,D">
And you will get:
B C
A D
You said you are fairly new to ColdFusion, so if you are unfamiliar with some of the CF functions i've used below, let me know what you would like me to explain.
<!---
<cfquery name="qryName" datasource="datasourceName
select *
from tableName
</cfquery>
---->
<!---
**************************
- creates a sample query
- comment out, or delete the following block of code, when using the query above
**************************
--->
<cfset qryName = QueryNew("A,B,C,D")>
<cfset QueryAddRow(qryName)>
<cfset QuerySetCell(qryName, "A", "A")>
<cfset QuerySetCell(qryName, "B", "B")>
<cfset QuerySetCell(qryName, "C", "C")>
<cfset QuerySetCell(qryName, "D", "D")>
<!---
**************************
--->
<!---
**************************
set the field order here, putting in a comma-delimited list how you want the values to appear from left to right
**************************
--->
<cfset fieldOrder = "B,C,A,D">
<cfset numOfFields = ListLen(fieldOrder)>
<table>
<cfloop from="1" to="#numOfFields#" index="f">
<cfset fieldName = ListGetAt(fieldOrder, f)>
<cfset fieldValue = Evaluate("qryName." & fieldName)>
<cfif f MOD 2 EQ 1>
<tr>
</cfif>
<td><cfoutput>#fieldValue#
<cfif f MOD 2 EQ 0>
</tr>
</cfif>
</cfloop>
</table>
All you have to is the order, like this:
<cfset fieldOrder = "B,C,A,D">
And you will get:
B C
A D
You said you are fairly new to ColdFusion, so if you are unfamiliar with some of the CF functions i've used below, let me know what you would like me to explain.
<!---
<cfquery name="qryName" datasource="datasourceName
select *
from tableName
</cfquery>
---->
<!---
**************************
- creates a sample query
- comment out, or delete the following block of code, when using the query above
**************************
--->
<cfset qryName = QueryNew("A,B,C,D")>
<cfset QueryAddRow(qryName)>
<cfset QuerySetCell(qryName, "A", "A")>
<cfset QuerySetCell(qryName, "B", "B")>
<cfset QuerySetCell(qryName, "C", "C")>
<cfset QuerySetCell(qryName, "D", "D")>
<!---
**************************
--->
<!---
**************************
set the field order here, putting in a comma-delimited list how you want the values to appear from left to right
**************************
--->
<cfset fieldOrder = "B,C,A,D">
<cfset numOfFields = ListLen(fieldOrder)>
<table>
<cfloop from="1" to="#numOfFields#" index="f">
<cfset fieldName = ListGetAt(fieldOrder, f)>
<cfset fieldValue = Evaluate("qryName." & fieldName)>
<cfif f MOD 2 EQ 1>
<tr>
</cfif>
<td><cfoutput>#fieldValue#
<cfif f MOD 2 EQ 0>
</tr>
</cfif>
</cfloop>
</table>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cool! =) Thanks.
<cfquery name='getdata' datasource='myDSN'>
select *
from tableA
</cfquery>
Then display it
<table>
<tr><th>heading1</th><th>h
<cfoutput query='getdata'>
<tr><td>#B#</td><td>#A#</t
</cfoutput>
it will loop through the "<cfoutput>" tags till all records are displayed, and you can access the values by the field names, and put them in any order you want.
good luck