Discussion Locked

Ranking fields using VB Component

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am writing a search component to be hooked into an asp page. I want to return results sorted by their ranking based on three fields;

Primary Categorisation
Secondary Categorisation
Keywords

I want to look at each field and if the word exists then add 1 to a counter for each field. The values will then be written back to the recordset and used for sorting. An example is

PC SC KW
5 5 8
5 4 10
5 4 7
4 3 11
0 5 4
0 3 6
0 0 5
0 0 4
A word will only appear once in each field. Any suggestions as to the best way to do this?

6 replies

by andrew

I you want to keep it DB independent,
here's one way using VB:

Public Sub GetRank(oRs As ADODB.Recordset, PC, SC, KW)
Dim vWord
Dim PCCount As Long
Dim SCCount As Long
Dim KWCount As Long

Do 'loop throught the record set...
For Each vWord In PC 'check the PC field for matches...
If InStr(1, oRs.Fields("PC"), vWord, vbTextCompare) > 0 Then PCCount = PCount + 1
Next v
For Each vWord In SC 'check the SC field for matches...If InStr(1, oRs.Fields("PC"), vWord, vbTextCompare) > 0 Then SCCount = SCount + 1
Next v
For Each vWord In KW 'check the KC field for matches...
If InStr(1, oRs.Fields("PC"), vWord, vbTextCompare) > 0 Then KWCount =KWCount + 1
Next v
oRs.MoveNext
Loop While Not oRs.EOF

'ok. we've compiled all the counts, now add those fields to the RS...
oRs.Fields.Append "PCCount", adInteger, , , PCCount
oRs.Fields.Append "SCCount",adInteger, , , SCCount
oRs.Fields.Append "KWCount", adInteger, , , KWCount

'now sort the RS based on those counts...
oRs.Sort = "PCCount DESC, SCCount DESC KWCount DESC"

End Sub

by andrew

here's the call example:

GetRank oRs, Array("bee", "wasp"), Array("honey", "queen"), Array("sting", "ouch")

The oRs param is byref, so you can just pass it back to the ASP for HTML generation.

Hope this helps.

by lo

#1 looks like it would work. With a quick look I am not sure where the results get stored to do the count for all then update but ... Jus put update inside a loop after the last count (read record, do count for 3 fields, update record, get next record)>.

Might consider = (pccount * 1000) + (sccount * 100) + kwcount which will give you a single field for sorting the same as the 3 in #1 (assumes counts will be < 99, if not make multipliers larger).

This discussion is closed to new replies.