Dynamic Variable Problem

[MorganGreylock]

I'm trying to do the following:

<CFLOOP From=&quot;1&quot; TO=&quot;#Counter#&quot; index=&quot;i&quot;>
<cfset tempcount = &quot;NumMonth#i#&quot;>
<cfif tempcount EQ 2>
<CFSWITCH EXPRESSION=&quot;Month#i#&quot;>
<CFCASE VALUE=&quot;January&quot;>
<cfset &quot;secondmonth#i#&quot; = &quot;February&quot;>
This is a test.<BR>
</CFCASE>
.....

Basically what I'm doing is this:

I've dynamically created select boxes on the previous page using the old Month#Counter# way.... So that generates Selects with the names of
Month1, Month2, etc (Unknown # of selects based on input). Now, i want to
get the data back out of those. The drop down lists contain months, but for
some options, it should be two months (January-February for example). On the
previous page, I set NumMonths = 1 or 2 to let me know on this page whether or not
I needed to add January, or January and February. (The months are the field names, and if its a two month stretch, I need to fill in the same data for both fields).

My problem is this: I have multiple NumMonths variables, (denoted NumMonths1, NumMonths2, etc), and I can't get to them. I tried using this: NumMonth#i#, and it gives me NumMonth1, NumMonth2, etc, but those themselves are variables containing January, March, July, etc. I need to evaluate those, and I can't seem to do it. In concept, this is what I want to do:

<cfset temp = NumMonths#i#>
<Cfset month = #temp#> <!--- at this point, 'month' would contain January --->

Any suggestions on this? I've been racking my brain for a while. I tried the Evaluate() function, and either it doesn't do it, or I didn't use it correctly.

Thanks in advance,

MG

 

[Silvano]

this would be the right way to use evaluate function:

<cfset temp = Evaluate(&quot;NumMonths#i#&quot>


and, one more thing, you can not use evaluate() function on the left side of the equation;
other then that, if you are getting variables set properly you should get the result; only tip I can give you is to go step by step through the code, and by setting break points, check the values of the variables Sylvano
dsylvano@hotmail.com

 

[mikeshort]

Without digging too deeply into your problem, I'd say this is a candidate for an Array.

 

[misereatur]

How do I write a query that will do the following:

Select * from Table where FieldA = #Var1# or #Var2# or #Var3#

The variables, which are session variables, are set when a user log ins to this site. They determine which records the user may view. He may see only those records where both his corporate and district ID match, however any one user may be allowed to see records from multiple districts. The district ID numbers are in a field seperated by commas in the user information table. They are extracted with an array and each one is set equal to district1, distrtict2, etc., for as many as there are.

When I try as follows, it does not work. I get a tremendous output to which I see no logic:

<cfquery name=&quot;test&quot;
datasource='#changedatasource.ParamValue#'
dbtype=&quot;ODBC&quot;>
select o.*, p.*, s.* from orders o, problem p, status s
where o.ProblemCode=p.ProblemCode and o.StatusCode=s.StatusCode
and PONmbr='#form.inputpurchase#'
<cfif #parameterexists(Session.Restrict)#> and DistrictKey = '#Session.district1#'
<cfif #parameterexists(Session.district2)#> or o.DistrictKey = '#Session.district2#' </cfif>
<cfif #parameterexists(Session.district3)#> or o.DistrictKey = '#Session.district3#' </cfif>
<cfif #parameterexists(Session.district4)#> or o.DistrictKey = '#Session.district4#' </cfif>
<cfif #parameterexists(Session.district5)#> or o.DistrictKey = '#Session.district5#' </cfif>
<cfif #parameterexists(Session.district6)#> or o.DistrictKey = '#Session.district6#' </cfif>
</cfif>
</cfquery>

Thank you,

Norman Bell

 

[Silvano]

simplify your query, make it work and then build on it by adding more complicated conditions;
here are few tips:

- you shouldn't use session variables like this; you should use cflock whenever session variable is used; to avoid many cflock usage through the template, go with one cflock at the start of the template and scope all session variables;

cflock...
cfset variables.varName = session.varName
/cflock

now you can use variables.varName instead of session vars...


- about your query: what I see without digging is that in:

where o.ProblemCode=p.ProblemCode and o.StatusCode=s.StatusCode
and PONmbr='#form.inputpurchase#'

&quot;PONmbr&quot; is not properly scoped here; if you retreive data from more that one table, every field have to be scoped, so here you have to specify from wich table you are trying to retreive PONmbr field

same thing with:
...Session.Restrict)#> and DistrictKey = '#Session.district1#'...

the ?.DistrictKey ...

- the logic:
when you write condition like you did:
where o.ProblemCode=p.ProblemCode and o.StatusCode=s.StatusCode and PONmbr='#form.inputpurchase#'
<cfif #parameterexists(Session.Restrict)#> and DistrictKey = '#Session.district1#'
<cfif #parameterexists(Session.district2)#> or o.DistrictKey = '#Session.district2#' </cfif>
<cfif #parameterexists(Session.district3)#> or o.DistrictKey = '#Session.district3#' </cfif>...

it is same as you use this concept:
WHERE var1 = var2 AND var3 = var4 AND var5=var6 OR var7=var8

follow this logic and you can rewrite this and use the following instead with the same result:
WHERE (var1 = var2 AND var3 = var4 AND var5=var6) OR var7=var8

in other words, the condition is satisfied if the left or the right side from the &quot;OR&quot; operator return true; the left side will return true only if all three condition are true (var1 = var2 AND var3 = var4 AND var5=var6). If this is false, the program flow will evaluate the right side of the OR operator but there is only one thing to check: var7=var8, and if that, and THAT only is true, the whole where statment will return true;
I am talking here about the operators and the order of precedence that controls the order in which operators on the same line are evaluated; hereis the order of precedence for all operators:

Unary +, Unary -

^
*, /
MOD
+, -
&
EQ, NEQ, LT, LTE, GT, GTE, CONTAINS, DOES NOT CONTAIN
NOT
AND
OR
XOR
EQV
IMP



as you can see, &quot;AND&quot; and &quot;OR&quot; are very close but &quot;AND&quot; will be evaluated before &quot;OR&quot; operator






in short, try this concept for the WHERE condition of your query:

WHERE
<cfif parameterexists(Session.Restrict)>
<cfif parameterexists(Session.district1)>
o.ProblemCode=p.ProblemCode AND o.StatusCode=s.StatusCode AND ?.PONmbr='#form.inputpurchase#' and ?.DistrictKey = '#Session.district1#'
</cfif>
<cfif parameterexists(Session.district2)>
o.ProblemCode=p.ProblemCode AND o.StatusCode=s.StatusCode AND ?.PONmbr='#form.inputpurchase#' and ?.DistrictKey = '#Session.district2#'
</cfif>
</cfif>...




Sylvano
dsylvano@hotmail.com

 

[mikeshort]

misereatur (Programmer) Jan 3, 2002 writes:
How do I write a query that will do the following:

Select * from Table where FieldA = #Var1# or #Var2# or #Var3#

to which I respond:

Select * from Table
where Field1 IN (#var1#,#var2#,#var3#)