Added: March/21/2005 at 5:37am | IP Logged

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>

Added: March/21/2005 at 7:42pm | IP Logged

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

Added: March/22/2005 at 1:25am | IP Logged

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

Added: March/22/2005 at 1:31am | IP Logged

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

Added: March/22/2005 at 1:45am | IP Logged

oops

you probably want this

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

notice spaces between the % and double quotes on both sides