#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    3
    Rep Power
    0

    Can you display query data based on a concatenated variable name?


    I need to pull and display data in a form out of a database column based on the current year.

    Let's say my database has 3 columns: A2014 A2013 A2012

    And I have a query that looks like this:

    <cfquery name="Query1" datasource="DB05"
    Select * from table where ID = #URL.ID#
    </cfquery>

    I can get the current year using: <cfset CYear = #Year(now())# />
    I can build the column name: <cfset CName = "A" & #CYear# />

    But is it possible to display query results based on a concatenated variable name?

    <cfinput type="text" size="10" name="C1" value="Query1.#CName#" /> (I know this doesn't work...but that's the idea)

    Is there a way to do this?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    3
    Rep Power
    0

    Can you display query data based on a concatenated variable name?


    For the benefit of others in the future that may have this same problem, I will respond to my own question (after a little more searching).

    You can use the array-style syntax instead. The only caveat is that you need to explicitly specify the row from which you want the value to come (and if the query has no rows, this will error out).

    I tried it on the example above and it works great!

    <cfinput type="text" size="10" name="C1" value="Query1[CName][1]" />

    (Thanks to: Sixten Otto from another forum)
  4. #3
  5. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,275
    Rep Power
    968
    What you probably want is: #Query1[CName][currentRow]#. What you have now will work but it will always give you the value from row number 1.

    As an aside, it's worth pointing out that this is a really bad schema design. You should almost never have repeating columns like that. Your table is screaming out for proper normalization.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    3
    Rep Power
    0
    Thank you, kiteless. Although the database schema was only an example to hopefully make the question a little more clear, I will keep your suggestion in mind for the future. Your input is appreciated.

IMN logo majestic logo threadwatch logo seochat tools logo