WOW, Chris!
You can do a sh*tload with that query. The transactions you have going back and forth
between CF and the DB can create major overhead in the long run. What you need to do
is this:
1. Get Ben Forta's Book "Teach yourself SQL in 10 Min" from SAMS. (Ben, you can send
my commission check to my work address)
2. Indent and comment your code, if you want anyone to look at it.
3. Combine some of those queries into one transaction. If you are not comfortable with
UNIONs and JOINs, start with nested queries. I find these somewhat efficient, and
un-intimidating. Here's an example of your first two queries combined with a subquery.
SELECT DescriptionID
FROM knowledgeKeyDesc
WHERE KeywordID IN (SELECT ID
FROM knowledgekeywords
WHERE keyword like '%#string#%')
4. If you need more help, my fee's are very competitive, and I'm real close to LI ;)
HTH,
IGOR ILYINSKY
CREDIT|FIRST
SUISSE|BOSTON
GLOBAL WEB SERVICES
-----Original Message-----
From: Kris Pilles [mailto:kpilles@;wsboces.org]
Sent: Wednesday, November 06, 2002 11:52 AM
To: CF-Talk
Subject: Help refining search
Can you guys help me in refining this code so that the search is
better....
It works but I don't think it is as good as it should be:
<cfif isdefined('url.action')>
<script>
alert('A Helpdesk Representative Will Respond To Your Questions
Shortly!');
</script>
</cfif>
<body bgcolor="cccccc"></body>
<table cellspacing="2" width="100%"cellpadding="2" border="0">
<tr>
<td colspan="2">
<div align="center"><table cellspacing="2" cellpadding="2"
border="0">
<form action="index.cfm?search=<cfif
isdefined('form.input')><cfoutput>#form.input#</cfoutput></cfif>"
method="post">
<tr>
<td class="text">Enter Keywords </td>
<td><input type="text" name="input" class="field"></td>
<td><input type="submit" name="submit" value="Search"
class="submit"></td>
</tr>
</form>
</table>
</div>
</td>
</tr>
<tr>
<td colspan="2"><hr></td>
</tr>
<tr>
<td colspan="2">
<cfif isdefined('form.input')>
<table cellspacing="1" cellpadding="0" style="border: thin
solid black; ">
<tr>
<td>
<table cellspacing="0" cellpadding="0" border="0" width="100%">
<!--- Insert search string unless it is empty --->
<cfset last = 0>
<cfif '#form.input#' NEQ ''>
<Cfquery name="get" datasource="tech">
select ID, hits
from knowledgesearch
where search = '#form.input#'
</CFQUERY>
<cfif #get.recordcount# GT 0>
<Cfoutput query="get">
<cfset count = #hits# + 1>
<Cfquery name="hit" datasource="tech">
update knowledgesearch
set Hits = #count#, When = #createodbcdatetime(now())#
where ID = #ID#
</CFQUERY>
</CFOUTPUT>
<cfelseif #get.recordcount# LT 1>
<cfquery name="search" datasource="tech">
insert into knowledgesearch (search,when,hits)
values('#form.input#', #createodbcdatetime(now())#, 1)
</cfquery>
</cfif>
<cfelse>
<Cfset generic = 'Yes'>
</cfif>
<!--- Put search input into string --->
<cfset string = "#form.input#">
<cfquery name="matchkeywords" datasource="tech">
select ID
from knowledgekeywords
where keyword like '#string#%'
</cfquery>
<!--- if it does --->
<cfif #matchkeywords.recordcount# GT 0>
<!--- Get the descriptionId associated with the keyword
--->
<cfquery name="getdescID" datasource="tech">
select DescriptionID
from knowledgeKeyDesc
where KeywordID = #matchkeywords.ID#
</cfquery>
<cfif #getdescID.recordcount# EQ 0>
<Cfset generic = 'Yes'>
<cfelse>
<!--- Select out description so you can display --->
<cfif #last# NEQ #getdescID.descriptionID#>
<cfoutput query="getdescID">
<cfquery name="getdescription" datasource="tech">
select *
from knowledgeDescriptions
where ID = #descriptionID#
</cfquery>
<cfset last = #descriptionID#>
</cfoutput>
<cfoutput query="getdescription">
<tr bgcolor=beige>
<td>
<table cellspacing="2" width="100%"cellpadding="2"
border="0">
<tr>
<td class="text"><a
href="details.cfm?id=#ID#"><strong>#title#</strong></A></td>
</tr>
<tr>
<td class="text">#description#</td>
</tr>
<tr>
<td>
<hr>
</td>
</tr>
</table>
</td>
</tr>
</cfoutput>
</cfif>
</cfif>
<cfelse>
<Cfset generic = 'Yes'>
</cfif>
<!--- loop over string with mentioned delimiters --->
<cfloop index="word" list="#string#" delimiters=" .?!">
<!--- see if keyword exists for word being looped --->
<cfquery name="matchkeywords" datasource="tech">
select ID
from knowledgekeywords
where keyword like '<cfoutput>#word#</cfoutput>%'
</cfquery>
<!--- if it does --->
<cfif #matchkeywords.recordcount# GT 0>
<!--- Get the descriptionId associated with the keyword
--->
<cfquery name="getdescID" datasource="tech">
select DescriptionID
from knowledgeKeyDesc
where KeywordID = #matchkeywords.ID#
</cfquery>
<cfif #getdescID.recordcount# EQ 0>
<Cfset generic = 'Yes'>
<cfelse>
<!--- Select out description so you can display --->
<cfif #last# NEQ #getdescID.descriptionID#>
<cfoutput query="getdescID">
<cfquery name="getdescription" datasource="tech">
select *
from knowledgeDescriptions
where ID = #getdescID.descriptionID#
</cfquery>
</cfoutput>
<cfoutput query="getdescription">
<tr bgcolor=beige>
<td>
<table cellspacing="2" width="100%"cellpadding="2"
border="0">
<tr>
<td class="text"><a
href="details.cfm?id=#ID#"><strong>#title#</strong></A></td>
</tr>
<tr>
<td class="text">#description#</td>
</tr>
<tr>
<td>
<hr>
</td>
</tr>
</table>
</td>
</tr>
</cfoutput>
</cfif>
</cfif>
<cfelse>
<Cfset generic = 'Yes'>
</cfif>
</cfloop>
<cfif isdefined('generic')>
<cfif '#generic#' EQ 'Yes'>
<tr bgcolor=beige>
<td>
<table cellspacing="2" cellpadding="2" border="0">
<tr>
<td><a href="details.cfm?id=generic"><font
class="text"><strong>Generic Knowledgebase Title Here</strong></a></td>
</tr>
<tr>
<td><font class="text">Generic Knowledgebase Description
Here</font></td>
</tr>
</table>
</td>
</tr>
</cfif>
</cfif>
</table>
</td>
</tr>
<tr>
<td width="100%">
<cfquery name="getkeyword" datasource="tech">
select *
from knowledgesearch
where search = '#form.input#'
</cfquery>
<div style="padding-left:25;" class="text">If you did
not find any helpful results for
'<em><cfoutput>#form.input#</cfoutput></em>' and you would like someone
from Internal Support to contact you, please fill in the form
below:</strong></div>
<form action="moreinfo.cfm" method="post">
<table cellspacing="2" width="100%" cellpadding="2"
border="0" style="margin-left:25;">
<tr>
<td class="text" align="right">Email Address:</td>
<td><input type="text" name="email" class="field"></td>
</tr>
<tr>
<td class="text" align="right">Question:</td>
<td><input type="text" name="comments" class="field"></td>
</tr>
<tr><input type="hidden" name="search"
value="<cfoutput>#getkeyword.ID#</cfoutput>">
<td colspan="2"><div align="center"><input type="submit"
value="Contact Me" class="submit"></div></td>
</tr>
</table>
Kris Pilles
Website Manager
Western Suffolk BOCES
507 Deer Park Rd., Building C
Phone: 631-549-4900 x 267
E-mail: [EMAIL PROTECTED]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
