Solved

more trouble with query

Posted on 2014-11-04
5
91 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>>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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now