Solved

more trouble with query

Posted on 2014-11-04
5
97 Views
Last Modified: 2014-11-04
ColdFusion 9

Hi again. I'm having trouble with this query, below. The output displays only one project per bureau -- even when there are several projects associated with a bureau.

Example: http://www.doivista.org/showBureauRecord.cfm?bureauID=5

Many of the states (Alabama and so on) should have several projects displayed under them .... but only one project is displayed per state.

I've tried different queries. What am I missing?

Thanks again. Learning a lot.

Eric

query:
<cfparam name="URL.bureauID" default="0"> 

<cfquery name="getBureauProjects" datasource="#application.datasource#">
SELECT
b.bureauID,
b.bureauTitle,
p.projectID,
p.ProjectName,
p.bureauTitle,
p.State,
p.Archive
FROM #REQUEST.BureauTable# b
            LEFT JOIN #REQUEST.ProjectsTable# p
            ON p.bureauTitle = b.bureauTitle
           <!--- no need for cfqueryparam when it's a hard coded value --->
           AND  p.Archive = 0
WHERE b.bureauID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(url.bureauID)#">
ORDER BY p.State ASC
</cfquery>

Open in new window


HTML:

<!--- if a project was found ...--->
<cfif getBureauProjects.projectID GT 0>

<cfoutput query="getBureauProjects" group="state">
<h3>#getBureauProjects.State#</h3>
<p><a href="/showProjectRecord.cfm?ProjectID=#val(getBureauProjects.ProjectID)#">#getBureauProjects.ProjectName#</a></p>
</cfoutput>




<cfelse>
<h2>Currently no projects are associated with this bureau. Please check back for future projects.</h2>

</cfif>

Open in new window

0
Comment
Question by:Eric Bourland
  • 3
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 40422055
Hi Eric,

When you use group= in your cfoutput, you need to add another set of cfoutput tags to show the grouped values

<cfoutput query="getBureauProjects" group="state">
<h3>#getBureauProjects.State#</h3>
  <cfoutput>
   <p><a href="/showProjectRecord.cfm?ProjectID=#val(getBureauProjects.ProjectID)#">#getBureauProjects.ProjectName#</a></p>
  </cfoutput>
</cfoutput>

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40422084
gdemaria,

That's working. I am wrapping my head around the logic of this:

>>>When you use group= in your cfoutput, you need to add another set of cfoutput tags to show the grouped values

I put a CFOUTPUT within the grouped CFOUTPUT.

states are grouped: #getBureauProjects.State#

projects are insidd CFOUTPUT that is not grouped: #getBureauProjects.ProjectName#

I think this is something I need to get used to.

It kind of makes sense.
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 40422092
This is working great, but I am still struggling a little to see why it works. I'm going to look at this some more and look for other examples.

gdemaria thank you as always. Hope your day is going well.

Eric
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40422141
The group=State on the cfoutput says that any record within that cfoutput with the SAME state will only appear ONCE.

Add a second set of cfoutput tags to show each record that has the SAME state within the outer loop

NY
 - Rec 1
 - Rec 2
 - Rec 3
VT - state has changed so go to the next outer loop
 - Rec 4
 - Rec 5
NH - state has changed next outer loop
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40422324
>>>The group=State on the cfoutput says that any record within that cfoutput with the SAME state will only appear ONCE.

Add a second set of cfoutput tags to show each record that has the SAME state within the outer loop

OK. That makes a lot more sense in fact. =)

Thank you as always, gdemaria. =)

E
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question