Discussion:
Query Problem
(too old to reply)
Inkfast
2009-03-21 23:05:10 UTC
Permalink
I'm really missing something here that I know is very simple and I'm going to
kick myself when I get an answer but here goes. I have a query that is getting
simple numbers from the db and then a series of if statements looking for the
numbers and then performing the function I need. My problem is I'm only getting
a positive result on the first row of the query. I created a simple test page
to demonstrate.

<cfquery datasource="#dsn#" name="GetRawMaterials">
Select market_component_id
From tbl_team_component
Where team_cycle_id = '#Session.team_cycle_id#'
</cfquery>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Containso-8859-1"
/>
<title>Untitled Document</title>
</head>

<body>
<cfif Isdefined("GetRawMaterials.market_component_id") And
GetRawMaterials.market_component_id Contains "27">
27: Yes
<cfelse>
27: No
</cfif>
<br />
<cfif Isdefined("GetRawMaterials.market_component_id") And
GetRawMaterials.market_component_id Contains "28">
28: Yes
<cfelse>
28: No
</cfif>
<br />
<cfif Isdefined("GetRawMaterials.market_component_id") And
GetRawMaterials.market_component_id Contains "29">
29: Yes
<cfelse>
29: No
</cfif>
<br />
<cfif Isdefined("GetRawMaterials.market_component_id") And
GetRawMaterials.market_component_id Contains "30">
30: Yes
<cfelse>
30: No
</cfif>
<br />
<cfif Isdefined("GetRawMaterials.market_component_id") And
GetRawMaterials.market_component_id Contains "31">
31: Yes
<cfelse>
31: No
</cfif>
<br />
<cfif Isdefined("GetRawMaterials.market_component_id") And
GetRawMaterials.market_component_id Contains "32">
32: Yes
<cfelse>
32: No
</cfif>
<br />
<cfif Isdefined("GetRawMaterials.market_component_id") And
GetRawMaterials.market_component_id Contains "33">
33: Yes
<cfelse>
33: No
</cfif>
<br />
<cfif Isdefined("GetRawMaterials.market_component_id") And
GetRawMaterials.market_component_id Contains "34">
34: Yes
<cfelse>
34: No
</cfif>
<br />
<cfif Isdefined("GetRawMaterials.market_component_id") And
GetRawMaterials.market_component_id Contains "35">
35: Yes
<cfelse>
35: No
</cfif>
<br />
<cfif Isdefined("GetRawMaterials.market_component_id") And
GetRawMaterials.market_component_id Contains "36">
36: Yes
<cfelse>
36: No
</cfif>
<br />
<cfif Isdefined("GetRawMaterials.market_component_id") And
GetRawMaterials.market_component_id Contains "37">
37: Yes
<cfelse>
37: No
</cfif>
<br />
<cfif Isdefined("GetRawMaterials.market_component_id") And
GetRawMaterials.market_component_id Contains "38">
38: Yes
<cfelse>
38: No
</cfif>

</body>
</html>
Dan Bracuk
2009-03-22 01:14:20 UTC
Permalink
Either use a value list and/or a loop. If you don't specify a row number for your query results, you get the first row only, just as you described.
Inkfast
2009-03-22 01:57:09 UTC
Permalink
Perhaps I could set row numbers based on the query results... I tried a loop
and it just ran over itself and still produced a no result. Actually I've tried
many things, this cannot be that complicated.
Kibbage.TEESO
2009-03-22 04:16:17 UTC
Permalink
I may not be following your goal but this is what I would do:

<body>
<cfoutput query="GetRawMaterials">
#market_component_id#: Yes
</body>
Kibbage.TEESO
2009-03-22 04:35:03 UTC
Permalink
If you are looking for specific values I would do this:

<body>
<cfset lstValues = ValueList(GetRawMaterials.market_component_id)>
<cfif ListContains(lstValues, "27")>
27: Yes
<cfelse>
27: No
</cfif>
<cfif ListContains(lstValues, "28")>
28: Yes
<cfelse>
28: No
</cfif>
<cfif ListContains(lstValues, "29")>
29: Yes
<cfelse>
29: No
</cfif>
etc.
</body>
Dan Bracuk
2009-03-23 00:18:32 UTC
Permalink
Where are all those numbers coming from anyhow? Could you not simply incorporate them into your query somehow?
tclaremont
2009-03-23 16:58:12 UTC
Permalink
If you are in a query loop, can't you just use [CurrentRow]?
Inkfast
2009-03-23 17:09:01 UTC
Permalink
I created the test page to include only the component numbers that are in the
db so every one should be a yes. The actual page I have this built into is over
a thousand lines of code. The second post by Kibbage is probably what I need
and I'm trying it now. What gets me is that in ten years of CF, I've never seen
this problem before.
tclaremont
2009-03-23 17:48:36 UTC
Permalink
If you have a predefined list of numbers that you are looking for, say 1 to 30,
try this:

<cfset lstValues = ValueList(GetRawMaterials.market_component_id)>
<cfloop index="Foobar" from="1" to="30">
#Foobar# <cfif ListContains(lstValues, "Foobar")>Yes<cfelse>No</cfif>
<br>
</CFLOOP>
Inkfast
2009-03-23 18:01:54 UTC
Permalink
Thanks for the help, the general consensus is certainly ValueList! After all the time I spent in my CF books looking for anything useful under "List" I should have been looking under "V". Go figure.
tclaremont
2009-03-23 18:02:33 UTC
Permalink
If your number set is not as simple as x through y, set up a new list of just
the numbers you are searching for, and then loop through that list rather than
through the index loop I demonstrated above.

It sure would reduce the number of lines of code, and make troubleshooting a
bit easier, I would think.

Loading...