Solved

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

Posted on 2014-10-15
13
100 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

734 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