Coldfusion - Variable Field Name When Looping Through Database Query Results

Coldfusion - variable field name when looping through database query results

Don't use Evaluate() for things like that! It's slow and should be avoided.

<cfloop index="i" from="1" to="4">
  <cfset foo = query["foo" & i][query.CurrentRow]>
</cfloop>

Or, if you like:

<cfloop index="i" from="1" to="4">
  <cfset foo = query["foo#i#"][query.CurrentRow]>
</cfloop>

Evaluate() is for evaluating bits of code. Don't use it for things that can be solved more elegantly in language-integrated, more appropriate ways.

EDIT:

When accessing Query objects with the "angle bracket"-syntax, you must append the (1-based) row number index (query["foo#i#"][RowNum]). When using the traditional "dot"-syntax (query.foo1), the current row is implicit.

To access the current row explicitly, use the QueryObject.CurrentRow property. But it could be any positive integer up to QueryObject.RecordCount. A range check is advised for anything other than CurrentRow.

This opens an interesting field: You can start to use query objects with "random access". Previously (before CFMX) all you could do was iterate them from start to end, pulling out the things that you look for. Now it's like a nested struct/array data structure that you can use in different ways.

Using column names from dynamically named variables in Coldfusion

Array notation is your friend.

<cfoutput>
<cfloop query = "ProdData">
<cfloop array = "#ProdData.getColumnList()#" index = columnName>
#prodData[columnName][currentrow]#
closing tags and formatting stuff

Looping over the query results set

Something like this should work for the data in your question.

<cfset NumberOfItems = ListLen(queryname.value[1])>
<cfoutput>
<cfloop from=1 to=NumberOfItems index=idx>
Row 1 item is #ListGetAt(queryname.value[1], idx)#<br>
Row 2 item is #ListGetAt(queryname.value[2], idx)#<br>
</cfloop>
</cfoutput>

Loop through 2 slectboxes submitted values to map to an update query. Coldfusion

Alternate Approach

Before I get to your current form, let me mention another option: using your database's import tools, like OPENROWSET or BULK INSERT. The former is a little more flexible it can be used from a SELECT statement. So you could do a direct insert from the CSV file, no looping. (I usually prefer to insert into a temp table first. Run a few validation queries, then insert/select the data into the primary table. But it depends on the application ..)

Anyway, once you have validated the column names, the insert with OPENROWSET is just a single query:

<!--- see below for how to validate list of column names --->
<cfquery name="insertRawData" datasource="yourDSN">
   INSERT INTO YourTable ( #theSelectedColumnNames# )
   SELECT  * 
   FROM    OPENROWSET( 'Microsoft.Jet.OLEDB.4.0'
            ,'text;HDR=YES;Database=c:\some\path\'
            , 'SELECT * FROM [yourFileName.csv]'  )
</cfquery>

Current Approach

Form:

Using your current method you would need to read the CSV file twice: once on the "mapping" page and again on the action page. Technically it could be as simple as giving the db column select lists the same name. So the names would be submitted as a comma delimited list:

<cfset csvHeaders = csvData[1]>
<cfloop array="#csvHeaders#" index="headerName">
    <cfoutput>
        Map file header: #headerName# 
        to column:  
        <select name="targetColumns">
            <option value="" selected>--- column name---</option>
            <cfloop query="getColumnNames">
                <option value="#column_name#">#column_name#</option>
            </cfloop>
        </select>
    </cfoutput>
    <br>
</cfloop>

Validate Columns:

Then re-validate the list of column names against your db metadata to prevent sql injection. Do not skip that step!. (You could also use a separate mapping table instead, so as not to expose the db schema. That is my preference.)

<cfquery name="qVerify" datasource="yourDSN">
    SELECT COUNT(COLUMN_NAME) AS NumberOfColumns    
    FROM   INFORMATION_SCHEMA.COLUMNS
    WHERE  TABLE_NAME = 'YourTableName'
    AND    COLUMN_NAME IN 
           (
        <cfqueryparam value="#form.targetColumns#" cfsqltype="cf_sql_varchar">
           )
</cfquery>

<cfif qVerify.recordCount eq 0 OR qVerify.NumberOfColumns neq listLen(form.targetColumns)>
    ERROR. Missing or invalid column name(s) detected
    <cfabort>
</cfif>

Insert Data:

Finally re-read the CSV file and loop to insert each row. Your actual code should contain a LOT more validation (handling of invalid column names, etcetera) but this is the basic idea:

<cfset csvData  = CSVToArray(....)>
<!--- deduct one to skip header row --->
<cfset numberOfRows = arrayLen(csvData) - 1>    
<cfset numberOfColumns = arrayLen(csvData[1])>  
<cfif numberOfColumns eq 0 OR numberOfColumns neq listLen(form.targetColumns)>
    ERROR. Missing or invalid column name(s) detected
    <cfabort>
</cfif>

<cfloop from="1" to="#numberOfRows#" index="rowIndex">
    <cfquery ...>
        INSERT INTO ClientColumnMappings ( #form.targetColumns# )
        VALUES 
        (
            <cfloop from="1" to="#numberOfColumns#" index="colIndex">
                 <cfif colIndex gt 1>,</cfif>
                 <cfqueryparam value="#csvData[rowIndex][colIndex]#" cfsqltype="cf_sql_varchar">
            </cfloop>
        )
    </cfquery>
</cfloop>

How can I dynamically reference column names while looping through a query's results?

It's possible using structure notation, but also requires a query row number. The general syntax is

#queryName[ "columnName" ][ rowNum ]# 
<!--- 
.. or specifically 
--->
#getCapabilityAndDescription[ DescriptionVariable ][ 1 ]#

To output all of the query columns dynamically. Use GetMetaData() to retrieve an array of query column properties (in select order). Then use the name property to output each column value:

<!--- Demo query --->
<cfset yourQuery = queryNew("EntryID,Baker3Description"
        , "integer,varchar"
        , [[1,"Descrip A"],[2,"Descrip B"]]
    )>

<cfset meta = getMetaData(yourQuery)>
<cfoutput query="yourQuery">
    <cfloop array="#meta#" index="props">
        #yourQuery[props.name][currentRow]#
    </cfloop>
    <br>
</cfoutput>

Though be careful building that kind of dynamic sql. If any of the values used to build the column names are user supplied (for example getJobDesc.Type) the query will be vulnerable to second order sql injection.

Double evaluation - How do I access my query column based on a variable that holds the column name?

When referencing column names as a structure, you need to also tell the query which row you want to get. You should also make sure that you check that the column name exists if you didn't get the cols variable via myQuery.ColumnList.

Use the following code to dynamically reference each column in your loop:

<table>
    <cfoutput query="myQuery">
        <tr>
            <cfloop list="#cols#" index="col">
                <td>#myQuery[col][CurrentRow]#</td>
            </cfloop>
        </tr>
    </cfoutput>
</table>

Coldfusion looping over query and display html tag only one time

You'll want to try to go farther than I did and reduce this to one query overall if possible, but this should get you on the right track

Combine your two inner queries into one query, add an order by attribute on the duplicate row type, and then add a group by attribute to your cfloop. If you're on CF10? or lower you may need to use cfoutput instead of cfloop to use the group attribute

<cfquery name="client_id" datasource="#application.dsn#">
 Select borrower_id,client_id,id from contracts where borrower_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(url.ID)#">
</cfquery>

<cfloop query="client_id">
  <cfquery name="Payment_log" datasource="#application.dsn#">
    SELECT pl.log_id log_id
    FROM paymentLog pl
        INNER JOIN paymentLog_file plf ON pl.log_id = plf.log_id
    WHERE contract_id = <cfqueryparam value="#client_id.id#" maxlength="36" cfsqltype="CF_SQL_BIGINT">
        AND DateDelete IS NULL
    ORDER BY pl.log_id, date_log, id
 </cfquery>

  <cfloop query="Payment_log" group="log_id">
    <th>file name</th>
  </cfloop>
</cfloop>

---

Related Topics