Home | Advertising Info39 USERS CURRENTLY ONLINE   
PowerASP
   Site Search Contact Us Saturday, July 02, 2022  

  Active InfoActive Info  Display List of Info MembersMemberlist  Search The InfoSearch  HelpHelp
  RegisterRegister  LoginLogin
Classic ASP - Scripting
 PowerASP Code Help Area : Classic ASP - Scripting
Subject Info: Keyword search help A d d  -  P o s tAdd P o s t
Author
Message << Prev Info | Next Info >>
babulgogoi
Newbie
Newbie


Joined: March/21/2005
Online Status: Offline
Info: 2
Added: March/21/2005 at 5:37am | IP Logged Quote babulgogoi

Hi,

The following ASP script is used in a keyword search form at http://www.asiasrc.org/search.htm.

However, the AND function, to show the exact match of the keyword search is not working.

There is a keyword field in the db.MDB file, like: babul, babulgogoi, babul gogoi, babul and gogoi

And when searcing for 'babul', it should not show 'babulgogoi' or 'babul gogoi'.

Can someone please help me?

Regards

Babul


Code:

<%
set con=server.CreateObject ("ADODB.Connection")
'set fastcon1=server.CreateObject ("ADODB.Connection")
datapath=server.MapPath (".") & "db.mdb"
'fastcon.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & datapath
con.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & datapath
if trim(request("user_request")) = "browse" then
alph = trim(request("alph"))
if alph = "" then
alph = "A"
end if
sqlselect = "Select * from web1 where Title like '"&alph&"%'"
else if trim(request("key_word")) = "" and trim(request("book_title")) = "" and trim(request("author"))="" and trim(request("publisher")) = "" then
response.redirect "search.htm"
else
key_word = trim(request("key_word"))
book_title = trim(request("book_title"))
author = trim(request("author"))
publisher = trim(request("publisher"))
R1 = trim(request("R1"))
R2 = trim(request("R2"))
R3 = trim(request("R3"))

if not key_word = "" then

arrkey_word = split(key_word," ")
'sqlselect = "Select * from web1 where Keywords like '&lt%"&trim(key_word)&"%&gt'"

if ubound(arrkey_word) = 0 then
keywordstr = "((Keywords like '"&trim(arrkey_word(0))&"%') or (Keywords like '% "&trim(arrkey_word(0))&"%'))"
else
keywordstr = "((Keywords like '"&trim(arrkey_word(0))&"%' or Keywords like '% "&trim(arrkey_word(0))&"%'"

for i = 1 to ubound(arrkey_word)
keywordstr = keywordstr & " or Keywords like '"&trim(arrkey_word(i))&"%' or Keywords like '% "&trim(arrkey_word(i))&"%'"
next
keywordstr = keywordstr &"))"
end if
'response.write keywordstr &"<br>"

sqlselect = "Select * from web1 where " & keywordstr

end if

if not book_title = "" then
if key_word = "" and  not book_title = "" then

'arrbook_title = split(book_title," ")

'if ubound(arrbook_title) = 0 then
book_titlestr = "((Title like '"&trim(book_title)&"%'))"
'else
'book_titlestr = "(Title like '% "&trim(arrbook_title(0))&"%'"
'for i = 1 to ubound(arrbook_title)
'book_titlestr = book_titlestr & " or Title like '% "&trim(arrbook_title(i))&"%'"
'next
'book_titlestr = book_titlestr & ")"
'end if
sqlselect = "Select * from web1 where " & book_titlestr

else

'arrbook_title = split(book_title," ")

'if ubound(arrbook_title) = 0 then
book_titlestr = "((Title like '"&trim(book_title)&"%'))"
'else
'book_titlestr = "(Title like '% "&trim(arrbook_title(0))&"%'"
'for i = 1 to ubound(arrbook_title)
'book_titlestr = book_titlestr & " or Title like '% "&trim(arrbook_title(i))&"%'"
'next
'book_titlestr = book_titlestr & ")"
'end if
sqlselect = sqlselect &" "& R1 &" "& book_titlestr

end if
end if

if not author = "" then
if key_word = "" and book_title = "" and  not author = "" then

arrauthor = split(author," ")

if ubound(arrauthor) = 0 then
authorstr = "((Authors like '"&trim(arrauthor(0))&"%') or (Authors like '% "&trim(arrauthor(0))&"%'))"
else
authorstr = "((Authors like '"&trim(arrauthor(0))&"%' or Authors like '% "&trim(arrauthor(0))&"%'"
for i = 1 to ubound(arrauthor)
authorstr = authorstr & " or Authors like '"&trim(arrauthor(i))&"%' or Authors like '% "&trim(arrauthor(i))&"%'"
next
authorstr = authorstr & "))"
end if
sqlselect = "Select * from web1 where " & authorstr

else

arrauthor = split(author," ")

if ubound(arrauthor) = 0 then
authorstr = "((Authors like '"&trim(arrauthor(0))&"%') or (Authors like '% "&trim(arrauthor(0))&"%'))"
else
authorstr = "((Authors like '"&trim(arrauthor(0))&"%' or Authors like '% "&trim(arrauthor(0))&"%'"
for i = 1 to ubound(arrauthor)
authorstr = authorstr & " or Authors like '"&trim(arrauthor(i))&"%' or Authors like '% "&trim(arrauthor(i))&"%'"
next
authorstr = authorstr & "))"
end if

sqlselect = sqlselect &" "& R2 &" "& authorstr

end if
end if

if not publisher = "" then
if key_word = "" and book_title = "" and author = "" and not publisher = "" then

arrpublisher = split(publisher," ")

if ubound(arrpublisher) = 0 then
publisherstr = "((Name_of_Pub like '"&trim(arrpublisher(0))&"%') or (Name_of_Pub like '% "&trim(arrpublisher(0))&"%'))"
else
publisherstr = "((Name_of_Pub like '"&trim(arrpublisher(0))&"%' or Name_of_Pub like '% "&trim(arrpublisher(0))&"%'"
for i = 1 to ubound(arrpublisher)
publisherstr = publisherstr & " or Name_of_Pub like '"&trim(arrpublisher(i))&"%' or Name_of_Pub like '% "&trim(arrpublisher(i))&"%'"
next
publisherstr = publisherstr & "))"
end if
sqlselect = "Select * from web1 where " & publisherstr

else

arrpublisher = split(publisher," ")

if ubound(arrpublisher) = 0 then
publisherstr = "((Name_of_Pub like '"&trim(arrpublisher(0))&"%') or (Name_of_Pub like '% "&trim(arrpublisher(0))&"%'))"
else
publisherstr = "((Name_of_Pub like '"&trim(arrpublisher(0))&"%' or Name_of_Pub like '% "&trim(arrpublisher(0))&"%'"
for i = 1 to ubound(arrpublisher)
publisherstr = publisherstr & " or Name_of_Pub like '"&trim(arrpublisher(i))&"%' or or Name_of_Pub like '% "&trim(arrpublisher(i))&"%'"
next
publisherstr = publisherstr & "))"
end if
sqlselect = sqlselect &" "& R3 &" "& publisherstr

end if
end if

end if
end if
%>
<html>
<head>
<META http-equiv=Page-Enter content=blendTrans(Duration=0)>
<title>: : : : Resource Center On Sexuality : : : :</title>
<style>
a:link {text-decoration: none; color:#ff3300}
a:hover {text-decoration: none; color:#ff3300}
a:visited {text-decoration: none; color:#ff3300}

</style>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head>

<body bgcolor="FCDB00" text="#000000" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0" onload=scroll()>

  <table width="90%" border="0" cellpadding="0" cellspacing="0">
    <tr>
      <td>
        <table border="0" cellspacing="0" cellpadding="0">
          <tr>
             <td  colspan="2"> <table border="0" align="right" cellpadding="0" cellspacing="1">
                 <tr>
                   <td colspan="2" align="center" valign="middle">


                        <table  border="0" cellpadding="0" cellspacing="0">
                           <tr>
                             <td colspan="2">&nbsp;</td>
                           </tr>
                           <tr>
                             <td colspan="2">&nbsp;</td>
                           </tr>
                           <tr>
                             <td colspan="2">&nbsp;</td>
                           </tr>
                           <tr>
                             <td colspan="2">&nbsp;</td>
                           </tr>
                           <tr>
                             <td>&nbsp;</td>
                             <td><font color="#ff3300" size="2" face="verdana" style="font-size: 10px"><strong>

Browse:
<a href="search1.asp?user_request=browse&alph=A">A</a >
                               <a href="search1.asp?user_request=browse&alph=B">B</a >
                               <a href="search1.asp?user_request=browse&alph=C">C</a >
                               <a href="search1.asp?user_request=browse&alph=D">D</a >
                               <a href="search1.asp?user_request=browse&alph=E">E</a >
                               <a href="search1.asp?user_request=browse&alph=F">
                               F</a> <a href="search1.asp?user_request=browse&alph=G">
                               G</a> <a href="search1.asp?user_request=browse&alph=H">
                               H</a> <a href="search1.asp?user_request=browse&alph=I">
                               I</a> <a href="search1.asp?user_request=browse&alph=J">
                               J</a> <a href="search1.asp?user_request=browse&alph=K">
                               K</a> <a href="search1.asp?user_request=browse&alph=L">
                               L</a> <a href="search1.asp?user_request=browse&alph=M">
                               M</a> <a href="search1.asp?user_request=browse&alph=N">
                               N</a> <a href="search1.asp?user_request=browse&alph=O">
                               O</a> <a href="search1.asp?user_request=browse&alph=P">
                               P</a> <a href="search1.asp?user_request=browse&alph=Q">
                               Q</a> <a href="search1.asp?user_request=browse&alph=R">
                               R</a> <a href="search1.asp?user_request=browse&alph=S">
                               S</a> <a href="search1.asp?user_request=browse&alph=T">
                               T</a> <a href="search1.asp?user_request=browse&alph=U">
                               U</a> <a href="search1.asp?user_request=browse&alph=V">
                               V</a> <a href="search1.asp?user_request=browse&alph=W">
                               W</a> <a href="search1.asp?user_request=browse&alph=X">
                               X</a> <a href="search1.asp?user_request=browse&alph=Y">
                               Y</a> <a href="search1.asp?user_request=browse&alph=Z">
                               Z</a>  </strong></font></td>
                           </tr>
                        </table>
                     </td>
                   <td align="center" valign="middle">&nbsp;</td>
                 </tr>
                 <tr>
                   <td rowspan="2" align="right" valign="bottom">
                        <p> <img src="images/xbar_ex.gif" width="35"></p>
                     </td>
                   <%
Dim currentPage, rowcount, j
currentPage  = Trim(request("currentPage"))
'response.write "currentPage :" &currentPage &"<br>"

if currentPage = "" then
currentPage = 1
end if

'response.write "currentPage :" &currentPage &"<br>"
'response.write "key_word :" &key_word &"<br>"
'response.write "book_title :" &book_title&"<br>"
'response.write "author :" &author &"<br>"
'response.write "publisher :" &publisher &"<br>"
'response.write "R1 :" &R1 &"<br>"
'response.write "R2 :" &R2 &"<br>"


set rs = server.createobject("ADODB.Recordset")
rs.open sqlselect,con,1,3

if not rs.EOF then
total_records = rs.RecordCount
rs.PageSize = 6
rs.AbsolutePage = cInt(currentPage)
rowcount = 0

'if not key_word = "" then
'Response.write arrkey_word(0)
'if ubound(arrkey_word) > 0 then
'for k = 1 to ubound(arrkey_word)
'response.write ", "&arrkey_word(k)
'next
'end if
'end if
%>
                   <td><font color="#ff3300" size="2" face="verdana" style="font-size: 10px"><strong>&nbsp;</strong></font> ;<font color="#ff3300" size="1" face="verdana" style="font-size: 10px"><strong>&nbsp;<font size="2">Search
                     Results
                     <%if cInt(currentPage) = rs.PageCount then%>
                     <%=total_records%> of <%=total_records%>
                     <%else%>
                     <%=(cInt(currentPage)*6)%> of <%=total_records%>
                     <%end if%>
                     </font></strong></font></td>
                   <%
else
%>
                   <td><font color="#ff3300" size="1" face="verdana" style="font-size: 10px"><strong><font size="2">
                     No Results</font></strong></font></td>
                   <%end if%>
                   <td rowspan="2" align="center" valign="middle">&nbsp;</td>
                 </tr>
                 <tr>
                   <td valign="top"><font color="#ff3300" size="2" face="verdana" style="font-size: 10px"><strong><img src="images/strip_txt125.gif" width="125" height="2"></strong></font></td>
                 </tr>
                 <tr>
                   <td colspan="2" valign="top"> <table border="0" align="left" cellpadding="0" cellspacing="0">
                        <tr>
                           <td align="left" valign="top">
                             <table border="0" cellspacing="0" cellpadding="0">
                               <tr>
                                 <td align="center" valign="top" background="images/vert-1.gif">&nbsp;</td>
                               </tr>
                               <tr>
                                 <td align="center" valign="top" background="images/vert-1.gif">&nbsp;</td>
                               </tr>
                               <tr>
                                 <td  align="center" valign="top" background="images/vert-1.gif">&nbsp;</td>
                               </tr>
<!---

                               <tr>
                                 <td align="center" valign="top">&nbsp;</td>
                               </tr>

--->


                             </table></td>
                           <td  align="left" valign="top">
<table border="0" cellspacing="0" cellpadding="0">
                               <tr>
                                 <td align="left" valign="top"><font color="#ff3300" size="1" face="verdana" style="font-size: 10px"><strong>
                                   &nbsp;&nbsp;&nbsp;<font size="2">
                                   <%
if not rs.eof then
%>
                                   </font></strong></font> </td>
                               </tr>
                             </table>
                             <%
while not rs.eof and rowcount < rs.PageSize
%>

                 
<table width=100%>
<tr><td width=40>&nbsp;
</td><td >

<font  size="1" face="Verdana">
        <b><%=rs("Title")%> </b>
                               <%if not trim(rs("Authors")) = "" then%>
                               <br>
                               by <%=rs("Authors")%>
                               <%end if%>
                              
                              
                               <%if not trim(rs("Name_of_Pub")) = "" then%>
                              
                               <br>
                               <%=rs("Name_of_Pub")%>,&nbsp;
                                <%end if%>
                              
                               <%if not trim(rs("Pl_of_Pub")) = "" then%>
                               <%=rs("Pl_of_Pub")%>
                               <%end if%>

                              
                               
                               <%if not trim(rs("year_of_Pub")) = "" then%>
                              
                               (<%=rs("year_of_Pub")%>.&nbsp;
                               <%end if%>
                              
                               <%if not trim(rs("no_of_pgs")) = "" then%>
                               <%=rs("no_of_pgs")%>.)
                               <%end if%>
                               <%if not trim(rs("Keywords")) = "" then%>
                              
<br>
                               Keywords: <%=rs("Keywords")%>.
                               <%end if%>
                               <%if not trim(rs("Cl_No")) = "" then%>
                               <br>
                               CL.NO:<%=rs("Cl_No")%>.
                               <%end if%>
                               <%if not trim(rs("website")) = "" then%>
                               <br>
                               <a href="<%=rs("website")%>"><%=rs("website")%>. </a>
                               <%end if%>
                               <%if not trim(rs("document_name")) = "" then%>
                               <br>
                               <a href="#" target="_blank">Click here to download
                               document.</a>
                               <%end if%>
                               <%if not trim(rs("main_class")) = "" then%>
                               <br>
                               Main Category :<%=rs("main_class")%>.
                               <%end if%>

</font>

</td>
</tr>
<tr>
<td><div align=right>  <img src="images/trans.gif" height=1></div>
</td>
<tr>
</table>
                             <%
rowcount = rowcount + 1
rs.movenext()
wend
else
%>
                             </td>
                        </tr>
                     </table>
                     <p>
                        No result found.</font></p>
                     <p>
                        <%
end if
%>
                     </p>
                     <boockquote><blockquote><blockquote>
                    
                     <table border="0" cellspacing="0" cellpadding="0">
                        <tr>
                           <td width="6%"><font color="#ff3300" size="2" face="Verdana">&nbsp;</font></td>
                           <%
                   if not trim(request("user_request")) = "browse" then
                   %>
                           <td valign="bottom" align="left">
                             <%if cInt(currentPage) > 1 then%>
                             <font color="#ff3300" size="2" face="Verdana"><strong><a href="search1.asp?currentPage=<%=(cInt(currentPage)-1)%&g t;&key_word=<%=key_word%>&book_title=<%=boo k_title%>&author=<%=author%>&publisher=< %=publisher%>&R1=<%=R1%>&R2=<%=R2%>&a mp;R3=<%=R3%>">Previous</a>&nbsp;&nbs p;</strong></font> &nbsp; &nbsp;
                             <%end if%>
                           </td>
                           <td align="right" valign="bottom">
                             <%if cInt(currentPage) < rs.PageCount then%>
                             <font color="#ff3300" size="2" face="Verdana"><strong><a href="search1.asp?currentPage=<%=(cInt(currentPage)+1)%&g t;&key_word=<%=key_word%>&book_title=<%=boo k_title%>&author=<%=author%>&publisher=< %=publisher%>&R1=<%=R1%>&R2=<%=R2%>&a mp;R3=<%=R3%>">&nbsp;&nbsp;Next</a>&a mp;nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</strong& gt;</font>
                             <%end if%>
                           </td>
                           <%else%>
                           <td  valign="bottom" align="left">
                             <%if cInt(currentPage) > 1 then%>
                             <font color="#ff3300" size="2" face="Verdana"><strong><a href="search1.asp?currentPage=<%=(cInt(currentPage)-1)%&g t;&user_request=browse&alph=<%=alph%>">Prev ious&nbsp;&nbsp;</a></strong></font&g t;
                             <%end if%>
                           </td>
                           <td  align="right" valign="bottom">
                             <%if cInt(currentPage) < rs.PageCount then%>
                             <font color="#ff3300" size="2" face="Verdana"><strong><a href="search1.asp?currentPage=<%=(cInt(currentPage)+1)%&g t;&user_request=browse&alph=<%=alph%>">& ;nbsp;&nbsp;Next</a>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;</strong></font>
                             <%end if%>
                           </td>
                           <%end if%>
                        </tr>
                     </table>
                    </blockquote>

                    </boockquote></blockquote>
                   
                   
                 </tr>
               </table>
              
                    </td>

                 </tr>
               </table>
             </td>
          </tr>
        </table>
 <table>
 
</table>

 

</body>

Back to Top View babulgogoi's Profile Search for other info by babulgogoi
 
cwilliams
Admin Group
Admin Group
Avatar

Joined: April/26/2004
Location: United States
Online Status: Offline
Info: 137
Added: March/21/2005 at 7:42pm | IP Logged Quote cwilliams

If you want an exact match for a string contained in a field maybe surround that string with a space on each side

for example..

if you search for " red " your won't get "redbull"

 

Or if they are in a database field like you showed with a comma after each one search for

"red,"

you get the idea..  you have to be clever and figure something out that works

you'd want to make sure the last value in there had a comma after it as well or that wouldn't find it



Edited by cwilliams on March/21/2005 at 7:45pm


__________________


Chris Williams
http://www.PowerASP.com
Back to Top View cwilliams's Profile Search for other info by cwilliams
 
babulgogoi
Newbie
Newbie


Joined: March/21/2005
Online Status: Offline
Info: 2
Added: March/22/2005 at 1:25am | IP Logged Quote babulgogoi

Thanks Chris,

First I must tell you that I am totally new into ASP's world.

In the access file, there is a Keywords field, for each entry, e.g.

1. Sexual Health; HIV/AIDS; Media
2. Biography; Fiction; Writings; Teacher
3. Fiction; Quilt; Heart Breaks; Wild One; Muslim Society
4. Women's Struggle; Christian Women; Inheritance Rights; Kerala

So shall I modify the keywords as:

1. " Sexual Health "; " HIV/AIDS "; " Media "
2. " Biography ", " Fiction "; " Writings "; " Teacher "
3. " Fiction "; " Quilt "; " Heart Breaks "; " Wild One "; " Muslim Society "
4. " Women's Struggle "; " Christian Women "; " Inheritance Rights "; " Kerala "

Also, in the search script, where should I modify the default search function from LIKE to = (for exact match) ?

With regards,

Babul

 

 

Back to Top View babulgogoi's Profile Search for other info by babulgogoi
 
cwilliams
Admin Group
Admin Group
Avatar

Joined: April/26/2004
Location: United States
Online Status: Offline
Info: 137
Added: March/22/2005 at 1:31am | IP Logged Quote cwilliams

I really dont know man.. thats quite honestly a lot of confusing code..

almost looks like a lot of it is commented out and repeated over and over..

You'd do it near the like statement..  thing is which one and why are there so many ? (probably a reason but I can't spend that much time looking at it)

regardless.. something like this

like '"&trim(arrauthor(i))&"%' or

might become

like ' " & trim(arrauthor(i)) & " ' or

notice the spaces between the single and double quotes and also that I took out the % which is like a wildcard

I also added spaces around the & signs.. that does not effect anything.. just makes it more readable



Edited by cwilliams on March/22/2005 at 1:35am


__________________


Chris Williams
http://www.PowerASP.com
Back to Top View cwilliams's Profile Search for other info by cwilliams
 
cwilliams
Admin Group
Admin Group
Avatar

Joined: April/26/2004
Location: United States
Online Status: Offline
Info: 137
Added: March/22/2005 at 1:45am | IP Logged Quote cwilliams

oops

you probably want this

like '% " & trim(arrauthor(i)) & " %' or

notice spaces between the % and double quotes on both sides



Edited by cwilliams on March/22/2005 at 1:46am


__________________


Chris Williams
http://www.PowerASP.com
Back to Top View cwilliams's Profile Search for other info by cwilliams
 

If you wish to make a comment to this info you must first login
If you are not already registered you must first register

  A d d  -  P o s tAdd P o s t
Printable version Printable version

Info Jump
You cannot add new info in this area
You cannot add to info in this area
You cannot delete your info in this area
You cannot edit your info in this area
You cannot create polls in this area
You cannot vote in polls in this area

   Active Server Pages Rule The World
Contact Us  
All artwork, design & content contained in this site are Copyright 1998 - 2022 PowerASP.com and Christopher J. Williams
Banner ads ,other site logos, etc are copyright of their respective companies.
STATS Unless otherwise noted - All Rights Reserved.

Active Server Pages ASP programs help tutorial tutorials routine routines jobs listserve mailinglist bulletin board bulletin boards programming snippet snippets CJWSoft ASPProtect ASPBanner ASPClassifieds www.aspprotect.com, www.cjwsoft.com,www.aspclassifieds.com,www.aspphotogallery.com