Solved

display output from search results from three tables -- grouping problem

Posted on 2014-10-15
13
95 Views
Last Modified: 2014-10-16
ColdFusion 9
MS SQL Server 2012

I'm having another problem with grouping. I have been working on this a while -- time to ask for help.

Here is the code:
<cfif IsDefined("URL.search")>

<cfquery name="search" datasource="#application.datasource#">
SELECT PageID
,PageTitle
,PageContentLeft
,PageContentRight
,keywords
,safeURL
,newsID
,newsTitle
,newsContent
,newsAuthor
,projectID
,ProjectName
,OSMVISTA
,City
,State
,County
,CongressionalDistrict
,Bureau
FROM #REQUEST.contentTable#, #REQUEST.NewsTable#, #REQUEST.ProjectsTable#
WHERE 
   PageTitle         LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR PageContentLeft   LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR PageContentRight  LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR keywords          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR newsTitle          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR newsContent          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR newsAuthor          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR ProjectName          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR OSMVISTA          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR City          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR State          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR County          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR CongressionalDistrict          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
OR Bureau          LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.searchKeyword#%" maxlength="255">
</cfquery>

<cfif search.RecordCount GT 0> 

<p>You searched on keyword: <strong><cfoutput>#form.searchKeyword#</cfoutput></strong>. Your result is found in these pages:</p>

<cfoutput query="search" group="PageID">

<p><strong>Web Page Results:</strong></p>
<p><a href="http://#REQUEST.companyURL#/pages/#search.safeURL#.cfm">#search.PageTitle#</a></p>

<p><strong>News Item Results:</strong></p>
<p><a href="http://#REQUEST.companyURL#/showNewsItem.cfm?newsID=#search.newsID#">#search.newsTitle#</a></p>
 
 
<p><strong>Project Page Results:</strong></p>
<p><a href="http://#REQUEST.companyURL#/showProjects.cfm?State=#search.State#">#search.State#</a></p>

<p><strong>Bureau Results:</strong></p>
<p><a href="http://#REQUEST.companyURL#/showProjects.cfm?State=#search.State#">#search.Bureau#</a></p>

</cfoutput>

<cfelse>
<p><strong>No items matched your query.</strong></p>
<p class="align-center"><a href="/index.cfm">Return to <cfoutput>#REQUEST.companyName#</cfoutput> Home</a></p>
<p>&nbsp;</p>

</cfif>

</cfif>

Open in new window



The output I get is not grouped in any useful way. I would like to group the output neatly under headers:

Web Page Results:
News Item Results:
Project Page Results:
Bureau Results:

How can I do that? =)

Thanks as always for your help.

Eric
0
Comment
Question by:Eric Bourland
  • 6
  • 4
  • 3
13 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 40382036
HI ERic - it looks like you are trying to group by PageID, in order for the group attribute to work correctly, the sELECT statement needs to be ordered by that column (PAgeid)   so Add an order by PAgeID so your group will work.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40382103
Hi, gdemaria,

Good to hear from you.

It would be easy to group by PageID. However, not all search results will include a PageID.

Search results include:

Web pages, such as: http://www.doivista.org/pages/About-Us.cfm

Project pages, such as: http://www.doivista.org/showProjectRecord.cfm?ProjectID=83

News pages, such as: http://www.doivista.org/showNewsItem.cfm?newsID=110

So I need to group by PageID, ProjectID, and NewsID.

Is that possible, and am I thinking about this correctly?

Thanks as always.

Eric
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 40382134
WHen you say you want to group by allt hree values, do you mean in a heirarchy?

  Web pages...
  -  PRoject Pages
  -  - News PAges

Or do you mean on or the other or how would it look?
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40382145
Not in a hierarchy.

Here's what I had in mind:

* user enters search keyword
* ColdFusion displays database records that contain that keyword
* records can be web page (PageID), news story (newsID), or project page (projectID)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40382164
>> FROM #REQUEST.contentTable#, #REQUEST.NewsTable#, #REQUEST.ProjectsTable#


Hi Eric - This may only be tangentially related to your question, but it looks like there's no JOIN between any of those tables. So you're going to end up with a cartesian product ie a LOT of records.  That's almost certainly NOT what you want.

What's the relationship between those 3 tables?
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40382174
_agx_

Yep -- you're right -- no JOINs.

There's no relationship among the tables. Maybe this was not the best way to build these tables. There are separate tables for

web pages
news
projects

and there is not any link among them such as a consistent record ID.

At this point I can't really rebuild the three tables.

But I welcome ideas. =) Thank you as always.

Eric
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 39

Accepted Solution

by:
gdemaria earned 250 total points
ID: 40382195
No need to rebuild the tablesif  the data is not related, but you do want to seperate them into three different queries.  That will make your search results a lot easier too.   doing this will give you three seperate sections for your results news, web pages, projects...   each driven by its own query.  

IF you want the results mixed together, you can UNION the select statements together
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 250 total points
ID: 40382203
To be honest, the simplest option may be to run separate queries for each table.  Then output each one.

However, if you're only using a few columns on the search screen - say "ID", "Title" and "Content"  - and all 3 tables have those columns (or something similar), you could use a UNION to  merge the results from the disparate tables together into one query.  It would be bulky though.

For example


SELECT WebPageID AS ID, WebPageTitle AS Title, WebContent AS Content FROM FirstTable WHERE .....
UNION ALL
SELECT NewID AS ID, NewsTitle AS Title, NewContent AS Content FROM Second WHERE .....
UNION ALL
.... etc....
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 250 total points
ID: 40382205
Took to long writing as usual.  Never mind ... what gd said ;-)
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 250 total points
ID: 40382210
we said the same thing, but agx said it better; we're on the same page.    If you want your results in three sections; use three separate queries and loops.  If you want one results with the types mixed in, use UNION.  It seems from your question about grouping that you want the first option.
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40382306
>>>To be honest, the simplest option may be to run separate queries for each table.  Then output each one.

I think this makes the most sense.

I am going to try this now; I'll come back here and let you know what I get.

Thank you as always. =)
0
 
LVL 3

Author Comment

by:Eric Bourland
ID: 40382624
This is coming together with the three separate queries.

I'll follow up on this later tonight or tomorrow.

Thanks so much gdemaria and _agx_.  Hope your respective days are going well. We just had a torrential storm here in DC. I'm off to survey the yard and house. =)

Eric
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
ID: 40385517
gdemaria and _agx_,

This is working very well and the client is satisfied.

Thanks as always. Hope you both have a great evening. Take care.

Eric
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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…

708 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

16 Experts available now in Live!

Get 1:1 Help Now