CFIF statement help 3

[kingjjx]

Hi, I have a table with a CODE field which could contain upto 5 different codes separated by commas

The field might have 0, 1 , 2 ,3 and upto 5 codes

How can I write a cfif statement to say that if code 1 is found , out put X, and if code 2 is found output Z and if code 3 is found out put W .. and so on ..

thank you

 

[GunJack]

I think this will do what you want:

<cfif listfind(#query1.codes#,&quot;1&quot>
x
<cfelseif listfind(#query1.codes#,&quot;2&quot>
z
......
....
</cfif>

Hope this helps,
GJ

 

[kingjjx]

This didnt seem to work.
Or maybe its on my end.

I created:

<CFQEURY NAME=&quot;CheckCode&quot;>
Select * from ArCustomer
</CFquery>


then :
<cfif listfind(#Checkcode.codes#,&quot;1&quot>
<img src=&quot;flag.gif&quot;>
</cfif>


Should it have worked like this ?? or did I do something wrong ?
thank you

 

[pcorreia]

GunJack's example assumed that the &quot;codes&quot; field would return a comma-separated list of codes. Is that what you're getting? If they are separated by something other than a comma, you need to specify that in the ListFind() function. If, for example, they're pipe-delimited, change it to this:

<cfif ListFind(Checkcode.codes,&quot;1&quot;,&quot;|&quot;)>
  <img src=&quot;flag.gif&quot;>
</cfif>

 

[kingjjx]

Hey, The codes are separated with comas with no spaces .. they look like this :

2,3,4

I want to out put that if a 2 is one of the codes, output x image .. if 3 .. out put Y image .. so if 2,3 and 4 is there it will out put 3 images.

how do i write it ?
thanks

 

[pcorreia]

I'll give it a try, but it seems like your earlier example should have worked...

<cfquery name=&quot;qryCheckCodes&quot; datasource=&quot;myDSN&quot;>
   SELECT codes
   FROM ArCustomer
</cfquery>

<cfoutput query=&quot;qryCheckCodes&quot;>
   <cfif ListFind(qryCheckCodes.codes,&quot;1&quot;) GT 0>
      First Image
   </cfif>
   <cfif ListFind(qryCheckCodes.codes,&quot;2&quot;) GT 0>
      Second Image
   </cfif>
   <cfif ListFind(qryCheckCodes.codes,&quot;3&quot;) GT 0>
      Third Image
   </cfif>
   <cfif ListFind(qryCheckCodes.codes,&quot;4&quot;) GT 0>
      Fourth Image
   </cfif>
   <cfif ListFind(qryCheckCodes.codes,&quot;5&quot;) GT 0>
      Fifth Image
   </cfif>
</cfoutput>

 

[kingjjx]

Finally Got it to work!

thanks a lot !!!

 

[kingjjx]

WAIT .. WAIT .. I GOT ONE MORE PROBLEM

IF IT FINDS A ONE .. IT JUST OUTPUTS THE IMAGE FOR CODE ONE , BUT LIKE I SAID, IT COULD CONTAIN CODE 1,2,3 AND I WANT TO OUTPUT IMAGES FOR 1,2, AND 3 (OUTPUT 3 IMAGES)

HOW DO I FIX THIS ??

 

[GunJack]

Just change the <cfif> statements like this.

<cfif listfind(#query1.codes#,&quot;1&quot>
x
</cfif>

<cfif listfind(#query1.codes#,&quot;2&quot>
z
</cfif>

<cfif listfind(#query1.codes#,&quot;3&quot>
...
</cfif>

GJ

 

[kingjjx]

Hi, I tried all the above codes and it still will only output the image for the first code it finds and doesnt look past that first code it finds. Example, if code present is 1,2 .. it only putputs image for code 1, If 3,4 .. it only putputs for image 3 ...

I want it to output the images for each code present.
I tried ... :

<cfif ListFind(qryCheckCodes.codes,&quot;1&quot GT 0>
First Image
</cfif>
AND :
<cfif listfind(#query1.codes#,&quot;1&quot>
x
</cfif>

both give me the same results
please help

 

[pcorreia]

You could try code like this to see what's coming through from the query and how ColdFusion is seeing it:

<cfquery name=&quot;qryCheckCodes&quot; datasource=&quot;myDSN&quot;>
   SELECT codes
   FROM ArCustomer
</cfquery>

<cfoutput query=&quot;qryCheckCodes&quot;>
   <p>
   Codes: '#qryCheckCodes.codes#'<br>
   List Length: #ListLen(qryCheckCodes.codes)#<br>
   <br>
   Find 1: #ListFind(qryCheckCodes.codes,&quot;1&quot;)#<br>
   Find 2: #ListFind(qryCheckCodes.codes,&quot;2&quot;)#<br>
   Find 3: #ListFind(qryCheckCodes.codes,&quot;3&quot;)#<br>
   Find 4: #ListFind(qryCheckCodes.codes,&quot;4&quot;)#<br>
   Find 5: #ListFind(qryCheckCodes.codes,&quot;5&quot;)#<br>
   <br>
   <cfloop index=&quot;i&quot; list=&quot;#qryCheckCodes.codes#&quot;>
      List Item: #i#<br>
   </cfloop>
   </p>
</cfoutput>

While this doesn't solve your problem directly, it should give you some insight into what's really happening. If it doesn't help you figure it out, post the output from this diagnostic code to the forum and maybe it will help someone else figure it out.

 

[kingjjx]

Okay, here's the result of the debug above: (The field have codes: 1,5)

Codes: '1,5 '
List Length: 2

Find 1: 1
Find 2: 0
Find 3: 0
Find 4: 0
Find 5: 0

List Item: 1
List Item: 5


I GUESS THE PROBLEM IS WITH THE FIND, ITS ONLY FOUND THE FIRST ONE; FIND 2 SHOULD HABE BEEN: 5

ANY HELP PLEASE ?
THANKS

 

[kingjjx]

Sorry, I meant FIND 5 , should have been 5 but it returned 0. Now, in the field the codes are separated by commas with no spaces, but I figured that would be ok.

 

[pcorreia]

Add the following line of code just below the opening <cfoutput> and see if it changes the debug output any:

<cfset qryCheckCodes.codes = Trim(qryCheckCodes.codes)>

I suspect the trailing space is causing this problem.

 

[kingjjx]

After adding the lines above, it gave me an error message:

Error Diagnostic Information

An error occurred while evaluating the expression:


<cfset qryCheckCodes.codes = Trim(qryCheckCodes.codes)>


Error near line 224, column 9.
--------------------------------------------------------------------------------

Cannot add value to query


The operation you have requested is invalid. Only query columns can be added to query objects. It is likely that you have omitted the indexing brackets of a query column you are trying to set.

For example, can produce this error message. The correct syntax is



The error occurred while processing an element with a general identifier of (CFSET), occupying document position (224:3) to (224:77).

 

[pcorreia]

I didn't know you couldn't do that. Revised debugging code below:

<cfquery name=&quot;qryCheckCodes&quot; datasource=&quot;myDSN&quot;>
   SELECT codes
   FROM ArCustomer
</cfquery>

<cfoutput query=&quot;qryCheckCodes&quot;>
   <cfset thisCodeList = Trim(qryCheckCodes.codes)>
   <p>
   Codes: '#thisCodeList#'<br>
   List Length: #ListLen(thisCodeList)#<br>
   <br>
   Find 1: #ListFind(thisCodeList,&quot;1&quot;)#<br>
   Find 2: #ListFind(thisCodeList,&quot;2&quot;)#<br>
   Find 3: #ListFind(thisCodeList,&quot;3&quot;)#<br>
   Find 4: #ListFind(thisCodeList,&quot;4&quot;)#<br>
   Find 5: #ListFind(thisCodeList,&quot;5&quot;)#<br>
   <br>
   <cfloop index=&quot;i&quot; list=&quot;#thisCodeList#&quot;>
      List Item: #i#<br>
   </cfloop>
   </p>
</cfoutput>

 

[Silvano]

<cfloop index=&quot;i&quot; list=&quot;#ValueList(qryCheckCodes.codes)#&quot;>
<!--- I don't think you need to trim it but here it is --->
<cfset i = Trim(i)>

<cfif i EQ 1 OR i EQ 2 OR i EQ 3 OR i EQ 4 OR i EQ 5>
<img src=&quot;flag.gif&quot;><br>
</cfif>
</cfloop> Sylvano
dsylvano@hotmail.com

 

[pcorreia]

Sylvano,

In this case, ValueList() won't work because kingjjx is not looking for a list of all the values returned by the query in this column, but is looking to use a single value returned as a list in ColdFusion. In other words, we're not getting x number of rows and interpreting those x number of values as a list; we're looking at getting one row whose value already is a list.

 

[bhightower]

Why don't you use switch/case it is faster than conditional if statements.

<cfswitch expression=&quot;qryCheckCodes.codes&quot;>
<cfcase value=&quot;1&quot; delimiters=&quot;Your Delimiter ( default=',' )&quot;>image a </cfcase>
<cfcase value=&quot;2&quot; delimiters=&quot;Your Delimiter ( default=',' )&quot;>image b </cfcase>
<cfcase value=&quot;3&quot; delimiters=&quot;Your Delimiter ( default=',' )&quot;>image c </cfcase>
<cfcase value=&quot;4&quot; delimiters=&quot;Your Delimiter ( default=',' )&quot;>image d </cfcase>
<cfcase value=&quot;5&quot; delimiters=&quot;Your Delimiter ( default=',' )&quot;>image e </cfcase>
</cfswitch>

 

[pcorreia]

None of us have used <cfswitch> because the point is not to find which one of a set of values matches the variable -- we're looking to match one or more values to a list contained in the variable. If the code were

<cfif><cfelseif><cfelseif></cfif>

, then a switch/case construct would be a better alternative. But our situation is more

<cfif></cfif><cfif></cfif><cfif></cfif>

, which can not be replaced more efficiently by a switch/case solution.