#asp.net #sql #sql-like
#asp.net #sql #sql-подобный
Вопрос:
Меня попросили отредактировать функцию поиска в чужом коде.
У меня нет огромных знаний в этом конкретном языке, и хотя я уверен, что это легко исправить, я не хочу нарушать его без совета.
Функция поиска возвращает слишком много результатов, ее поисковая часть слова. Таким образом, поиск «e» вернет очень многое с «e» в нем..
Мне нужно отредактировать, чтобы выполнить поиск по полному слову.
Вот весь код.
<!-- #include virtual="/site-elements/includes/config.asp" -->
<!-- #include virtual="/site-elements/includes/folder-string-functions.asp" -->
<!-- #include virtual="/site-elements/includes/database-open.asp" -->
<!-- #include virtual="/site-elements/includes/field-filtering-functions.asp" -->
<!-- #include virtual="/site-elements/includes/login-checker.asp" -->
<!-- #include virtual="/site-elements/includes/date-functions.asp" -->
<!-- #include virtual="/site-elements/includes/form-counter.asp" -->
<!-- #include virtual="/site-elements/includes/email-check-code-functions.asp" -->
<!-- #include virtual="/site-elements/includes/trimstring-functions.asp" -->
<!-- #include virtual="/site-elements/includes/toilehighlighter-functions.asp" -->
<!-- #include virtual="/site-elements/includes/paging-functions.asp" -->
<!-- #include virtual="/site-elements/includes/regular-expression-functions.asp" -->
<%
lightboxgallery = "Yes"
searchfor = fieldfiltering(Request("searchfor"))
searchfor = TRIM(searchfor)
IF RIGHT(searchfor, 1) = "/" then searchfor = LEFT(searchfor, LEN(searchfor)-1)
seotitle = "Tube Tech International - search results for: " amp; searchfor
seodescription = "Tube Tech International search results for: " amp; searchfor
seokeywords = "Tube Tech International, " amp; searchfor
Session("searchedfor") = searchfor
Session("searchedfortime") = now()
%>
<!-- #include virtual="/site-elements/template/template-start.asp" -->
<%
'#### Paging ####
thispage = id1
If NOT ISNUMERIC(thispage) OR thispage = "" then
thispage = 1
Else
thispage = CINT(thispage)
End If
'#################
%>
<div id="breadcrumbs">
<form action="/search/" method="get" class="searchform"><input type="submit" name="submit" tabindex="2" value="Search" class="search-click" /><input type="text" tabindex="1" value="Type your search here" onfocus="this.value=''" name="searchfor" class="search-box" /></form>
<a href="/"><img src="/images/home.png" alt="Home" width="12" height="12" class="home" border="0" /></a>
<a href="/" class="active">Home</a>
<a href="#">Search for: <i><% =searchfor %></i></a>
</div>
<div id="searchcontent">
<div class="leftbox">
<h1>Search</h1>
<p class="opening"><b>Your search results: <% =searchfor %></b></p>
<div class="clear"></div>
</div>
<% If searchfor <> "" then %>
<div class="rightbox">
<h2>Photo Gallery</h2>
<p>Click the images for a larger view.</p>
<div id="gallery">
<%
listingstringloop = ""
listingstring = ""
counterdiv = 0
strSQL = ""
strSQL = "SELECT 'case-studies' AS pagetype, cs.title AS entrytitle, cs.casestudyid AS id, csi.casestudyimageid AS imageid, csi.imagetitle, LIKE (cs.title, cs.content) AGAINST ('" amp; searchfor amp; "') AS relevance, LIKE (csi.imagetitle) AGAINST ('" amp; searchfor amp; "') AS imagerelevance "
strSQL = strSQL amp; "FROM casestudies AS cs, casestudiesimages AS csi "
strSQL = strSQL amp; "WHERE cs.casestudyid = csi.casestudyid AND cs.modestatus = '1' AND ((LIKE(csi.imagetitle) OR (cs.title like '%"amp; searchfor amp; "%') OR (cs.content like '%" amp; searchfor amp; "%') OR (csi.imagetitle like '%" amp; searchfor amp; "%')) "
strSQL = strSQL amp; "UNION ALL "
strSQL = strSQL amp; "SELECT 'news' AS pagetype, news.title AS entrytitle, news.newsid AS id, newsimages.newsimageid AS imageid, newsimages.imagetitle, LIKE (news.title, news.content) AGAINST ('" amp; searchfor amp; "') AS relevance, LIKE (newsimages.imagetitle) AGAINST ('" amp; searchfor amp; "') AS imagerelevance "
strSQL = strSQL amp; "FROM news, newsimages "
strSQL = strSQL amp; "WHERE news.newsid = newsimages.newsid AND news.modestatus = '1' AND ((news.title like '%" amp; searchfor amp; "%') OR (news.content like '%" amp; searchfor amp; "%') OR (newsimages.imagetitle like '%" amp; searchfor amp; "%')) "
strSQL = strSQL amp; "GROUP BY id "
strSQL = strSQL amp; "ORDER BY relevance DESC, imagerelevance DESC "
Set rsSearch = my_conn.Execute(strSQL)
If rsSearch.EOF = False then
counter = 1
Do while NOT rsSearch.eof
If counter > 2 then counter = 1
thisid = reversefieldfiltering(rsSearch("id"))
thistitle = reversefieldfiltering(rsSearch("entrytitle"))
imageid = reversefieldfiltering(rsSearch("imageid"))
pagetype = reversefieldfiltering(rsSearch("pagetype"))
imagetitle = reversefieldfiltering(rsSearch("imagetitle"))
listingstring = "<a href=""/images/" amp; pagetype amp; "/images/" amp; imageid amp; "-3.jpg"" class=""normal"" title=""" amp; imagetitle
If pagetype = "case-studies" then
listingstring = listingstring amp; "<br />To read the case study in full please <a href='http://www.tubetech.com" amp; buildlink("/case-studies/" amp; thisid amp; "_" amp; thistitle amp; "/") amp; "'>click here</a>"
ElseIf pagetype = "news" then
listingstring = listingstring amp; "<br />To read the news story in full please <a href='http://www.tubetech.com" amp; buildlink("/news/" amp; thisid amp; "_" amp; thistitle amp; "/") amp; "'>click here</a>"
End If
listingstring = listingstring amp; """>"
listingstring = listingstring amp; "<img src=""/images/" amp; pagetype amp; "/transparent.gif"" style=""background-image:url('/images/" amp; pagetype amp; "/images/" amp; imageid amp; "-1.jpg');background-position:center;"" width=""115"" height=""86"" alt=""" amp; imagetitle amp; """ title=""" amp; imagetitle amp; """ "
If counter = 1 then
listingstring = listingstring amp; "class=""left"""
Else
listingstring = listingstring amp; "class=""right"""
End If
listingstring = listingstring amp; "/></a>" amp; vbcrlf
listingstringloop = listingstringloop amp; listingstring
counter = counter 1
counterdiv= counterdiv 1
rsSearch.MoveNext
loop
%>
<div class="photocontainer"<% If counterdiv < 7 then %> style="height:auto;"<% End If %>>
<div id="photocontent" style="overflow: hidden;">
<% =listingstringloop %>
</div>
</div>
<%
End if
rsSearch.Close
Set rsSearch = Nothing
%>
</div>
</div>
<div class="leftbox">
<% If searchfor <> "" then %>
<%
searchrecordin = "No"
listingstringloop = ""
listingstring = ""
counterleftbox = 0
strSQL = ""
strSQL = "SELECT 'case-studies' AS pagetype, cs.casestudyid AS id, cs.title, cs.content, 0 AS menutype, '' AS menuoption, LIKE (cs.title, cs.content) AGAINST ('" amp; searchfor amp; "') AS relevance FROM casestudies AS cs "
strSQL = strSQL amp; "WHERE cs.modestatus = '1' AND (LIKE(cs.title, cs.content) AGAINST ('" amp; searchfor amp; "' IN BOOLEAN MODE) OR (cs.title like '%" amp; searchfor amp; "%') OR (cs.content like '%" amp; searchfor amp; "%')) "
strSQL = strSQL amp; "GROUP BY id "
strSQL = strSQL amp; "ORDER BY lastupdated DESC "
' strSQL = strSQL amp; "ORDER BY relevance DESC "
Set rsSearch = my_conn.Execute(strSQL)
If rsSearch.EOF = False then
searchrecordin = "Yes"
%>
<h2 class="section">Case studies</h2>
<%
Do while NOT rsSearch.eof
id = reversefieldfiltering(rsSearch("id"))
pagetype = reversefieldfiltering(rsSearch("pagetype"))
title = reversefieldfiltering(rsSearch("title"))
menutype = reversefieldfiltering(rsSearch("menutype"))
menuoption = reversefieldfiltering(rsSearch("menuoption"))
content = rsSearch("content")
content = reversefieldfiltering(content)
content = striptags(content)
content = trimstring(content, 148)
content = toilehighlighter(content, searchfor)
content = content amp; "..."
If pagetype = "web-pages" then
If menutype = "1" then
linkname = buildlink("/" amp; title amp; "/")
ElseIf menutype = "2" then
linkname = buildlink("/" amp; menuoption amp; "/" amp; id amp; "_" amp; title amp; "/")
End If
Else
linkname = buildlink("/" amp; pagetype amp; "/" amp; id amp; "_" amp; title amp; "/")
End If
title = toilehighlighter(title, searchfor)
listingstring = "<h3><a href=""" amp; linkname amp; """>" amp; title amp; "</a></h3>" amp; vbcrlf
listingstring = listingstring amp; "<p class=""opening""><a href=""" amp; linkname amp; """>" amp; content amp; "</a></p>" amp; vbcrlf
listingstringloop = listingstringloop amp; listingstring
counterleftbox = counterleftbox 1
rsSearch.MoveNext
loop
%>
<div class="casestudiescontainer"<% If counterleftbox < 5 then %> style="height:auto;"<% End If %>>
<div id="casestudiescontent">
<% =listingstringloop %>
</div>
</div>
<% End if
rsSearch.Close
Set rsSearch = Nothing
%>
<%
listingstringloop = ""
listingstring = ""
counterleftbox = 0
strSQL = ""
strSQL = "SELECT 'news' AS pagetype, news.newsid AS id, news.title, news.content, 0 AS menutype, '' AS menuoption, LIKE (news.title, news.content) AGAINST ('" amp; searchfor amp; "') AS relevance FROM news "
strSQL = strSQL amp; "WHERE news.modestatus = '1' AND (LIKE(news.title, news.content) OR (news.title like '%" amp; searchfor amp; "%') OR (news.content like '%" amp; searchfor amp; "%')) "
strSQL = strSQL amp; "GROUP BY id "
strSQL = strSQL amp; "ORDER BY relevance DESC "
Set rsSearch = my_conn.Execute(strSQL)
If rsSearch.EOF = False then
searchrecordin = "Yes"
%>
<h2 class="section">News</h2>
<%
Do while NOT rsSearch.eof
id = reversefieldfiltering(rsSearch("id"))
pagetype = reversefieldfiltering(rsSearch("pagetype"))
title = reversefieldfiltering(rsSearch("title"))
menutype = reversefieldfiltering(rsSearch("menutype"))
menuoption = reversefieldfiltering(rsSearch("menuoption"))
content = rsSearch("content")
content = reversefieldfiltering(content)
content = striptags(content)
content = trimstring(content, 148)
content = toilehighlighter(content, searchfor)
content = content amp; "..."
If pagetype = "web-pages" then
If menutype = "1" then
linkname = buildlink("/" amp; title amp; "/")
ElseIf menutype = "2" then
linkname = buildlink("/" amp; menuoption amp; "/" amp; id amp; "_" amp; title amp; "/")
End If
Else
linkname = buildlink("/" amp; pagetype amp; "/" amp; id amp; "_" amp; title amp; "/")
End If
title = toilehighlighter(title, searchfor)
listingstring = "<h3><a href=""" amp; linkname amp; """>" amp; title amp; "</a></h3>" amp; vbcrlf
listingstring = listingstring amp; "<p class=""opening""><a href=""" amp; linkname amp; """>" amp; content amp; "</a></p>" amp; vbcrlf
listingstringloop = listingstringloop amp; listingstring
counterleftbox = counterleftbox 1
rsSearch.MoveNext
loop
%>
<div class="newscontainer"<% If counterleftbox < 5 then %> style="height:auto;"<% End If %>>
<div id="newscontent">
<% =listingstringloop %>
</div>
</div>
<% End if
rsSearch.Close
Set rsSearch = Nothing
%>
<%
listingstringloop = ""
listingstring = ""
counterleftbox = 0
strSQL = ""
strSQL = "SELECT 'techniques' AS pagetype, tech.techniqueid AS id, tech.title, tech.content, 0 AS menutype, '' AS menuoption, LIKE (tech.title, tech.content) AGAINST ('" amp; searchfor amp; "') AS relevance FROM techniques AS tech "
strSQL = strSQL amp; "WHERE tech.modestatus = '1' AND (LIKE(tech.title, tech.content) OR (tech.title like '%" amp; searchfor amp; "%') OR (tech.content like '%" amp; searchfor amp; "%')) "
strSQL = strSQL amp; "GROUP BY id "
strSQL = strSQL amp; "ORDER BY relevance DESC "
Set rsSearch = my_conn.Execute(strSQL)
If rsSearch.EOF = False then
searchrecordin = "Yes"
%>
<h2 class="section">Techniques</h2>
<%
Do while NOT rsSearch.eof
id = reversefieldfiltering(rsSearch("id"))
pagetype = reversefieldfiltering(rsSearch("pagetype"))
title = reversefieldfiltering(rsSearch("title"))
menutype = reversefieldfiltering(rsSearch("menutype"))
menuoption = reversefieldfiltering(rsSearch("menuoption"))
content = rsSearch("content")
content = reversefieldfiltering(content)
content = striptags(content)
content = trimstring(content, 148)
content = toilehighlighter(content, searchfor)
content = content amp; "..."
If pagetype = "web-pages" then
If menutype = "1" then
linkname = buildlink("/" amp; title amp; "/")
ElseIf menutype = "2" then
linkname = buildlink("/" amp; menuoption amp; "/" amp; id amp; "_" amp; title amp; "/")
End If
Else
linkname = buildlink("/" amp; pagetype amp; "/" amp; id amp; "_" amp; title amp; "/")
End If
title = toilehighlighter(title, searchfor)
listingstring = "<h3><a href=""" amp; linkname amp; """>" amp; title amp; "</a></h3>" amp; vbcrlf
listingstring = listingstring amp; "<p class=""opening""><a href=""" amp; linkname amp; """>" amp; content amp; "</a></p>" amp; vbcrlf
listingstringloop = listingstringloop amp; listingstring
counterleftbox = counterleftbox 1
rsSearch.MoveNext
loop
%>
<div class="techniquescontainer"<% If counterleftbox < 5 then %> style="height:auto;"<% End If %>>
<div id="techniquescontent">
<% =listingstringloop %>
</div>
</div>
<% End if
rsSearch.Close
Set rsSearch = Nothing
%>
<%
listingstringloop = ""
listingstring = ""
counterleftbox = 0
strSQL = ""
strSQL = "SELECT 'web-pages' AS pagetype, pages.pageid AS id, pages.title, pages.content, pages.menutype, pages.menuoption, LIKE (pages.title, pages.content) AGAINST ('" amp; searchfor amp; "') AS relevance FROM webpages AS pages "
strSQL = strSQL amp; "WHERE pages.modestatus = '1' AND (LIKE(pages.title, pages.content) OR (pages.title like '%" amp; searchfor amp; "%') OR (pages.content like '%" amp; searchfor amp; "%')) "
strSQL = strSQL amp; "GROUP BY id "
strSQL = strSQL amp; "ORDER BY relevance DESC "
Set rsSearch = my_conn.Execute(strSQL)
If rsSearch.EOF = False then
searchrecordin = "Yes"
%>
<h2 class="section">Web Pages</h2>
<%
Do while NOT rsSearch.eof
id = reversefieldfiltering(rsSearch("id"))
pagetype = reversefieldfiltering(rsSearch("pagetype"))
title = reversefieldfiltering(rsSearch("title"))
menutype = reversefieldfiltering(rsSearch("menutype"))
menuoption = reversefieldfiltering(rsSearch("menuoption"))
content = rsSearch("content")
content = reversefieldfiltering(content)
content = striptags(content)
content = trimstring(content, 148)
content = toilehighlighter(content, searchfor)
content = content amp; "..."
If pagetype = "web-pages" then
If menutype = "1" then
linkname = buildlink("/" amp; title amp; "/")
ElseIf menutype = "2" then
linkname = buildlink("/" amp; menuoption amp; "/" amp; id amp; "_" amp; title amp; "/")
End If
Else
linkname = buildlink("/" amp; pagetype amp; "/" amp; id amp; "_" amp; title amp; "/")
End If
title = toilehighlighter(title, searchfor)
listingstring = " <h3><a href=""" amp; linkname amp; """>" amp; title amp; "</a></h3>" amp; vbcrlf
listingstring = listingstring amp; " <p class=""opening""><a href=""" amp; linkname amp; """>" amp; content amp; "</a></p>" amp; vbcrlf
listingstringloop = listingstringloop amp; listingstring
counterleftbox = counterleftbox 1
rsSearch.MoveNext
loop
%>
<div class="webpagescontainer"<% If counterleftbox < 5 then %> style="height:auto;"<% End If %>>
<div id="webpagescontent">
<% =listingstringloop %>
</div>
</div>
<% End if
rsSearch.Close
Set rsSearch = Nothing
%>
<%
listingstringloop = ""
listingstring = ""
counterleftbox = 0
strSQL = ""
strSQL = "SELECT 'categories' AS pagetype, cat.categoryid AS id, cat.category AS title, cat.content, 0 AS menutype, '' AS menuoption, LIKE (cat.category, cat.content) AGAINST ('" amp; searchfor amp; "') AS relevance FROM categories AS cat "
strSQL = strSQL amp; "WHERE (LIKE(cat.category, cat.content) AGAINST ('" amp; searchfor amp; "' IN BOOLEAN MODE) OR (cat.category like '%" amp; searchfor amp; "%') OR (cat.content like '%" amp; searchfor amp; "%')) "
strSQL = strSQL amp; "GROUP BY id "
strSQL = strSQL amp; "ORDER BY relevance DESC "
Set rsSearch = my_conn.Execute(strSQL)
If rsSearch.EOF = False then
searchrecordin = "Yes"
%>
<h2 class="section">Categories</h2>
<%
Do while NOT rsSearch.eof
id = reversefieldfiltering(rsSearch("id"))
pagetype = reversefieldfiltering(rsSearch("pagetype"))
title = reversefieldfiltering(rsSearch("title"))
menutype = reversefieldfiltering(rsSearch("menutype"))
menuoption = reversefieldfiltering(rsSearch("menuoption"))
content = rsSearch("content")
content = reversefieldfiltering(content)
content = striptags(content)
content = trimstring(content, 148)
content = toilehighlighter(content, searchfor)
content = content amp; "..."
If pagetype = "web-pages" then
If menutype = "1" then
linkname = buildlink("/" amp; title amp; "/")
ElseIf menutype = "2" then
linkname = buildlink("/" amp; menuoption amp; "/" amp; id amp; "_" amp; title amp; "/")
End If
Else
linkname = buildlink("/" amp; pagetype amp; "/" amp; id amp; "_" amp; title amp; "/")
End If
title = toilehighlighter(title, searchfor)
listingstring = " <h3><a href=""" amp; linkname amp; """>" amp; title amp; "</a></h3>" amp; vbcrlf
listingstring = listingstring amp; " <p class=""opening""><a href=""" amp; linkname amp; """>" amp; content amp; "</a></p>" amp; vbcrlf
listingstringloop = listingstringloop amp; listingstring
counterleftbox = counterleftbox 1
rsSearch.MoveNext
loop
%>
<div class="categoriescontainer"<% If counterleftbox < 5 then %> style="height:auto;"<% End If %>>
<div id="categoriescontent" style="width: auto;">
<% =listingstringloop %>
</div>
</div>
<% End if
rsSearch.Close
Set rsSearch = Nothing
If searchrecordin = "No" then
%>
<p class="opening"><b>We are sorry, but no matches were found matching your search term. Please try a different search term.</b></p>
<%
End If
End If %>
<div class="clear"></div>
</div>
<div class="rightbox">
<h2>Video Gallery</h2>
<%
listingstringloop = ""
listingstring = ""
counterdiv = 0
strSQL = ""
strSQL = "SELECT 'video-gallery' AS pagetype, vg.videogalleryid AS id, vg.title, vg.videocontent, vg.folderlink, LIKE (vg.title, vg.description) AGAINST ('" amp; searchfor amp; "') AS relevance FROM videogallery AS vg "
strSQL = strSQL amp; "WHERE (LIKE(vg.title, vg.description) AGAINST ('" amp; searchfor amp; "' IN BOOLEAN MODE) OR (vg.title like '%" amp; searchfor amp; "%') OR (vg.description like '%" amp; searchfor amp; "%')) "
strSQL = strSQL amp; "GROUP BY id "
strSQL = strSQL amp; "ORDER BY relevance DESC "
Set rsSearch = my_conn.Execute(strSQL)
If rsSearch.EOF = False then
imagecounter = 1
Do while NOT rsSearch.eof
If imagecounter > 2 then imagecounter = 1
youtubevideoid = ""
id = reversefieldfiltering(rsSearch("id"))
pagetype = reversefieldfiltering(rsSearch("pagetype"))
videotitle = reversefieldfiltering(rsSearch("title"))
folderlink = reversefieldfiltering(rsSearch("folderlink"))
videocontent = rsSearch("videocontent")
videocontent = reversefieldfiltering(videocontent)
regExpVideocontent = Replace(videocontent, "v/","#v=")
youtubevideoid = GetFirstMatch("[\#amp;]v=([^?#]*)",regExpVideocontent)
If youtubevideoid = "" then
regExpVideocontent = GetFirstMatch("<iframe [^>]*src=""(.*?)""[^>]*>",videocontent)
regExpVideocontent = Replace(regExpVideocontent,"http://www.youtube.com/embed/","")
regExpVideocontent = Replace(regExpVideocontent,"?rel=0","")
youtubevideoid = TRIM(regExpVideocontent)
youtubevideoid = RTRIM(youtubevideoid)
youtubevideoid = LTRIM(youtubevideoid)
End If
listingstring = " <a href=""/video-gallery/?a=1amp;vgid=" amp; id amp; """>"
listingstring = listingstring amp; "<img src=""/images/transparent.gif"" style=""background-image:url('http://img.youtube.com/vi/" amp; youtubevideoid amp; "/1.jpg');background-position:center;"" border=""0"" width=""115"" height=""86"" alt=""" amp; videotitle amp; """ title=""" amp; videotitle amp; """ "
If imagecounter = 1 then
listingstring = listingstring amp; "class=""left"""
Else
listingstring = listingstring amp; "class=""right"""
End If
listingstring = listingstring amp; "/></a>" amp; vbcrlf
listingstringloop = listingstringloop amp; listingstring
imagecounter = imagecounter 1
counterdiv = counterdiv 1
rsSearch.MoveNext
loop
%>
<div class="videocontainer"<% If counterdiv < 7 then %> style="height:auto;"<% End If %>>
<div id="videocontent">
<% =listingstringloop %>
</div>
</div>
<%
End if
rsSearch.Close
Set rsSearch = Nothing
%>
</div>
<% End If %>
<div class="clear"></div>
</div>
<!-- #include virtual="/site-elements/template/template-end.asp" -->
<!-- #include virtual="/site-elements/includes/database-close.asp" -->
Обычно я использую% для обозначения моей части и полных слов. Так что не могу понять, где он это устанавливает…
Любая помощь была бы полезной.
Комментарии:
1. Не могли бы вы также опубликовать внутренний код? Код на C #?
Ответ №1:
Если вы хотите выполнить поиск по слову, вы можете использовать следующую идею.
Вызовите следующую строку перед построением SQL
searchfor = "[^a-zA-Z]" searchfor "[^a-zA-Z]"
Идея здесь заключается в поиске введенного содержимого, в котором до и после него нет алфавитов. Например, если вы введете «at», то он соответствует «car at usa», но не «cat»
Это не идеальное решение, а просто идея, ее можно расширить, чтобы исключить дополнительные переменные