SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: keyword search

  1. #1
    SitePoint Member
    Join Date
    May 2004
    Location
    New Zealand
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    keyword search

    Hi, at the moment I've got a search query that involves a kewyord input that searchs 2 db feilds. It works fine if you use one word searches, but draws no records if you have 2 words. How do I set up a search that can include multiple words or multiple individual letters etc...

    heres my script for drawing the data into the page:

    <%
    Dim rsSearch__varKeyword
    rsSearch__varKeyword = "%"
    If (Request.QueryString("keyword") <> "") Then
    rsSearch__varKeyword = Request.QueryString("keyword")
    SearchCounter = SearchCounter + 1
    End If
    %>
    <%
    Dim rsSearch__varState
    rsSearch__varState = "%"
    If (Request.QueryString("state") <> "") Then
    rsSearch__varState = Request.QueryString("state")
    SearchCounter = SearchCounter + 1
    End If
    %>
    <%
    Dim rsSearch__varIndustry
    rsSearch__varIndustry = "%"
    If (Request.QueryString("industry") <> "") Then
    rsSearch__varIndustry = Request.QueryString("industry")
    SearchCounter = SearchCounter + 1
    End If
    %>
    <%
    set rsSearch = Server.CreateObject("ADODB.Recordset")
    rsSearch.ActiveConnection = MM_classifieds_STRING
    Dim strSQL
    strSQL = "SELECT * FROM ADS WHERE (COMPANY LIKE '%" + Replace(rsSearch__varKeyword, "'", "''") + "%'"
    strSQL = strSQL & " OR AD_DESCRIPTION LIKE '%" + Replace(rsSearch__varKeyword, "'", "''") + "%')"
    strSQL = strSQL & " AND AD_APPROVED=1"
    strSQL = strSQL & " AND (AD_STATE LIKE '" + Replace(rsSearch__varState, "'", "''") + "')"
    strSQL = strSQL & " AND (SUB_ID4 LIKE '" + Replace(rsSearch__varIndustry, "'", "''") + "'"
    strSQL = strSQL & " OR SUB_ID2 LIKE '" + Replace(rsSearch__varIndustry, "'", "''") + "'"
    strSQL = strSQL & " OR SUB_ID3 LIKE '" + Replace(rsSearch__varIndustry, "'", "''") + "')"
    strSQL = strSQL & " ORDER BY BACKLINK DESC, AD_DESCRIPTION = '' DESC, AD_LINK ='' DESC"
    rsSearch.Source = strSQL
    'Response.Write "rsSearch.Source:" & rsSearch.Source & "<BR>"
    rsSearch.CursorType = 0
    rsSearch.CursorLocation = 2
    rsSearch.LockType = 3
    rsSearch.Open()
    rsSearch_numRows = 0
    %>



    Can anyone provide a mulitple keyword script using only one input box?

    cheers
    Dave.

  2. #2
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    code looks it should work..perhaps enclosing the like statements....i have a similar set up and works great finding multiple words using % on both sides:

    (COMPANY LIKE '%" + Replace(rsSearch__varKeyword, "'", "''") + "%'"

    to

    (COMPANY LIKE '%" + Replace(rsSearch__varKeyword, "'", "''") + "%')"

    and

    AD_DESCRIPTION LIKE '%" + Replace(rsSearch__varKeyword, "'", "''") + "%')"


    to:

    (AD_DESCRIPTION LIKE '%" + Replace(rsSearch__varKeyword, "'", "''") + "%')"

  3. #3
    Guru Bullschmidt's Avatar
    Join Date
    Apr 2002
    Location
    USA
    Posts
    524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And perhaps this (which is similar) may hopefully give you some ideas:

    Classic ASP Design Tips - Search For Keywords on Multiple Fields
    http://www.bullschmidt.com/devtip-se...iplefields.asp
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips

  4. #4
    SitePoint Enthusiast Nawaz Ijaz's Avatar
    Join Date
    Feb 2005
    Location
    Lahore, Pakistan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Like this way you can search multiple words. You need to split the keywords string like below ..

    Sql = "SELECT Person.* FROM Person where 1=1 and "

    Dim Condn
    Condn = ""

    SkillCondn = ""
    keyword = Replace(Request("keywords"),","," ")
    ArrSkills=Split(keyword," ")
    if IsArray(ArrSkills) then
    for i=0 to ubound(ArrSkills)
    if SkillCondn <> "" then SkillCondn = SkillCondn & " or "
    SkillCondn = SkillCondn & "( Person.personid in (select person.personid from person where textresume like '%" & ArrSkills(i) & "%') )"
    'SkillCondn = SkillCondn & "( Person.personid in (select personid from PersonSkills where (Skillid in (select skillid from Skills where Skill like '%" & ArrSkills(i) & "%') or Skillid in (select skillid from skillsynonyms where synonym like '%" & ArrSkills(i) & "%') )) )"
    next
    end if

    if SkillCondn <> "" then 'SkillCondn = " (" & SkillCondn & ")"
    Condn = Condn & SkillCondn & "or "
    end if
    call openConn()
    set rs = server.CreateObject("ADODB.Recordset")
    Dim Sql


    Sql = "SELECT Person.* FROM Person where 1=1 and "

    Dim Condn
    Condn = ""

    SkillCondn = ""
    keyword = Replace(Request("keywords"),","," ")
    ArrSkills=Split(keyword," ")
    if IsArray(ArrSkills) then
    for i=0 to ubound(ArrSkills)
    if SkillCondn <> "" then SkillCondn = SkillCondn & " or "
    SkillCondn = SkillCondn & "( Person.personid in (select person.personid from person where textresume like '%" & ArrSkills(i) & "%') )"
    'SkillCondn = SkillCondn & "( Person.personid in (select personid from PersonSkills where (Skillid in (select skillid from Skills where Skill like '%" & ArrSkills(i) & "%') or Skillid in (select skillid from skillsynonyms where synonym like '%" & ArrSkills(i) & "%') )) )"
    next
    end if

    if SkillCondn <> "" then 'SkillCondn = " (" & SkillCondn & ")"
    Condn = Condn & SkillCondn & "or "
    end if

    if request("myradio1")= "0" then

    if Request("State") <> "" and Request("State") <> "0" and Request("State") <> "1" then

    SkillCondn22 = ""
    'keyword22 = Replace(Trim(Request("State")),","," ")
    ArrSkills22=Split(Trim(Request("State")),",")
    if IsArray(ArrSkills22) then
    for i=0 to ubound(ArrSkills22)
    if SkillCondn22 <> "" then SkillCondn22 = SkillCondn22 & " or "
    SkillCondn22 = SkillCondn22 & "( Person.State like '%" & trim(ArrSkills22(i)) & "%') "
    next
    end if

    if SkillCondn22 <> "" then SkillCondn22 = " (" & SkillCondn22 & ")"
    Condn = Condn & SkillCondn22 & "or "
    end if

    if Request("State") = "0" then
    Condn = Condn & " Person.Country like '%" & "US" & "%'" & " or "
    end if

    if Request("State") = "1" then
    Condn = Condn & " or Person.Country like '%" & "Canada" & "%'" & " or "
    end if

    end if

    if request("myradio1")= "1" then

    if Request("zip_code") <> "" then
    Condn = Condn & " (Person.PostalCode like '%" & Request("zip_code") & "%' and "
    end if

    if Request("Country") <> "" then
    Condn = Condn & " Person.Country like '%" & Request("Country") & "%'" & ") or "
    end if

    'response.Write("<br>" & "myradiobutton 02"& "<br>")
    end if

    Hope this will help,
    Nawaz Ijaz.
    Nawaz Ijaz [Lahore, Pakistan]
    Boy: He is big..i wont like to fight him...
    Achilles: Thatz Y no one wud remember u after thousand years...!!

  5. #5
    SitePoint Member
    Join Date
    May 2004
    Location
    New Zealand
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help guys.... here's what I ended up with below that works an absolute treat....

    Dave.


    <%
    Keywords = Request.Form("keyword")

    ' Set var.
    KeywordsFixedForSQL = Keywords

    ' Remove any ".
    KeywordsFixedForSQL = Replace(KeywordsFixedForSQL, Chr(34), "")

    ' Double up any single quotes.
    KeywordsFixedForSQL = Replace(KeywordsFixedForSQL, "'", "''")

    If KeywordsFixedForSQL <> "" Then
    ' Set var (array of words).
    strarrayKeywordsFixedForSQL = Split(KeywordsFixedForSQL, " ")

    ' Loop thru items (words).
    For intItemNum = 0 To UBound(strarrayKeywordsFixedForSQL)
    strSQLWhere = strSQLWhere & " OR ("
    strSQLWhere = strSQLWhere & "Company Like '%" & strarrayKeywordsFixedForSQL(intItemNum) & "%'"
    strSQLWhere = strSQLWhere & " OR AD_DESCRIPTION Like '%" & strarrayKeywordsFixedForSQL(intItemNum) & "%'"
    strSQLWhere = strSQLWhere & ")"
    Next ' Next intItemNum.

    ' Remove initial " OR ".
    strSQLWhere = Right(strSQLWhere, Len(strSQLWhere) - 4)
    ELSE
    strSQLWhere = strSQLWhere & "0=0"
    End If
    %>
    <%
    set rsSearch = Server.CreateObject("ADODB.Recordset")
    rsSearch.ActiveConnection = MM_classifieds_STRING
    Dim strSQL
    strSQL = "SELECT * FROM ADS WHERE (" & strSQLWhere & ")"
    strSQL = strSQL & " AND AD_APPROVED=1"
    strSQL = strSQL & " AND (AD_STATE LIKE '" & Request.Form("state") & "')"
    strSQL = strSQL & " AND (SUB_ID4 LIKE '" & Request.Form("industry") & "'"
    strSQL = strSQL & " OR SUB_ID2 LIKE '" & Request.Form("industry") & "'"
    strSQL = strSQL & " OR SUB_ID3 LIKE '" & Request.Form("industry") & "')"
    strSQL = strSQL & " ORDER BY BACKLINK DESC, AD_DESCRIPTION = '' DESC, AD_LINK ='' DESC"
    rsSearch.Source = strSQL
    'Response.Write "rsSearch.Source:" & rsSearch.Source & "<BR>"
    rsSearch.CursorType = 0
    rsSearch.CursorLocation = 2
    rsSearch.LockType = 3
    rsSearch.Open()
    rsSearch_numRows = 0
    %>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •