?
Solved

more trouble with query

Posted on 2014-11-04
5
Medium Priority
?
102 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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
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 …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

621 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