Solved

more trouble with query

Posted on 2014-11-04
5
95 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
Renaming file after uploading in Coldfusion 4 224
cfchart display 12 98
CFMail not sending messages with queries 5 45
Automate ColdFusion Internet Form with VBA from Access 10 37
This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

14 Experts available now in Live!

Get 1:1 Help Now