Solved

more trouble with query

Posted on 2014-11-04
5
96 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating Excel file via Coldfusion and XML leads to large file size 1 2,408
How to do a Left Outer Join sql query in coldfusion 4 98
Coldfusion RegEx 8 63
Apache error.log 19 43
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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