searching article for names in the database before submitting article to the database

Question

I want to create a function that will search through a text, find names those match with existing names in the database and add links to those names before submitting the article to the database.

i.e. text: Chelsea are making a change now as goalscorer Nicolas Anelka is replaced by in-form Florent Malouda who can do no wrong lately.

Nicolas Anelka exists in the database in the Players table with ID column equals to 1.

I want text to be converted to Chelsea are making a change now as goalscorer a href="player.asp=ID=1"Nicolas Anelka/a is replaced by in-form Florent Malouda who can do no wrong lately.

Problem with my code is that I have player names are stored in one column. So Nicolas Anelka is one column so names dont match with letters.

---

Function PlayerStats (ArticleDesc)

If IsNull(ArticleDesc) Then Exit Function

WordArray = Split(ArticleDesc, " ")

For i = 1 to Ubound(WordArray)

SQL = "SELECT PlayerID, PlayerName"
SQL = SQL & " FROM Players"
Set objPlayer = objConn.Execute(SQL)    

Do While NOT objPlayer.EOF

If WordArray(i) = objPlayer("PlayerName") Then
PlayerStats = objPlayer("PlayerName") & " is in the database!"
Else
End If

objPlayer.MoveNext
Loop

Next

End Function

Answer 1

Could do a sql COUNT then if the counts greater than 0 theres a match...

variable =  SELECT COUNT(*) FROM table WHERE 'field' = 'search term' 

if(variable <= 0) {
    // Run code here
}

I'm not to sure on how to do this with asp code, but that is how i would do it in theory

hope this helps :)

EDIT:

mmm, problem with that is an article thats say 1000 words long, the db would have to do 1000 lookups, quite costly i'd say.

However to speed things up, you could employ the use of a FULLTEXT index, and like you said split up into words looping through each one looking for links, downside to this is you may incur several results for the same word.

I'd say a better way would be to flip it around, instead of splitting up the texts words looking for names, why not do it the other way around, iterate through all articles looking for references in the text, then link. Although all of these solutions are costly!

Answer 2

Expanding on Robs last suggestion to iterate over all players/articles I'd suggest you load all players/articles name and id column once at application startup to make it fast.

Depending on how many player articles there are you could just do a brute force Replace on your text content:

Pre-Load all player names and article ids at application startup

Global.asa

<script language="vbscript" runat="server">
Sub Application_OnStart
    '... code for database query omitted ..'

    Dim playerarticles(objPlayer.RecordCount, 1) '- init array to RecordCount -'
    Dim i

    i = 0

    While Not objPlayer.EOF
        playerarticles(i, 0) = objPlayer("PlayerName")
        playerarticles(i, 1) = objPlayer("PlayerID")
        objPlayer.MoveNext
        i = i + 1
    Wend

    Application("articles") = playerarticles
End Sub
</script>

sample usage in page

<%
Option Explicit

Dim parsedtext
Dim i

parsedtext = "Chelsea are making a change now as goalscorer Nicolas Anelka is replaced by in-form Florent Malouda who can do no wrong lately."

For i = 0 To UBound(Application("articles"))
    parsedtext = Replace(parsedtext, Application("articles")(i,0), "<a href=""player.asp?ID=" & Application("articles")(i,1) & """>" & Application("articles")(i,0) & "</a>")
Next

Response.Write(parsedtext)
%>

If you pull your text content from a database and have a lot of player articles you should consider preparing your content offline by (nightly) a database update.

---

long term view

How are you planning to handle updates to your player article database? Just parsing your text on insert would leave you ending up with articles that don't link to players added to the database later.

As mentioned above, some kind of job that scans your articles for player names not linked yet would be a better solution. Your web application would not be cluttered with code concerned with maintaining your content.