odd results in database query

ColdFusion 9
MS SQL Server 2012

On this page http://www.doivista.org/showBureaus.cfm I'm getting some strange results -- which tells me I have not built my query correctly.

I want to click on the title of a bureau, and then see all of the projects that are associated with the bureau. Instead, I see either zero or one projects. So I did something wrong. I've been staring at this for a while.

Here are the queries I am using:

http://www.doivista.org/showBureaus.cfm:

<cfquery name="getBureaus" datasource="#application.datasource#">
SELECT BureauTitle
FROM #REQUEST.BureauTable#
ORDER BY BureauTitle
</cfquery>

Open in new window


HTML, generates simple list of Bureaus:

 <cfoutput query="getBureaus">
 
<p><a href="/showBureauRecord.cfm?BureauTitle=#getBureaus.BureauTitle#">#getBureaus.BureauTitle#</a></p>

 </cfoutput>

Open in new window



showBureauRecord.cfm:

<cfquery name="getBureauProjects" datasource="#application.datasource#">
SELECT
b.bureauID,
b.bureauTitle,
p.projectID,
p.ProjectName,
p.Bureau
FROM #REQUEST.BureauTable# b, #REQUEST.ProjectsTable# p
WHERE b.bureauTitle = <cfqueryparam value="#url.bureauTitle#" cfsqltype="cf_sql_varchar">
AND b.bureauTitle = p.Bureau
ORDER BY b.bureauTitle
</cfquery>

Open in new window


I think I need to change the query immediately above, in showBureauRecord.cfm. I am working with two tables. Do I need to use a JOIN?

Thanks as always.

Eric
LVL 3
Eric BourlandAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
_agx_Connect With a Mentor Commented:
Hi Eric.


>> Do I need to use a JOIN?

If you need to include data from both tables in In showBureauRecord.cfm, yes. You need to include a JOIN here:

SELECT ...
FROM #REQUEST.BureauTable# b 
                 INNER JOIN   #REQUEST.ProjectsTable# p ON  p.ColumnItHasinCommon = b.ColumnItHasinCommon
WHERE ....

Open in new window


>> Instead, I see either zero or one projects


Can you post the cfoutput code for showBureauRecord.cfm?  I suspect it's using cfoutput group="..." and that's suppressing the display of the individual projects. If so, remove the "group" and problem solved.

<cfoutput query="getBureauProjects" group="BureauTitle">
...

Just a suggestion, instead of passing Bureau name - it's better to pass the ID instead. Then you don't have to worry about URL encoding the title string:
0
 
Eric BourlandAuthor Commented:
Got it!

New code:

http://www.doivista.org/showBureaus.cfm:

Query:
<cfquery name="getBureaus" datasource="#application.datasource#">
SELECT bureauID, bureauTitle
FROM #REQUEST.BureauTable#
ORDER BY bureauTitle
</cfquery>

Open in new window


HTML:
<cfoutput query="getBureaus">
<p><a href="/showBureauRecord.cfm?bureauID=#getBureaus.bureauID#">#getBureaus.bureauTitle#</a></p>
</cfoutput>

Open in new window



showBureauRecord.cfm:

Query:
<cfquery name="getBureauProjects" datasource="#application.datasource#">
SELECT
b.bureauID,
b.bureauTitle,
p.projectID,
p.ProjectName,
p.bureauTitle
FROM #REQUEST.BureauTable# b
INNER JOIN  #REQUEST.ProjectsTable# p
ON p.bureauTitle = b.bureauTitle
ORDER BY b.bureauTitle
</cfquery>

Open in new window



HTML:

<cfoutput query="getBureauProjects">
 
<h2>Bureau: #bureauTitle#</h2>
<h3>Associated Projects:</h3>
<p><a href="/showProjectRecord.cfm?ProjectID=#val(getBureauProjects.ProjectID)#">#getBureauProjects.ProjectName#</a></p>

</cfoutput>

Open in new window


I see how the JOIN is working. I think I need a GROUP in the <cfoutput query="getBureauProjects"> ... I am trying out a few things. Making progress. Thank you as always, _agx_. E
0
 
Eric BourlandAuthor Commented:
Update: It's working great.

<cfquery name="getBureauProjects" datasource="#application.datasource#">
SELECT
b.bureauID,
b.bureauTitle,
p.projectID,
p.ProjectName,
p.bureauTitle
FROM #REQUEST.BureauTable# b
INNER JOIN #REQUEST.ProjectsTable# p
ON p.bureauTitle = b.bureauTitle
WHERE b.bureauID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.bureauID#">
</cfquery>

Open in new window


I have one more question -- well, two. =)

1. code above is safe vs. injection, correct?

2. one bureau has no projects associated with it yet. I'd like to add a message -- "Currently no projects are associated with this bureau.".

When I do this (below) ... I don't see my "Currently no projects are associated with this bureau." message ... obviously because recordcount.getBureauProjects is, in fact, GT 0. What's a good way to do this?

_agx_ thanks as always. Hope you have fun and merry halloween plans. E

<cfoutput query="getBureauProjects">

<cfif recordcount.getBureauProjects GT 0>
<p><a href="/showProjectRecord.cfm?ProjectID=#val(getBureauProjects.ProjectID)#">#getBureauProjects.ProjectName#</a></p>

<cfelse>
<h2>Currently no projects are associated with this bureau.</h2>

</cfif>

</cfoutput>

Open in new window

0
 
_agx_Connect With a Mentor Commented:
RE #1

Yes. As long as #REQUEST.BureauTable# and #REQUEST.ProjectsTable# are values you hard code somewhere in your application file.  They would not be safe if they were supplied by user input like a FORM or URL variable.

RE #2

I think it should work if you rearrange the CFIF

<cfif recordcount.getBureauProjects GT 0>
    <cfoutput query="getBureauProjects">
        <p>... show link here </p>
    </cfoutput>
<cfelse>
    <h2>Currently no projects are associated with this bureau.</h2>
</cfif>

Open in new window


You too. Happy Halloween :)
0
 
Eric BourlandAuthor Commented:
Working great. And this makes sense to me.

_agx_, thank you as always. Have a safe halloween. Take care.

Eric
0
All Courses

From novice to tech pro — start learning today.