CFQUERY

Dynamically adding columns to a database query using ColdFusion

Leave a comment
Readability

Dynamically adding columns to a database query using ColdFusion

The fol­low­ing exam­ple shows how you can dynam­i­cally add a col­umn to a data­base query in Cold­Fu­sion using the queryAddColumn() function.


    SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
    FROM ARTISTS A

And the out­put of the <CFDUMP> tag is as follows:

query

[Record # 1]
ARTIS­TID: 1
FIRST­NAME: Aiden
FULL­NAME: DONOLAN, AIDEN
LAST­NAME: Donolan

[Record # 2]
ARTIS­TID: 2
FIRST­NAME: Austin
FULL­NAME: WEBER, AUSTIN
LAST­NAME: Weber

[Record # 3]
ARTIS­TID: 3
FIRST­NAME: Eli­cia
FULL­NAME: KIM, ELI­CIA
LAST­NAME: Kim

[Record # 4]
ARTIS­TID: 4
FIRST­NAME: Jeff
FULL­NAME: BACLAWSKI, JEFF
LAST­NAME: Baclawski

[Record # 5]
ARTIS­TID: 5
FIRST­NAME: Lori
FULL­NAME: JOHN­SON, LORI
LAST­NAME: Johnson

You can also pop­u­late an array object and pass it to the queryAddColumn() func­tion, as seen in the fol­low­ing example:


    SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
    FROM ARTISTS A

Or you can pre-​size the array object and set the col­umn value using the arrayResize() and arraySet() func­tions, as seen in the fol­low­ing examples:


    SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
    FROM ARTISTS A

The following example shows how you can dynamically add a column to a database query in ColdFusion using the queryAddColumn() function.

<cfquery name="getArtists" datasource="cfartgallery" maxRows="5">
    SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
    FROM ARTISTS A
</cfquery>
 
<cfset queryAddColumn(getArtists, "fullName", ArrayNew(1)) />
<cfloop query="getArtists">
    <cfset querySetCell(getArtists, "fullName", uCase(getArtists.LASTNAME & ", " & getArtists.FIRSTNAME), getArtists.currentRow) />
</cfloop>
 
<cfdump var="#getArtists#" metaInfo="false" format="text" />

And the output of the <CFDUMP> tag is as follows:

query

[Record # 1]
ARTISTID: 1
FIRSTNAME: Aiden
FULLNAME: DONOLAN, AIDEN
LASTNAME: Donolan

[Record # 2]
ARTISTID: 2
FIRSTNAME: Austin
FULLNAME: WEBER, AUSTIN
LASTNAME: Weber

[Record # 3]
ARTISTID: 3
FIRSTNAME: Elicia
FULLNAME: KIM, ELICIA
LASTNAME: Kim

[Record # 4]
ARTISTID: 4
FIRSTNAME: Jeff
FULLNAME: BACLAWSKI, JEFF
LASTNAME: Baclawski

[Record # 5]
ARTISTID: 5
FIRSTNAME: Lori
FULLNAME: JOHNSON, LORI
LASTNAME: Johnson

You can also populate an array object and pass it to the queryAddColumn() function, as seen in the following example:

<cfquery name="getArtists" datasource="cfartgallery" maxRows="5">
    SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
    FROM ARTISTS A
</cfquery>
 
<cfset col = arrayNew(1) />
<cfloop query="getArtists">
    <cfset arrayAppend(col, uCase(getArtists.LASTNAME & ", " & getArtists.FIRSTNAME)) />
</cfloop>
 
<cfset queryAddColumn(getArtists, "fullName", col) />
 
<cfdump var="#getArtists#" metaInfo="false" format="text" />

Or you can pre-size the array object and set the column value using the arrayResize() and arraySet() functions, as seen in the following examples:

<cfquery name="getArtists" datasource="cfartgallery" maxRows="5">
    SELECT A.ARTISTID, A.FIRSTNAME, A.LASTNAME
    FROM ARTISTS A
</cfquery>
 
<cfset col = arrayNew(1) />
<cfset arrayResize(col, getArtists.recordCount) />
<cfloop query="getArtists">
    <cfset idx = getArtists.currentRow />
    <cfset arraySet(col, idx, idx, uCase(getArtists.LASTNAME & ", " & getArtists.FIRSTNAME)) />
</cfloop>
 
<cfset queryAddColumn(getArtists, "fullName", col) />
 
<cfdump var="#getArtists#" metaInfo="false" format="text" />

Leave a Reply