?
Solved

odd results in database query

Posted on 2014-10-31
5
Medium Priority
?
122 Views
Last Modified: 2014-10-31
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
0
Comment
Question by:Eric Bourland
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 40416516
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
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40416561
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
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40416596
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
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 2000 total points
ID: 40416618
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
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 40416638
Working great. And this makes sense to me.

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

Eric
0

Featured Post

DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

Question has a verified solution.

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

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 …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

719 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