Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

odd results in database query

Posted on 2014-10-31
5
Medium Priority
?
127 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 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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…
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

824 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