0

I want to show result based on highest matching keywords in descending order.

Below code shows result but not sorted in descending order.

Please help me to resolve this issue as I don't want to use full text index.

My code is as follows:

<%
Dim SearchWord, arrKeyWords, Cnt, tsearch, i
SearchWord  = Trim(request("searcha"))
SearchWord  = Replace(SearchWord, " and ", " ")
SearchWord  = Replace(SearchWord, " in ", " ")
SearchWord  = Replace(SearchWord, " or ", " ")
arrKeyWords = Split(SearchWord ," ") 
%>
<%
If IsArray(arrKeyWords) Then
  For i = 0 To (UBound(arrKeyWords)-1)
    Dim objRSsg, objCmdsg, strsg
    Set objCmdsg = Server.CreateObject("ADODB.Command")
    Set objRSsg  = Server.CreateObject("ADODB.Recordset")

    Dim countItem, numPerPage, page, totalpages, totalRecs
    countItem  = 0
    numPerPage = 50

    objRSsg.cursorlocation = 3
    If request("page") = "" Then
      page = 1
    Else
      page = CLng(request("page"))
    End If
    strsg = "select rProd_name, r_id from reseller_prod " & _
            "where rProd_name LIKE '%' + ? + '%' " & _
            "union " & _
            "select Prod_name, '' as r_id from product " & _
            "where Prod_name LIKE '%' + ? + '%'"

    With objCmdsg
      .ActiveConnection = MM_connDUdirectory_STRING
      .CommandText = strsg
      .CommandType = adCmdText
      .Parameters.Append(.CreateParameter("@sa1", adVarChar, adParamInput, 1000))
      .Parameters.Append(.CreateParameter("@sa2", adVarChar, adParamInput, 1000))
      .Parameters.Append(.CreateParameter("@sa3", adVarChar, adParamInput, 1000))
      .Parameters.Append(.CreateParameter("@sa4", adVarChar, adParamInput, 1000))

      .Parameters("@sa1").Value = arrKeyWords(i)
      .Parameters("@sa2").Value = arrKeyWords(i)
      .Parameters("@sa3").Value = arrKeyWords(i)
      .Parameters("@sa4").Value = arrKeyWords(i) 
    End With 
    objRSsg.Open objCmdsg, , 1, 2
  Next
End If
...
...
%>
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
lokesh purohit
  • 179
  • 2
  • 2
  • 16
  • 2
    You didn't add an ORDER BY to the query. How do you determine the highest matching keywords? Do you store this in a sql column? – kloarubeek May 15 '16 at 21:06
  • @kloarubeek, i am retrieving search result from table without storing keyword in sql column. even i have used order by too but not showing as per me – lokesh purohit May 16 '16 at 03:25
  • I'm not sure what you mean with 'highest matching keywords'. What column do you want to sort on? That should somehow be in the database (to keep it simple). For instance if you want to sort on Prod_name, add an order by at the end (so after the 2nd query) : ORDER BY Prod_name DESC – kloarubeek May 16 '16 at 19:26
  • It looks like you are doing a separate query and database fetch for each keyword... is this how it is meant to work? Or do you want to search for something with one or all of they keywords and then order by which matches have the most keywords? – johna May 17 '16 at 05:14
  • @John yes you are right as you see in above code, i am doing a separate query and database fetch for each keyword. As result, i want records matches with most keywords in descending order. – lokesh purohit May 17 '16 at 09:08
  • 1
    [Related](http://stackoverflow.com/a/189399/1630171). Since you already know the number of keywords: construct a prepared statement with an `IN` clause accepting that number of input arguments. That will allow you to `GROUP BY` and `ORDER BY` the results on the database side, too. If anything I'd add the `%` wildcard characters in VBScript rather than in the SQL statement. – Ansgar Wiechers May 17 '16 at 11:08
  • @AnsgarWiechers thanks for giving hint. let me try and get back to you – lokesh purohit May 17 '16 at 13:08
  • @AnsgarWiechers there is not limit for keywords. it may be 1 or 25 if i go with IN clause. One more issue is as per above code searching become too slow. pls help me how to resolve this issue as it took many days. – lokesh purohit May 18 '16 at 08:53
  • is there any other way to pass keywords like "keyword1" or "keyword2" or "keyword3"... In above code its searching for each keyword in loop so it time taking and show timeout expire message. or please tell me how to write in sql db as procedure as i have multi table. – lokesh purohit May 18 '16 at 12:55
  • 1
    The answer I referred you to has everything you need to know. The approach you chose will be slow no matter what you do. The most promising approaches are constructing a prepared statement or using a stored procedure. Either way, I'm not writing the code for you. Pick one, try to implement it, and come back when you have a question about something specific you can't get to work. – Ansgar Wiechers May 18 '16 at 13:43

0 Answers0