Skip to main content
F
freedomflyer
Known Participant
August 12, 2008
Answered

Checking against a query with CFIF

  • August 12, 2008
  • 11 replies
  • 1520 views
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
This topic has been closed for replies.
Correct answer A***
<!--- 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>

11 replies

C
cjdunkerley
Inspiring
August 13, 2008
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?
D
Dan_Bracuk
Inspiring
August 12, 2008
I often debug if/else logic like this:

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

F
freedomflyerAuthor
Known Participant
August 12, 2008
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__-
Inspiring
August 13, 2008
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.




F
freedomflyerAuthor
Known Participant
August 12, 2008
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__-
Inspiring
August 12, 2008
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
-
-__cfSearching__-
Inspiring
August 12, 2008
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.
C
cjdunkerley
Inspiring
August 12, 2008
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__-
Inspiring
August 12, 2008
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
F
freedomflyerAuthor
Known Participant
August 12, 2008
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__-
Inspiring
August 12, 2008
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?


F
freedomflyerAuthor
Known Participant
August 12, 2008
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...






F
freedomflyerAuthor
Known Participant
August 12, 2008
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...






Show more replies
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices