Solved

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

Posted on 2014-10-15
13
101 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

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…
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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