Solved

odd results in database query

Posted on 2014-10-31
5
100 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
  • 3
  • 2
5 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 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 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
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 …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

759 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

22 Experts available now in Live!

Get 1:1 Help Now