Checking against a query with CFIF

[freedomflyer]

Hi everyone,
I have a small problem that I can't seem to solve no matter how much I search.

I have a database column with values 10001, 10002, and 10003 that represent text (i.e., apple, orange, pear)
In coldfusion, I query the database for that column and then use a CFIF statement to test for the 3 possible values. I always end up getting a value of "Orange" and never get anything else, although there are different values in the database.
Can anyone enlighten me? The code is attached below

[-__cfSearching__-]

freedomflyer wrote:
> I have a database column with values 10001, 10002, and 10003

What is the data type of your column? Can you give a few examples of query values that incorrectly return "Orange"?

> <cfif #getNotes.process_id# IS 10002>

BTW, having nothing to do with your current problem you do not need the extra # signs there.

[freedomflyer]

The column type is int(11)

I just whittled it down to
<cfif getNotes.process_id IS 10001>
<cfset source = "Orange">
</cfif>

And try to display it in a table:

<cftable query="getNotes" htmltable="yes" border="yes">
<cfcol text=#source#>
</cftable>

And it says "source" is undefined...

[freedomflyer]

The column type is int(11)

I just whittled it down to
<cfif getNotes.process_id IS 10001>
<cfset source = "Orange">
</cfif>

And try to display it in a table:

<cftable query="getNotes" htmltable="yes" border="yes">
<cfcol text=#source#>
</cftable>

And it says "source" is undefined...

[-__cfSearching__-]

freedomflyer wrote:
> And it says "source" is undefined...

Yes, because there is no else case. But again, what is the actual value of getNotes.process_id that is failing? CFDump the query.

BTW, you know you could also do this in your database query using a CASE statement, right?

[freedomflyer]

OK - There is more data in the table, etc...and it is a bit more complicated, but I did output a table and it works PERFECTLY displaying 10001, 10002, 10003. What I need to do is convert these values to "apple" "orange" or "pear" (or whatever) before I insert them into the table...
so i used a CFIF statement to do that, and then output it into the table using a seperate variable, like I said above...
so...

<cfif #getNotes.process_id# IS 10002>
<cfset fruit = "Orange">

<cfelseif #getNotes.process_id# IS 10001>
<cfset fruit = "Apple">

<cfelseif #getNotes.process_id# IS 10003>
<cfset fruit = "Pear">

</cfif>

And then:

<cftable query="getNotes" htmltable="yes" border="yes">
<cfcol text=#fruit#>
</cftable>


But it says that fruit couldnt be found....yet, I can display the ACTUAL values like so:

<cftable query="getNotes" htmltable="yes" border="yes">
<cfcol text=#getNotes.process_id#>
</cftable>

[-__cfSearching__-]

I understand your goal, but I cannot really help without knowing more about the values.

Assuming you have eliminated bad data as a cause, it is possible CF is interpreting the values differently than you think. Since since CF is loosely typed it often applies some rather liberal rules with regards to interpretation and conversion of values. In short, while you may think you are comparing two simple numbers or two strings .. that may not be what is actually happening.

http://livedocs.adobe.com/coldfusion/7/htmldocs/00000909.htm

[cjdunkerley]

Hi,

Have you thought about having another table to contain the values apple, orange, etc.?

You could then simply JOIN the tables on the ID column and your table will display the text withou having to use the <cfif's

[-__cfSearching__-]

cjdunkerley wrote:
> Have you thought about having another table to contain the
> values apple, orange, etc.?

That is probably the approach I would take. Either that or a CASE statement. Unless there is a specific reason the comparison must be done in CF.

[freedomflyer]

As I said, the values are int(11) in my table,
and I am basically using <cfif getNotes.process_id IS 10001>
and then setting a variable after that.
I narrowed it down and so there could only be one problem and thats my if statement...what else would you like?

[-__cfSearching__-]

freedomflyer wrote:
> I narrowed it down and so there could only be one problem
> ...what else would you like?

To see an example of a value that fails the cfif test. ie CFDump the query to show the raw values. Obviously CF is not performing the comparison as you think it should. But it is difficult to say much of anything about cause, without being able to see the actual data values. They are just simple numbers, not anything confidential, correct?

As I said, there is often a certain amount of magic involved in CF conversions. That might be the problem here. But without knowing more that is just a guess.
http://livedocs.adobe.com/coldfusion/7/htmldocs/00000909.htm

[Dan_Bracuk]

I often debug if/else logic like this:

<cfif somevariable is "some value>
true
<cfelse>
<cfdump var="#somevariable#">
<.cfif>

[freedomflyer]

Ok. I CFDumped the query and it shows exactly what I wanted to. 10001, 10002, and 10003. The data is there, and everything works just great. I simply cannot write an If statement to do it correctly. I am quite sure that i am using an integer value...so could anyone just try to write a quicky that uses 10001 for apple, 10002 for orange, and 10003 for pear?
Maybe I am writing it incorrectly...
Thank you so much! Hopefuly I can fix this!

[-__cfSearching__-]

freedomflyer wrote:
> so could anyone just try to write a quicky that uses 10001 for apple, 10002 for orange, and 10003 for pear?

Your code works perfectly with MySQL 5.0.5 and CF8.

[A*** · Accepted solution]

<!--- I suggest name/value pairs structure from which you can pull out your fruit value --->

<cfset getNotes[10002] = "Orange" />
<cfset getNotes[10001] = "Apple" />
<cfset getNotes[10003] = "Pear" />

<!--- For process_id 10001 output its fruit --->
<cfset process_id = "10001" />
<cfoutput>#getNotes[process_id]#</cfoutput><br />

<!--- Same for others --->
<cfset process_id = "10002" />
<cfoutput>#getNotes[process_id]#</cfoutput><br />
<cfset process_id = "10003" />
<cfoutput>#getNotes[process_id]#</cfoutput>

[cjdunkerley]

Is your <cfif code in inside a loop or output query and inside the <table> </table> tags?

Something like this:
getDataQuery...

<table...>
<cfoutput query="getDataQuery">

<cfif getDataQuery.col eq 10001>
Orange
<cfelseif...>
Apple
</cfif>

</cfoutput>
</table>

Does that help?

Can you post all your code?