?
Solved

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

Posted on 2014-10-15
13
Medium Priority
?
102 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
[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
  • 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
Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

 
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
 
LVL 39

Accepted Solution

by:
gdemaria earned 1000 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 1000 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 1000 total points
ID: 40382205
Took to long writing as usual.  Never mind ... what gd said ;-)
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 1000 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

Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

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…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses
Course of the Month11 days, 14 hours left to enroll

752 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