[thelist] Quick ColdFusion / SQL question

[Joshua OIson]

Do you know which row the "4" is in?  If so, you could select that row
(presumably by using a primary key of some sort) then looping through
results, searching for the column with the "4".  For example (simplified for
berevity)

<cfquery name="myquery">
  SELECT *
  FROM mytable
  WHERE id = #Val(row_num)#
</cfquery>

<cfset col_containing_my_value = "">
<cfset my_val = "4">

<cfloop list="#myquery.ColumnList#" index="col">
  <cfif myquery[col][1] IS my_val>
    <cfset col_containing_my_value = col>
    <cfbreak>
  </cfif>
</cfloop>

<cfoutput>
  Column #col_containing_my_value# contains #my_val#!
</cfoutput>


If you don't know the row, yuo'd have to select the entire table (remove the
WHERE clause) then wrap the cfloop in a cfoutput, replace the [1] with
[currentrow] and somhow break out of the cfoutput once the answer is found.

There may be an easier way.  I'll post again if I think of it.

HTH,
joshua

----- Original Message -----
From: "jon steele" <jjsteele22 at yahoo.com>
Subject: [thelist] Quick ColdFusion / SQL question


> Hello,
>
> I need a way to select the *field* name of a table based on data in
> it...for example if this was a table (with fieldXXX, etc. being names
> of columns):
>
> -fieldXXX-  -fieldYYY-   -fieldZZZ-
>     1           2            4
>
> I need to get the value "fieldZZZ" when knowing 4....so kinda like
> selecting the column name based on data in it (the data in the first
> row to be exact). Does this make sense? The reason is that the field
> names are created dynamically.
>
> If the above can't be done, I could go a more complex way. I have a way
> of getting all column headers in an array (a technique which I reuse in
> my app). Could this be of help in achieving my goal?
>
> I've been on this for a while...I just can't seem to get it (seemed
> simple at first).
>
> Thanks,
> Jon.