Skip to main content
October 19, 2008
Question

How can I return a dynamic column name from a query?

  • October 19, 2008
  • 1 reply
  • 387 views
Heeey!!

I am stuck on this problem. I'm pretty sure it can be done. Basically, I want to have a function that returns a column from a table that the developer decided to pick. E.g. getItemField(5,"item_title") - the first being an item ID and the second being the column we want to get. This gets passed to a CFC. However, I'm having trouble on returning the value of this query column because the column name is dynamic and now in the "arguments" scope.

My code fails on the CFRETURN tag:

<cfquery name="qryGetItemField" datasource="#request.dsn#" username="#request.username#" password="#request.password#">
SELECT
<cfif trim(itemField) neq "item_id">item_id,</cfif>
#arguments.itemField#
FROM #request.tbl_items#
WHERE item_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.itemID#" />
</cfquery>

<cfif qryGetItemField.recordcount eq 1>
<!---<cfdump var="#qryGetItemField#" />--->
<cfreturn qryGetItemField["arguments.itemField"] />
<cfelse>
<cfreturn "" />
</cfif>


As you can see, I'm trying to append the query object name with the dynamic arguments name that was passed. This errors and gives the following message:

[Table (rows 1 columns ITEM_ID, ITEM_TITLE): [ITEM_ID: coldfusion.sql.QueryColumn@b1bd7e] [ITEM_TITLE: coldfusion.sql.QueryColumn@10fd4ff] ] is not indexable by arguments.itemField

The error occurred in C:\ColdFusion8\wwwroot\cms\coldbox\components\tags.cfc: line 406
Called from C:\ColdFusion8\wwwroot\cms\coldbox\index.cfm: line 90
Called from C:\ColdFusion8\wwwroot\cms\coldbox\index.cfm: line 1
Called from C:\ColdFusion8\wwwroot\cms\coldbox\index.cfm: line 1

404 : <cfif qryGetItemField.recordcount eq 1>
405 : <!---<cfdump var="#qryGetItemField#" />--->
406 : <cfreturn qryGetItemField["arguments.itemField"] />
407 : <cfelse>
408 : <cfreturn "" />




Any help would be greatly appreciated. I'm pretty sure it can be done but my syntax is probably all wrong!

Thanks,
Mikey.
    This topic has been closed for replies.

    1 reply

    Inspiring
    October 19, 2008
    hi,
    A sample below:
    cheers,
    fober

    =================================================
    <cfset tempvar= items_query(1,"ITM_PARTNUMBER")>

    <cfoutput>
    value is: #tempvar#<br>
    </cfoutput>

    <cffunction name="items_query" output="Yes">
    <cfargument name="id" type="string" required="Yes">
    <cfargument name="field" type="string" required="Yes">

    <cfquery name= "rs_items" datasource="#application.database#">
    SELECT #field#
    FROM qry_item_info
    WHERE 1=1
    AND itm_id= #id#
    </cfquery>

    <cfreturn rs_items[#arguments.field#]>
    </cffunction>