3

Not sure if this is possible or not. What I am trying to do is build an output string via queries. I am concatenating the output "name" and appending the "value" to the end. Then outputting the string. I don't think this is possible. But I am looking for any alternatives.

So this is what I have:

qry1 is the main query. qry2 gets the value to append to the end of the string. So the value of variable test would look like this: "variables.qry1.100"

Which would make sense to qry1 as this is part of the query object. So then this string would return a correct value from the database as there is a subquery called 100

<cfoutput>
<cfloop query="variables.qry2">
    <cfset test = variables.qry1. & variables.qry2.#valueID#>
<td>#test#</td> 
</cfloop>
</cfoutput>

Many thanks.

JC

Leigh
  • 28,765
  • 10
  • 55
  • 103
jeeperscreepers
  • 143
  • 5
  • 16
  • What exactly do you need outputted? Do you want to make test a dynamic variable name and then output that variable? – Vincent P Jan 18 '13 at 10:10
  • yes so test should read #variables.qry1.100# equates to a subquery name and then outputs the subquery so the value returned maybe 12324 for #variables.qry1.100# so if it wsnt dynamic it would just read #variables.qry1.100# which would return from the query the 100 value. I'm trying to dynamically create the output string which then outputs a value from the query. – jeeperscreepers Jan 18 '13 at 10:15
  • I've added my answer. But why are you doing this? Using one queries output value to select a column in another query seems strange. To me at least. – Vincent P Jan 18 '13 at 11:05
  • Im using it to dynamically create a report which outputs to excel.The last set of columns names are dynamic and can be changed added etc so the query builds the column names and then assigns the subquery values to these columns.Each subquery is dynamically created and assigned an id. Its this id that I use to return a value. – jeeperscreepers Jan 18 '13 at 11:11
  • Ah makes sense now. Was worried that you were maybe making more work for yourself. – Vincent P Jan 18 '13 at 11:13
  • Its the only way i could think of doing it because everything is duynamic column names and values bit of a nightmare to figure out lol – jeeperscreepers Jan 18 '13 at 11:15

3 Answers3

13

So basically - given your example - you have a column in qry1 called 100 (etc, perhaps also 200, 300 etc), and the values 100, 200, 300 etc are row values in the valueID column of qry2? And qry1 is a single-row query? Is that right?

If you have the name of the column in a dynamic string, you use this syntax:

queryName[columnName][rowNumber]

Where queryName is the variable that is the query, columnName is the string holding the column name, and rowNumber is the row number (either an actual number, or a variable containing one).

So using your example variables, you code would be:

<td>#variables.qry1[variables.qry2.valueID][1]#</td>

There is no need to use evaluate() to do this, and it is not a good solution here. There is very seldom a need to use evaluate() in CFML, since the days of CF5.

I have no idea of the background of how your data structures came to be the way they are, but if you are needing to write the sort of code you are suggesting you need to... I'd be looking long and hard at how you're going about things.

Adam Cameron
  • 29,677
  • 4
  • 37
  • 78
4

What your trying to do is possible, but you need to build a variable name first.

Instead of

<cfset test = variables.qry1. & variables.qry2.#valueID#>

Try

<cfset test = "variables.qry1.#variables.qry2.valueID#">

Test will then be variables.qry1.[valueID value]. Note that [valueID value] is what is getting returned from the query, so the actual value inside the variable.

Then to display the value of variables.qry1.[valueID value].

#evaluate(test)#

UPDATE As stated by Adam Cameron's answer. You should really try to avoid the evaluate() function, it's quite a performance hit and not considered good practice. Instead rather use the following code (This is copied from Adam Cameron's answer)

#variables.qry1[variables.qry2.valueID][1]#

NOTE: Go look at Adam Cameron's answer for a better description of whats going on.

Vincent P
  • 426
  • 3
  • 16
  • Many thanks for sharing your knowledge, works just fine now :) – jeeperscreepers Jan 18 '13 at 11:08
  • 1
    Sorry Vincent: downvoting you. evaluate() should almost never be part of any suggested solution in modern CFML. – Adam Cameron Jan 18 '13 at 11:24
  • @AdamCameron. I've updated my answer to rather indicate that it is bad practice and also added that people should rather look at your answer. Hopefully the asker sees this, so many asker's accept an answer and never read any other answers. – Vincent P Jan 18 '13 at 12:01
0

Adam has the correct solution. Here is a modified version of your original code that does what I think you are trying to do.

<cfoutput query="variables.qry1">
<tr>
<cfloop query="variables.qry2">
    <cfset test = variables.qry1[variables.qry2.valueID][variables.qry1.currentrow]>
    <td>#test#</td> 
</cfloop>
</tr>
</cfoutput>
Billy
  • 236
  • 2
  • 5